RE: How can you log number of rows returned by all queries?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <cdunscombe@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 28 Oct 2013 12:12:25 -0500
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 vspsa.id = 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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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?
Hi,
Looking for a way of "logging" the number of rows returned by all queries run
by a specific user, running 11.2.0.3?
Any ideas anyone?
Thanks,
Chris
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
