Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: 10046 Trace Overhead
Note in-line
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated July 20th "Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0407202314.60bca3b6_at_posting.google.com...Received on Wed Jul 21 2004 - 14:47:34 CDT
> > 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...?
Yes
>
![]() |
![]() |