Re: 10046 trace producing a new wait that is not normally there

Purely as a hypothetical:

Direct path reads may be overlapping and non-blocking, so there is never any time spent waiting for a read buffer to be filled.

Enabling the level 12 trace may interfere with Oracle's I/O strategy - either making it impossible to dispatch non-blocking reads, or causing file-system/hardware delay that interferes with the non-blocking reads - with the result that enabling the trace causes lots of lost time on I/O.

On another note, enabling tracing at this level on 10.2 also enables _rowsource_execution_statistics, and that can add a massive CPU overhead to certain classes of query (though 3 minutes to 12 hours not likely)

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: <ryan_gaffuri_at_comcast.net> To: <oracle-l_at_freelists.org> Sent: Tuesday, August 22, 2006 6:09 PM Subject: 10046 trace producing a new wait that is not normally there

We have an update statement that runs in 3 minutes with no trace or a level 1 trace, but ran for 2 hours and we killed it with a level 12 trace.

we got 20,000 'direct path read temp ' waits, but ONLY with a level 12 trace and ONLY on this update. We have not tested it in multiple environments. This is on 10.2 on solaris.

We didn't see this wait in v$active_session_history when we do not have a level 12 trace going. I did not see anything about this on metalink. Does this strike you as one of those bugs where its one thing in one version in one environment, etc...? I have gotten those occasionally?

10.3.5 direct path read and direct path read temp When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.
If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).

--

http://www.freelists.org/webpage/oracle-l Received on Tue Aug 22 2006 - 17:02:44 CDT