Re: 10046 Trace Overhead

--
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...
> > 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
>