direct path read temp
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: direct path read temp

Threaded View

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    direct path read temp

    I am working to find why one of the queries in my warehouse DB is taking long time. Past execution history for this session was 12 - 16 seconds where as since last 2 days it is running more than 15 hours.

    The only difference previous execution time and current execution is, multiple aggregation queries from BO are also executing same time.

    I trace the session with 10046 event and the only line popping thousands of times is like below.

    WAIT #4: nam='direct path read temp' ela= 3 file number=1014 first dba=404353 block cnt=1 obj#=300406 tim=12873056097821
    A query from v$session_wait is giving the below output.

    SID EVENT P1 P2 P3 STATE
    ---------- -------------------- ---------- ---------- ---------- -----
    669 direct path read temp 1008 166037 1 WAITED SHORT TIME
    I can see this session is using around 200K temp segment blocks.

    My pga_aggregate_target is set to 12G. When I check v$pgastat max pga allocated is just 4GB.

    Please guide me to find why this query is behaving vague. The first question popping in my mind is why this sessions is going to disk when there is sufficient PGA available. Is there any hidden parameters limiting to use PGA?

    Please post if you need any other information to help on this.

    by the way, Oracle DB is 10g R2 & HP UNIX.

    Thanks,
    Last edited by vnktummala; 02-02-2011 at 10:58 AM.
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width