RE: How can you log number of rows returned by all queries?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <yong321@xxxxxxxxx>, "'Stephens, Chris'" <Chris.Stephens@xxxxxxx>, <cdunscombe@xxxxxxxxx>
- Date: Tue, 5 Nov 2013 11:20:24 -0500
+1 on the logoff trigger idea for session metrics
Regarding the "hardcoding" issue, some find it a useful work-around to code
up your own release specific xv$<viewname> view that is bare bones but
release specific so that you can recode the view easily (except in the rare
event that Oracle changes the meaning of a v$<viewname>; if the underlying
x$ values change, but you've stuck to things that do appear in v$ places,
then you can trace the required revisions needed for your custom view.)
Quite often, if you want one specific fact from a join of v$ views, a much
more efficient custom view can be created without much effort.
Using the xv$ prefix for these views (which I think is my convention
invention and which I recommend) makes it trivial to review v$ changes in a
patch or release to see whether you need to do anything about it. If you
don't like xv$, you still probably want to use something consistent to make
it easy to find them.
I'm not suggesting that every ad hoc use of joins of v$ objects be thus
optimized, but if you have a case such as grabbing a stat or two for every
session at logoff (or especially logon) then it is more likely to be a
worthwhile exercise.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Yong Huang
Sent: Tuesday, November 05, 2013 9:18 AM
To: Stephens, Chris; cdunscombe@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: How can you log number of rows returned by all queries?
Yes. A logoff trigger is a smart idea. Thanks.
Again, this statistic records number of table rows scanned, not number of
rows sent to the client (e.g. select count(*) ... returns only 1 row after
scanning many).
Yong
_____
From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
To: "yong321@xxxxxxxxx" <yong321@xxxxxxxxx>; "cdunscombe@xxxxxxxxx"
<cdunscombe@xxxxxxxxx>
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, November 5, 2013 7:40 AM
Subject: RE: How can you log number of rows returned by all queries?
" The problem with this approach is that if the session exited, you lose its
data"
Logoff trigger?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Yong Huang
Sent: Tuesday, October 29, 2013 11:32 AM
To: cdunscombe@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to
which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader
of this message is not the intended recipient or the employee or agent
responsible for delivering this message to the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply.
Other related posts:
