This is first time I'm asking a question or initiating a discussion in Oracle community.
We are the production support team of a hedging system. Our system uses Oracle 220.127.116.11 version as database on Windows 2012 R2.
We are facing issues in our batch process in which a query sometimes give results and sometimes not in processes running in different calc servers at same time.
To calculate daily Greeks in financial term, our business users kick-off runs/processes in batches.
Each batch is called as run set. Each set contains various runs that calculates Greeks in various calculation nodes/servers.
There is a load balancer server that assigns tasks to calculation nodes as well as aggregates the results from calculation nodes.
Oracle 12c client is installed in load balancer server as well as in all calculation nodes.
Highlights of Oracle DB:
1. Following entries exist in all client SQLNET.ORA:
2. Client results cache is also disabled. “client_result_cache_size” parameter is shown as zero value in V$PARAMETER. There is no setting for client result cache in SQLNET.ora. Also ORAACCESS.xml file is not used, i.e. it has not been configured in our database.
The attached word document gives detail. Conclusion is there is a small slice of time ~20 seconds, where apparently Oracle is returning 0 rows in response to queries where we expect data to be returned. The really strange thing is that queries on the same datasets do successfully return data immediately before, after, and even during this time slice.
Based on what we see in the attached evidence, we suspect towards database being the problem here. We are quite concerned about this error. There are 1,000,000s of queries run against the databases every day, and if we cannot trust it to return correct results for queries, then there would be inaccuracies in the financial numbers being calculated.
Could anyone please share his/her views or solution to this problem?
Thanks in advance!