RE: How can you log number of rows returned by all queries?
Date: Mon, 28 Oct 2013 12:12:25 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF021C14C5_at_WIN02.hotsos.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 28 2013 - 18:12:25 CET