Re: 10046 Trace Overhead

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:cdmh8m$fgj$1_at_sparta.btinternet.com...
>
> 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...
> > > 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.

I wouldn't expect this to be correct for your version - though of course rowsource stats maybe backported and/or I may be incorrect. The simple way to tell would be to look at the trace file and see if in fact it has rowsource stats in it (recent 9.2 and 10 should be yes, 8i should be no).

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

I'd agree with this as well, but would be reluctant to attribute it to the rowsource calculations without seeing the trace file. The other advantage of seeing the trace file might be that anomalies in it might present themselves.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com