Thanks for the question, Chittaranjan Tandel.
Asked: September 26, 2016 - 4:19 pm UTC
Last updated: September 27, 2016 - 1:43 am UTC
Version: 11g r2
Viewed 10K+ times! This question is
You Asked
Hi Tom,
I have series of job which are configured daily & every day its been completed within expected time 7 mins. But last 4 days back this job is behaving like any thing its taking more than 2 hours. Its going for more sort operations & waiting for wait event direct path read temp. As I have ample of temp space in my db. please see the details below.
Tablespce : TEMP
Mb_total : 145582.984375
MB_Used : 5454
MB_Free : 140128.984375
PGA parameter
pga_aggregate_target integer 64424509440
Can u please suggest how to overcome this issue.
Rgds,
Chittaranjan T
and Connor said...
Many possible causes here
1) your plans have changed, so you've gone from an efficient plan to a less efficient (that involves lots of sorting)
2) other activities going on, so there is less pga available for sorting, so you've spilled to disk
3) your temp I/O is slow
I would tackle (1) first - take the worst queries, and see if the plans have altered, or are inefficient.
temp writes/reads are not a problem, they are *symptom* of a problem, namely a query that is perhaps choosing a poor plan. Start with that.
Is this answer out of date? If it is, please let us know via a Comment