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.

Quote:

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.

Quote:

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,

See 10.3.5

http://download.oracle.com/docs/cd/B....htm#sthref955

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)

Quote:

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,

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

Quote:

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,

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.

Quote:

Originally Posted by PAVB

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,

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.

Thanks Paul.

Thanks,