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

From: Ric Van Dyke <>
Date: Mon, 28 Oct 2013 12:12:25 -0500
Message-ID: <>

Getting the number of rows returned by a SQL statement is rather easy: select sql_id, last_output_rows, id from v$sql_plan_statistics_all where last_output_rows is not null and id = 1;

However linking this back to the user is a little tricky. You could like this view back to v$session for example with something like this:

select vspsa.sql_id, username, last_output_rows from v$sql_plan_statistics_all vspsa, v$session vs where vspsa.sql_id = vs.sql_id and vspsa.child_number = vs.sql_child_number and last_output_rows is not null and = 1

Since you want to log this as it happens I think something like this could work. The *tricky* part is that this is only going to show you stuff that is happening RIGHT NOW. The main problem is if I run this in the same session what I see is the number of rows returned for running this query, which of course isn't what I want. I really need to know the SQL_ID of the query that I want the stats for this to work correctly. More or less the query I ran BEFORE this one. This is the right idea, but would need to be fleshed out much more.

  • Note: The stat in the v$sql_plan_statistics_all is basically the same type of data you get from the stat lines in 10046 tracing. Also, be warned this view (v$sql_plan_statistics_all) is rather *expensive* to select from. It gets data from a few different X$ tables. Just be mindful of this as you write up something using it.

Ric Van Dyke
Education Director
Hotsos Enterprises, Ltd
Hotsos Symposium March 2-6 2014. Be there.

-----Original Message-----

From: [] On Behalf Of Chris Dunscombe Sent: Monday, October 28, 2013 4:42 AM
To: Oracle-L Freelists
Subject: How can you log number of rows returned by all queries?


Looking for a way of "logging" the number of rows returned by all queries run by a specific user, running

Any ideas anyone?



-- Received on Mon Oct 28 2013 - 18:12:25 CET

Original text of this message