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>
[...] 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 UTC Feb 25,2010 |
[...] 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 UTC Jun 27,2010 |