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

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Mon, 28 Oct 2013 12:34:14 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF021C14C6_at_WIN02.hotsos.com>



Well of course v$session has the prior SQL_ID... duh.... prev_sql_id Change the query to something like this and I think you're close to what you want. I'm sure a few more details need to be worked out:

select vspsa.sql_id, username, last_output_rows from v$sql_plan_statistics_all vspsa, v$session vs where vspsa.sql_id = vs.prev_sql_id and vspsa.child_number = vs.sql_child_number and vspsa.id = 1
/

Here is a little test I just did:

SQL> _at_emp

    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                   

--------------- -------------------- ------------------------- ------------------------
440 David Austin440 DAUSTIN440 500 David Lee500 DLEE500 <removed a bunch of rows here, total really is 44> 27140 David Austin27140 DAUSTIN27140 27200 David Austin27200 DAUSTIN27200
SQL> _at_user_rows
SQL_ID        USERNAME                       LAST_OUTPUT_ROWS

------------- ------------------------------ ----------------
fcnap0m5qx68v SYSMAN b5bzzr3wtd5w0 OP 44
39m4sx9k63ba2 OP
SQL>
SQL> get user_rows
  1 select vspsa.sql_id, username, last_output_rows from v$sql_plan_statistics_all vspsa, v$session vs   2 where vspsa.sql_id = vs.PREV_SQL_ID and vspsa.child_number = vs.sql_child_number   3* and vspsa.id = 1
SQL>
-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke Sent: Monday, October 28, 2013 1:12 PM
To: cdunscombe_at_yahoo.com; Oracle-L Freelists Subject: RE: How can you log number of rows returned by all queries?

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 28 2013 - 18:34:14 CET

Original text of this message