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

On top of that, we have just filed a case with Oracle about a trace that can cause self-blocking mutexes (requestor = holder). We were using DBMS_MONITOR.SESSION_TRACE_ENABLE (with waits and binds) on a session that had already started. Hard to reproduce - only seems to work for a certain login running a specific program. But none-the-less, the whole idea of a mutex is to be very very fast, so we are still waiting to hear from Oracle why putting this trace on has such a huge impact.

On 8/22/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> 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
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l