RE: Sampling V$SESSTAT

Ian,

One thing to consider when using V$SESSTAT is that the background processes (DBWR, LGWR, etc.) generate a different set of counters while the shadow processes (acting on behalf of a user session such as SQL*Plus) generate a different set of counters. This leaves a lot of counters with a value of zero, depending on what that process does... I would suggest looking at the CLASS of these stats, such as 1: User, 2: Redo, 4: Enqueue, 8: Cache, 16: OS, 32: Parallel Server, 64: SQL, 128: Debug and deciding their usefulness.

Personally, I have used the following to cross-check findings from V$SYSTEM_EVENT and V$SESSION_EVENT:

Client:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                             27267
opened cursors current                                                  110
user commits                                                           2482
user calls                                                           196556
recursive calls                                                        6958
recursive cpu usage                                                     158
session logical reads                                                720410
CPU used by this session                                               6622
enqueue timeouts                                                      18576
enqueue requests                                                      24552
db block gets                                                        134215
consistent gets                                                      586195
physical reads                                                          148
db block changes                                                      72669
consistent changes                                                   238603
table scans (short tables)                                            21569
table scan rows gotten                                              1021998
table scan blocks gotten                                              61968
table fetch by rowid                                                 136078
leaf node splits                                                         12
parse time cpu                                                          611
parse time elapsed                                                      620
parse count (total)                                                   32335
execute count                                                         72734
bytes sent via SQL*Net to client                                   14201893
bytes received via SQL*Net from client                             22131957
SQL*Net roundtrips to/from client                                    215573
sorts (memory)                                                         1747
sorts (rows)                                                          30603

DBW1:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical writes                                                       89945
physical writes non checkpoint                                        81286

LGWR:

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo writer latching time                                               546
redo writes                                                         3004398
redo blocks written                                                 8451148
redo write time                                                      433785

If selected, I may be documenting this as part of a paper (and will touch base with you to see if you have anything to input at that time).

> -----Original Message-----
> From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU]
> Sent: Wednesday, September 04, 2002 11:18 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Sampling V$SESSTAT
>
>
> I want to start sampling this table, however collecting data
> on the 200+ statistics for each session would produce a
> prohibitively large result. I'm trying to pare the 225
> statistics to something more reasonable, but I cannot decide
> which ones to discard and which to record.
>
> Does anyone have a listing of the most useful statistics that
> they would like to share?
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_SLAC.Stanford.edu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).