Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: 10046 Trace Overhead

Re: 10046 Trace Overhead

From: Jonathan Lewis <>
Date: Wed, 21 Jul 2004 19:47:34 +0000 (UTC)
Message-ID: <cdmh8m$fgj$>

Note in-line


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Optimising Oracle Seminar - schedule updated July 20th

"Matt" <> wrote in message

> > It makes a difference because enabling
> > sql_trace also switches on "rowsource
> > execution statistics", and for some SQL
> > statements the cost of collecting the
> > statistics if very high.
> Thanks for the feedback... This makes sense but I didn't expect the
> overhead to become a significant portion of the response time. The
> other confusing part is that the extra time is being attributed to the
> CPU call time (i.e. the 'c' statistic in the trace file) of one of the
> SQL statements.
That is correct - it's all CPU for the extra step in the rowsource opertion.
> This suggests that the extra time is due to the
> computation of these additional rowsource stats rather than writing to
> the trace file. If the overhead was due to the I/O to the trace file
> I would have expected the time to have been 'unaccounted for' (as per
> Cary Millsaps explanation in his recent book).
There may be some room for variation here - Cary identifies in-call and between call wait time (I may have his terminology wrong). I think it is possible for some trace write time to be included in between-call waits, and some to be 'unaccounted' because it is effectively in-call time. But that does apply to the point below, not the stats collection time.
> >
> > There is also the overhead of writing the trace
> > file, which can be significant if your code does
> > a lot of single-row processing (i.e. lots of
> > separate calls to the database) even if you
> > don't have the stats problem to deal with.
> There is a significant number of DB calls in the trace file so a
> certain amount of this time would be due to write I/O to the flat
> file. However as I said above I think that this 'write' time would be
> unaccounted for in the trace file (i.e. not attributed to any CPU
> stats or wait events).
> Would you agree...?
Received on Wed Jul 21 2004 - 14:47:34 CDT

Original text of this message