Re: 10046 Trace Overhead

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 20 Jul 2004 15:37:02 +0000 (UTC)
Message-ID: <cdje6u$r29$1@hercules.btinternet.com>

Which version of Oracle ?

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.

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.

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



"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0407200731.6c91c007_at_posting.google.com...


> I encountered some mysterious behaviour today when trying to diagnose

> a performance problem with an online screen in one of our

> applications.

>

> This specific user action takes 3.5 seconds without tracing enabled.

> In order to determine where this time was being spent I enabled a

> 10046 trace (level 8) to catch the wait events.

>

> The elapsed time jumped up to 17 seconds with the trace enabled.

>

> At first I couldn't believe it because its my understanding that the

> overhead of extended SQL trace should be mimimal.

>

> So I did some more investigation and found that this overhead

> increased or decreased depending on the level of SQL trace (I tried

> level 1, 8 and 12).

>

> Level 1 tracing caused the elapsed time to be 5 seconds, and level 12

> caused a 24 second total elapsed time

>

> Has anyone encountered behaviour like this before...?

>

> Any feedback would be greatly appreciated...

>

> Matt

Received on Tue Jul 20 2004 - 10:37:02 CDT