Re: 10046 Trace Overhead

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

Thanks

Matt Received on Wed Jul 21 2004 - 03:01:05 CDT