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

From: Yong Huang <yong321_at_yahoo.com>
Date: Tue, 29 Oct 2013 10:15:31 -0700 (PDT)
Message-ID: <1383066931.61544.YahooMailNeo_at_web184804.mail.gq1.yahoo.com>

I want to add that the statistic "table scan rows gotten" is not ncessarily the same as how many rows are returned to the client. For example, select count(*) from sometable returns 1 row. But the stat increments by number of rows in the table if the SQL uses full table scan. So you need to clarify for us "the number of rows returned by all queries...". Yong Huang

---

 From: Yong Huang <yong321_at_yahoo.com> To: "cdunscombe_at_yahoo.com" <cdunscombe_at_yahoo.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, October 29, 2013 11:32 AM Subject: Re: 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 11.2.0.3?

Chris,

How about checking statistic 'table scan rows gotten' for the user's sessions? Join v$session and v$sesstat (and v$statname if you don't want to hardcode statistic# for your version).

The problem with this approach is that if the session exited, you lose its data. You either have to sample more frequently (and replace the value for the same session sampled last time in your record) to minimize this problem, or make sure most other sessions rarely disconnect and get the value for this stat from v$sysstat instead, minus the values for the long-connecting sessions.

Why number of rows? How about logical reads, which you can get from dba_audit_(session|trail) if you have audit turned on, which is default in 11g?

Yong Huang

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 29 2013 - 18:15:31 CET