Skip to Main Content
  • Questions
  • Direct path read temp wait event issue

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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