Thanks for the question, Vikas.
Asked: November 06, 2019 - 8:10 pm UTC
Last updated: November 07, 2019 - 4:53 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi,
We have a query that runs on database every Friday. This used to complete within 2 mins.
However, from last few weeks the job times out. The application level time out is set to 4 mins. therefore, since query is not completing with 4 mins, the job is getting timed out.
We tried to increase the time out value to lets say 15 mins and it still times out. However the next run immediate after 15 mins time out completes the query within one minute.
Can you please explain how this happens ? I feel it has something to do with the memory parameter settings. We have increased the SGA_TARGET value but it did not resolve the issue but the execution after time out completed much faster.
Is this something to do with PGA work area size ? Please guide me on where to look in database to explain this.
Thank you
and Connor said...
Something has changed with your query - it could be the data in the tables it uses, it could be the optimizer statistics, but something has changed. A great way to *see* where the time is being lost is to see the execution plan used.
Get this by:
- Running "set serveroutput off"
- Adding the /*+ gather_plan_statistics */ hint to your query
- Run it
- Get the plan by running:
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
And post the output of it here. Ensure it includes the A(ctual)-rows as well as the (E)estimated-rows columns!
Is this answer out of date? If it is, please let us know via a Comment