Re: How can you log number of rows returned by all queries?

Thanks for the detail, very helpful. Looking at a trace file on 11.1 via sql_trace=true you're right, the stat lines are only dumped on the first execution.

Cheers,

Chris

On Monday, 28 October 2013, 16:47, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote: Chris,

The rules for generating stat lines vary with versions of Oracle and depend on how you've enabled the tracing - in 11g you have options for stat lines on first execution, every execution, or never - and the default from dbms_monitor and dbms_session is first execution only; in earlier versions you only get stat lines when cursors close (and the plan had to be still in memory anyway).

I think that in 11g sql_trace = true follows the dbms_mponitor convention (first execution), but 10046 dumps on every execution.

If you have to generate the trace file, not dumping stat lines at all seems like a good idea (smaller overhead) - if you can write the code that sums the FETCH lines correctly.

Regards
Jonathan Lewis

From: Chris Dunscombe [cdunscombe_at_yahoo.com] Sent: 28 October 2013 13:41
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: How can you log number of rows returned by all queries?

Hi Jonathan,

Thanks for that idea re the FETCH lines.

BTW why aren't STAT lines always generated and how would you force it in those versions of Oracle which can be forced.

Thanks again,

Chris

On Monday, 28 October 2013, 12:43, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

Won't work - unless you ensure you get the STAT lines generated for every single execution of the statement, which can't be forced in all versions of Oracle.

If you're going to end up with trace files then maybe you could parse out the values for "r=" on FETCH lines where dep=0.

Regards
Jonathan Lewis--
http://www.freelists.org/webpage/oracle-l

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