| Previous | Table of Contents | Next |
The utlbstat utility samples the database and stores the results in a temporary table. When utlbstat is run and the database is sampled again, a report is made by comparing the differences in values between the begin snapshot and the end snapshot. The output is spooled to a file called report.txt. Although the report is very ugly and hard to read, the salient data is included. Listing 11.3 presents a sample of how the statistics report might appear.
Listing 11.3 The output from the bstat-estat Oracle utility.
oracle@myhost:mtty-61>more report.txt
SQLDBA>
SQLDBA> set charwidth 12
SQLDBA> set numwidth 10
SQLDBA> Rem Select Library cache statistics...The pin hit rate should be
SQLDBA> Remhigh.
SQLDBA> select namespace library,
2> gets,
3> round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3)
4> gethitratio,
5> pins,
6> round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3)
7> pinhitratio,
8> reloads, invalidations
9> from stats$lib;
LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI
------------ ------- ---------- ----- ---------- ------- ----------
BODY 9 1 9 1 0 0
CLUSTER 0 1 0 1 0 0
INDEX 8 .125 12 .333 0 0
OBJECT 0 1 0 1 0 0
PIPE 0 1 0 1 0 0
SQL AREA 490 .935 1675 .937 21 20
TABLE/PROCED 111 .865 281 .911 8 0
TRIGGER 0 1 0 1 0 0
8 rows selected.
SQLDBA>
SQLDBA> set charwidth 27;
SQLDBA> set numwidth 12;
SQLDBA> Rem The total is the total value of the statistic between the time
SQLDBA> Rem bstat was run and the time estat was run...Note that the estat
SQLDBA> Rem script logs on as "internal" so the per_logon statistics will
SQLDBA> Rem always be based on at least one logon.
SQLDBA> select n1.name "Statistic",
2> n1.change "Total",
3> round(n1.change/trans.change,2) "Per Transaction",
4> round(n1.change/logs.change,2) "Per Logon"
5> from stats$stats n1, stats$stats trans, stats$stats logs
6> where trans.name='user commits'
7> and logs.name='logons cumulative'
8> and n1.change != 0
9> order by n1.name;
Statistic Total Per Transact Per Logon
-------------------------- ------------ ------------ ------------
CR blocks created 45 15 9
DBWR buffers scanned 49630 16543.33 9926
DBWR checkpoints 13 4.33 2.6
DBWR free buffers found 49389 16463 9877.8
DBWR lru scans 1515 505 303
DBWR make free requests 1513 504.33 302.6
DBWR summed scan depth 49634 16544.67 9926.8
DBWR timeouts 38 12.67 7.6
background timeouts 114 38 22.8
calls to kcmgas 136 45.33 27.2
calls to kcmgcs 16 5.33 3.2
calls to kcmgrs 1134 378 226.8
cleanouts and rollbacks - c 4 1.33 .8
cleanouts only - consistent 90 30 18
cluster key scan block gets 266 88.67 53.2
cluster key scans 114 38 22.8
consistent changes 48 16 9.6
consistent gets 111477 37159 22295.4
cursor authentications 90 30 18
data blocks consistent read 48 16 9.6
db block changes 1292 430.67 258.4
db block gets 1286 428.67 257.2
deferred (CURRENT) block cl 112 37.33 22.4
enqueue releases 2277 759 455.4
enqueue requests 2282 760.67 456.4
enqueue timeouts 4 1.33 .8
execute count 605 201.67 121
free buffer inspected 146 48.67 29.2
free buffer requested 28628 9542.67 5725.6
immediate (CR) block cleano 94 31.33 18.8
immediate (CURRENT) block c 13 4.33 2.6
logons cumulative 5 1.67 1
messages received 1610 536.67 322
messages sent 1610 536.67 322
no work - consistent read g 109953 36651 21990.6
opened cursors cumulative 387 129 77.4
parse count 502 167.33 100.4
physical reads 28505 9501.67 5701
physical writes 213 71 42.6
recursive calls 6838 2279.33 1367.6
redo blocks written 607 202.33 121.4
redo entries 769 256.33 153.8
redo size 282490 94163.33 56498
redo small copies 723 241 144.6
redo synch writes 33 11 6.6
redo wastage 23060 7686.67 4612
redo writes 94 31.33 18.8
rollbacks only - consistent 44 14.67 8.8
session logical reads 112714 37571.33 22542.8
session pga memory 629940 209980 125988
session pga memory max 629940 209980 125988
session uga memory 14248 4749.33 2849.6
session uga memory max 228624 76208 45724.8
sorts (memory) 23 7.67 4.6
sorts (rows) 5957 1985.67 1191.4
table fetch by rowid 36144 12048 7228.8
table fetch continued row 31679 10559.67 6335.8
table scan blocks gotten 5884 1961.33 1176.8
table scan rows gotten 123829 41276.33 24765.8
table scans (long tables) 11 3.67 2.2
table scans (short tables) 19 6.33 3.8
user calls 188 62.67 37.6
user commits 3 1 .6
write requests 100 33.33 20
64 rows selected.
SQLDBA>
SQLDBA> set charwidth 27;
SQLDBA> set numwidth 12;
SQLDBA> Rem System wide wait events.
SQLDBA> select n1.event "Event Name",
2> n1.event_count "Count",
3> n1.time_waited "Total Time",
4> (n1.time_waited/n1.event_count) "Average Time"
5> from stats$event n1
6> where n1.event_count > 0
7> order by n1.time_waited desc;
Event Name Count Total Time Average Time
---------------------------- ------------ ------------ ------------
smon timer 2 0 0
buffer busy waits 3 0 0
control file sequential rea 8 0 0
rdbms ipc reply 12 0 0
log file sync 39 0 0
pmon timer 57 0 0
latch free 73 0 0
log file parallel write 93 0 0
db file parallel write 96 0 0
client message 190 0 0
db file scattered read 635 0 0
rdbms ipc message 1600 0 0
db file sequential read 25071 0 0
13 rows selected.
SQLDBA>
| Previous | Table of Contents | Next |