In a nutshell, we have a complex SQL that joins several views , to return several thousand records, to be written to a file. We run this SQL against batch of input data, a part of it always varies in iterations. We are seeing a weird problem, related to performance of this program. In order to meet our SLA , we are expected to complete the file generation with around 200000 records in less than 15 mins every day. For some days, we are able to achieve this and for most of the days, we are not able to meet this and the file generation crossing over an hr.
We are using packaged stored procedures, being called using CallableStatement and iterate over the resultsetl. I added some debug statements, captured the timestamp and noticed that after exection, it is taking longer time to complete rs.next();
I try to explain this with an example.
Briefly, our setup is like below
What I noticed is, the stmt.execute() was quick, completes in few milliseconds. But it takes longer time to ever enter the while loop, for the first time. Once it enters, the looping over resultset is quick. My question is, why and would could be the reason it takes very long time, some times about 15 to 20 mins of time, to just enter the while loop after completing stmt.execute(). This changes for different inputs, but the clogging is happening just before rs.next() is called for the first time.
What can I do to improve this or fix the issue.
Your guidance is highly appreciated?
Note: The SQL that is being called is plain SELECT against several views joinined. There are no write operations involved.
My guess (based on no data) is that you are running out of RAM and the system is thrashing. Can you gather the statistics for the server while this process is running. You can do this your QA server if you like. See if memory or CPU is maxed out. If that is the case, you can tune the program to be less demanding on that resource.
Another possibility is that another process is getting a write lock on one of the tables involved in your SQL statement.
Are you able to do any of the operations in parallel and merge the data at the end?
Try running SELECT COUNT(*) with the same query body to see if it can identify the matching records quickly. If not, then you'll need to look at your SQL.
Have you run an Explain Plan on the SQL to make sure it's using indexes correctly? Are your join conditions using indexes correctly e.g. you almost certainly need to index your foreign key columns if you are doing lots of joins. Would it be better to query the tables directly, instead of via views, so you have more control over your query? Are your search filters being applied efficiently e.g. are you excluding as much data as possible as early as possible? Are your database stats up to date so the query engine can optimise your query correctly? Are you sorting the output and if so, are you sorting on an indexed column?
Without seeing the actual SQL that you are executing, there's no way to tell if there are problems in the query or not.
And following on from the "short of memory" theory: because the delay takes place before your code gets to read the first record, that suggests that the delay could be caused by copying the entire result set over from the server. (JDBC drivers commonly do this.) Setting the fetch size on the statement might well reduce this delay, but it might just spread the delay around.
Thanks to you all for your suggestions. I will follow that direction by setting FETCHSIZE and other things. The packaged SQL is on server, which I do not access, so I will have our DBA to find out the plan and other statistics.