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

From: Chris Dunscombe <>
Date: Wed, 30 Oct 2013 09:02:23 +0000 (GMT)
Message-ID: <>

Hi Jonathan,

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.



On Monday, 28 October 2013, 16:47, Jonathan Lewis <> 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.

Jonathan Lewis

From: Chris Dunscombe [] Sent: 28 October 2013 13:41
To: Jonathan Lewis; 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,


On Monday, 28 October 2013, 12:43, Jonathan Lewis <> 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.

Jonathan Lewis--

Received on Wed Oct 30 2013 - 10:02:23 CET

Original text of this message