DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: direct path read temp

  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.

  2. #2
    Join Date
    May 2002
    Posts
    2,645

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thank you Stecal.

    My I/O sub system doesn't support async I/O.

    Query from v$session_wait is giving the same output as I posted initially. Output from from v$sesstat is below (top few rows)

    SID STATISTIC# NAME VALUE
    ---------- ---------- ---------------------------------------------------------------- -------------------
    307 58 physical read bytes 86764797952
    307 39 physical read total bytes 86764797952
    307 42 physical write total bytes 8617197568
    307 66 physical write bytes 8617197568
    307 18 session connect time 1296652871
    307 19 process last non-idle time 1296652871
    307 25 session pga memory 1003279648
    307 26 session pga memory max 1003279648
    307 20 session uga memory 966983976
    307 21 session uga memory max 966983976
    307 251 table scan rows gotten 273984187
    307 351 sorts (rows) 132516915
    307 54 physical reads 5295703
    307 9 session logical reads 3520034
    307 51 consistent gets from cache 3519936
    307 50 consistent gets 3519936
    307 180 no work - consistent read gets 3518024
    307 252 table scan blocks gotten 3516499
    307 93 free buffer requested 3317729
    307 55 physical reads cache 3317707
    307 114 physical reads cache prefetch 3165814
    307 97 free buffer inspected 2533775
    307 37 physical read total IO requests 2117740
    307 57 physical read IO requests 2117740
    307 56 physical reads direct 1977996
    307 121 physical reads direct temporary tablespace 1977996
    307 199 Cached Commit SCN referenced 1294402
    307 332 workarea memory allocated 943529
    307 123 physical writes direct temporary tablespace 525952
    307 67 physical writes non checkpoint 525952
    307 63 physical writes direct 525952
    307 62 physical writes 525952
    307 343 bytes sent via SQL*Net to client 144323
    307 38 physical read total multi block requests 116503
    307 17 user I/O wait time 85087
    307 344 bytes received via SQL*Net from client 47542
    307 13 DB time 41265
    307 40 physical write total IO requests 39960
    307 65 physical write IO requests 39960
    307 41 physical write total multi block requests 39955
    307 96 hot buffers moved to head of LRU 22744
    307 337 parse time elapsed 22677
    307 7 recursive calls 19718
    307 134 redo size 14744
    307 12 CPU used by this session 13032
    307 11 CPU used when call started 12295
    307 94 dirty buffers inspected 8915
    307 32 enqueue releases 5173
    307 30 enqueue requests 5173
    307 176 undo change vector size 4336
    307 328 buffer is pinned count 2998
    307 116 prefetched blocks aged out before use 2654
    307 253 table fetch by rowid 2021
    307 329 buffer is not pinned count 1759
    307 8 recursive cpu usage 1583
    307 336 parse time cpu 1579
    307 52 consistent gets - examination 1532
    307 131 calls to get snapshot scn: kcmgss 1250
    All the objects in the query are with default degree and not using any parallel hint in the query. For sure there are too large sorts but why are they going to disk when I have huge pga_aggregate_target (12GB) is my concern.

    Are there any hidden parameters which controls the PGA usage for each session?

    Any inputs are highly appreciated.

    Thanks,
    Vijay Tummala

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

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    also, I am trying to generate 10053 trace but trace file is recorded for first few minutes and stopped recording any thing. This is the last line in my 10053 trace

    Query block (9fffffffbc5535e0) unchanged
    Query block (9fffffffbf3e7c48) before join elimination:
    SQL:******* UNPARSED QUERY IS *******
    Please note that the session is active since last 2 hours but no trace is generating.

    Is the information about disk sorts not record in 10053 level 1?

    Thanks,
    Vijay Tummala

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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I would tkprof the trace file and focus on Wait Events section of it - you may want to include SYS=N option.

    Risking to be stating the obvious please include...
    alter session set max_dump_file_size = unlimited;
    alter session set timed_statistics = true;
    alter session set sql_trace = true;
    ...before actual alter session set events blah blah blah

    Trace may be hitting max_dump_file_size value so it appears not to be tracing - that's the reason of the first command.

    Also check you have enough space on dump file directory.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Quote Originally Posted by PAVB View Post
    I would tkprof the trace file and focus on Wait Events section of it - you may want to include SYS=N option.

    Risking to be stating the obvious please include...
    alter session set max_dump_file_size = unlimited;
    alter session set timed_statistics = true;
    alter session set sql_trace = true;
    ...before actual alter session set events blah blah blah

    Trace may be hitting max_dump_file_size value so it appears not to be tracing - that's the reason of the first command.

    Also check you have enough space on dump file directory.
    Thanks for the reply Paul.

    all the parameters are already taken care except sql_trace. I believe when we are explicitly generating 10046 / 10053 trace we don't need to set sql_trace=true. Please update if my understanding is wrong.

    any way, I will set it for session and generate 10053 trace again.

    Also, I have a quick question for you. Is all the parallel slaves does direct path read? or it happens only when parallelism is enabled on object instead of parallel hint?

    Thanks,
    Vijay Tummala

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

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    It happens when doing parallel full table scans - no matter how parallelism got triggered.

    Direct path read operations move data directly to PGA, reads are async so Oracle has to check no outstanding reads are pending then you see the wait events.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks Paul.

    Thanks,
    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