Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

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!



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.