Oracle Scratchpad

My Stats

Sometimes I want to see how much work I’ve done in a little job. If I’ve got my snapshot packages installed this is easy, but for an ad hoc visit this isn’t always possible, In simple cases, then, and with the appropriate privileges granted, I will log off, log on, do something, then run the following script:

spool my_stats

select
        name, value
from
        v$statname      sn,
        v$mystat        ms
where
        ms.value != 0
and     sn.statistic# = ms.statistic#
;

select
        event, total_waits, total_timeouts, time_waited, max_wait
from
        (select /*+ no_merge */ sid from v$mystat where rownum = 1) ms,
        v$session_event         se
where
        se.sid = ms.sid
;

select
        stat_name, value
from
        (select /*+ no_merge */ sid from v$mystat where rownum = 1) ms,
        v$sess_time_model       tm
where
        tm.sid = ms.sid
and     tm.value != 0
;

spool off

Here’s a sample output after logging on to coalesce an index (Oracle 11.1.0.7):


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
logons cumulative                                                         1
logons current                                                            1
opened cursors cumulative                                                77
opened cursors current                                                    1
user calls                                                                7
recursive calls                                                         750
recursive cpu usage                                                       1
session logical reads                                                697394
CPU used when call started                                             1325
CPU used by this session                                               1325
DB time                                                               15701
user I/O wait time                                                    13777
session uga memory                                                  1717104
session uga memory max                                              1844200
messages sent                                                           247
session pga memory                                                  2360128
session pga memory max                                              2360128
enqueue requests                                                      28746
enqueue releases                                                      28745
physical read total IO requests                                       31693
physical read total bytes                                         259629056
physical IO disk bytes                                            259629056
cell physical IO interconnect bytes                               259629056
db block gets                                                        390018
db block gets from cache                                             390018
db block gets from cache (fastpath)                                   29090
consistent gets                                                      307376
consistent gets from cache                                           307376
consistent gets from cache (fastpath)                                   418
consistent gets - examination                                        306956
physical reads                                                        31693
physical reads cache                                                  31693
physical read IO requests                                             31693
physical read bytes                                               259629056
db block changes                                                     583333
change write time                                                       500
redo synch writes                                                         1

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch time                                                           2
free buffer requested                                                 74621
dirty buffers inspected                                                2954
pinned buffers inspected                                                  2
hot buffers moved to head of LRU                                     103240
free buffer inspected                                                 77421
commit cleanout failures: cannot pin                                      1
commit cleanout failures: callback failure                            15775
commit cleanouts                                                     112885
commit cleanouts successfully completed                               97109
write clones created in foreground                                        6
shared hash latch upgrades - no wait                                      2
calls to kcmgcs                                                       15451
calls to kcmgas                                                       70856
calls to get snapshot scn: kcmgss                                     28662
redo entries                                                         352138
redo size                                                         383270800
redo buffer allocation retries                                            1
redo log space requests                                                   2
redo log space wait time                                                  6
redo ordering marks                                                   42217
redo subscn max counts                                                49759
undo change vector size                                           250608616
no work - consistent read gets                                          214
rollback changes - undo records applied                                 486
transaction rollbacks                                                   135
immediate (CURRENT) block cleanout applications                      118460
commit txn count during cleanout                                      32909
active txn count during cleanout                                     290229
cleanout - number of ktugct calls                                     36086
Block Cleanout Optim referenced                                          48
commit batch/immediate requested                                        135
commit immediate requested                                              135
commit batch/immediate performed                                        135
commit immediate performed                                              135
table scans (short tables)                                               69
table scan rows gotten                                                 4378

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table scan blocks gotten                                                206
table fetch by rowid                                                      4
cluster key scans                                                         2
cluster key scan block gets                                               2
rows fetched via callback                                                 2
index fetch by key                                                        5
index scans kdiixs1                                                       4
sql area purged                                                           1
session cursor cache hits                                                68
session cursor cache count                                                8
buffer is not pinned count                                               19
workarea memory allocated                                              1520
parse time cpu                                                            1
parse count (total)                                                      78
parse count (hard)                                                        1
execute count                                                            77
bytes sent via SQL*Net to client                                       2139
bytes received via SQL*Net from client                                  744
SQL*Net roundtrips to/from client                                         5
sorts (memory)                                                            1

94 rows selected.

EVENT                              TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED   MAX_WAIT
---------------------------------- ----------- -------------- ----------- ----------
latch: cache buffers chains                  1              0           0          0
buffer busy waits                            3              0           0          0
log file switch completion                   2              0           6          5
log file sync                                1              0           2          2
db file sequential read                  31693              0       13777         55
SQL*Net message to client                   15              0           0          0
SQL*Net message from client                 14              0       40238      38511
events in waitclass Other                    1              0           0          0

8 rows selected.

STAT_NAME                                                             VALUE
---------------------------------------------------------------- ----------
DB time                                                           153342940
DB CPU                                                             13280000
parse time elapsed                                                    10751
hard parse elapsed time                                                2865
sql execute elapsed time                                          153320870
connection management call elapsed time                                3169

6 rows selected.

SQL>

2 Comments »

  1. [...] collection job   SGA Resizing: a script to report resizing operations (v$sga_resize_ops) What have I done: a script to report the work done, time spent, and wait events for my [...]

    Pingback by Simple scripts « Oracle Scratchpad — February 25, 2010 @ 6:27 pm BST Feb 25,2010 | Reply

  2. [...] article http://jonathanlewis.wordpress.com/my-stats/ (will open in a new window)  happens to be a report of the work done in an 11g database for an [...]

    Pingback by Coalesce « Oracle Scratchpad — June 27, 2010 @ 6:38 pm BST Jun 27,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,173 other followers