Re: Trace file does not list detailed timings

Had to use 3009359.8 to find it on Metalink

I see that it is "higher CPU use if sql_trace = true", fixed in 9.2.0.5 - possibly the fix was to stop sql_trace from enabling _rowsource_execution_statistics.

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/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 10th Jan 2006

• Original Message ----- From: "Cary Millsap" <cary.millsap_at_hotsos.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Saturday, January 28, 2006 7:55 PM Subject: RE: Trace file does not list detailed timings

Be aware of bug 3009359.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba  

Hotsos Symposium 2006 / March 5-9 / Dallas Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, January 27, 2006 4:43 PM
To: oracle-l_at_freelists.org
Subject: Re: Trace file does not list detailed timings

Is this a case of: 9i and

    It used to show stats when I switched on SQL_TRACE,     but it doesn't any more after an upgrade.

If so, then it's a change in default behaviour. In earlier versions of 9, when you enabled sql_trace, you implicitly enabled "_rowsource_execution_statistics". The most recent versions don't do this. If you want them, then

    alter session set "_rowsource_execution_statistics"=true;

The cost is significant in some classes of query - nested loops seem to be the worst. I have an example where the CPU time goes up by a factor of three when stats are on.

Regards

Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l