| |
 |
|
The Standard STATSPACK Report
Oracle Tips by Burleson Consulting
|
Oracle STATSPACK provides only a single report
to show activity between snapshots. You are prompted for a beginning
and an ending snapshot; the report then shows the differences between
those snapshots.
Note: You must ensure that you have not stopped and
re-started the database during the time between the starting and the
ending snapshot. Remember, a STATSPACK snapshot is nothing more than a
reflection of the v$ views at the time that the snapshot was taken,
and the v$ views are in-memory accumulators of database activity.
Because of the large size of the STATSPACK
report, we will divide it into small sections and explain each section
individually. Also, remember that the report was originally designed
for the exclusive use of Oracle Technical Support. Hence, many
parameters and values are completely undocumented and therefore
useless to those without the Oracle internal DSECT manuals.
Introductory Section
The first section of the STATSPACK report
describes the report environment. The “environment” includes the
database name, database version, and other standard information about
the size of the data blocks and the SGA component sizes.
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
------------
DIA2 1710902234 dia2 1 9.0.1.0.0 NO
oracle_db
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 1 08-Dec-01 20:22:49 7 6.7
End Snap: 2 08-Dec-01 20:35:17 8 7.3
Elapsed: 12.47 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 64M Std Block Size:
8K
Shared Pool Size: 112M Log Buffer:
512K
Load Profile Section
The Load Profile section provides an overall
metric of the load on the database during the snapshot period. The
most important statistics in this section are the total number of
physical reads and writes, because those numbers give you a good idea
of the amount of activity on the database.
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 426.92
159,668.00
Logical reads: 48.77
18,240.00
Block changes: 0.82
306.50
Physical reads: 0.04
14.00
Physical writes: 0.18
66.00
User calls:
0.04 14.50
Parses: 0.21
78.00
Hard parses:
0.02 8.00
Sorts:
0.22 82.00
Logons: 0.00
0.50
Executes: 0.41
154.50
Transactions: 0.00
% Blocks changed per Read: 1.68 Recursive Call %: 99.19
Rollback per transaction %: 0.00 Rows per Sort: 41.32
Instance Efficiency Section
The Instance Efficiency section provides
metrics about the general health of the SGA RAM region during the
snapshot period. Let’s take a closer look at some of the information.
-
Buffer hit ratio This is the percentage
of time that a requested data block resided inside a RAM data
buffer. You should always check the data buffer hit ratio; you
should also review the data buffer hit ratios for the
buffer_pool_keep and buffer_pool_recycle.
-
Library hit ratio This is the percentage
that an SQL statement was found cached in the RAM of the library
cache. If this metric is too low, additional memory can be added to
the library cache by increasing the shared_pool_size
parameter.
-
Buffer nowait ratio This ratio is
the percentage of time that a buffer was immediately available.
This statistic is for all of the data buffers in the SGA, as well as
for the library cache buffers. If this number falls too low,
further investigation is required: look at specific buffer hit
ratios to determine the buffer that needs more RAM storage.
-
Redo nowait ratio This ratio is
the percentage of times that redo space is immediately available. A
shortage in this metric may indicate that the redo log file sizes
are too small and that log switches are occurring too frequently.
Instance Efficiency percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.92 In-memory Sort %: 100.00
Library Hit %: 88.76 Soft Parse %: 89.74
Execute to Parse %: 49.51 Latch Hit %: 100.00
Parse CPU to Parse
Elapsd
%: 79.31 % Non-Parse CPU: 99.84
Shared Pool Activity Section
The Shared Pool Activity section shows RAM
memory usage within the shared pool. The most important area of the
shared pool is the library cache, and the % SQL with executions>1
shows the number of SQL statements that were successfully cached in
the library cache. The % Memory for SQL w/exec>1 shows the
amount of RAM required for the SQL cursor, normally for sorting and
hash joins. This memory is allocated from the pga_aggregate_target
region in Oracle9i.
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 16.89 17.58
% SQL with executions>1: 47.22 52.28
% Memory for SQL w/exec>1: 54.52 69.61
Wait Event Section
The Wait Event section is important because it
shows the types of operations that experienced wait conditions within
Oracle. As we know, wait conditions within Oracle may be caused by
locks, latches, waits on disk I/O, or waits on buffer blocks.
Top 5 Event Wait Section
The Top 5 Event Wait section is one of the most
important in the report because it shows how long Oracle is waiting
for resources. This section is the starting point for looking at
tuning the Oracle database. The Top 5 lists the highest-ranking events
waited for during the snapshot period. Here are the most important
sources of waits:
-
db
file scattered read This indicates full-table scan processing
where the sequential pre-fetch (set by the
db_file_multiblock_read_count parameter) is reading sequential
disk blocks. In an OLTP database, you should minimize full-table
scans, and tune the SQL to access the tables via indexes.
-
db file sequential read This indicates
requests for individual data blocks, such as the type of I/O that
would occur when a table is being accessed via an index.
Top 5 Wait Events
~~~~~~~~~~~~~~~~~
Wait % Total
Event Waits Time
(s) Wt Time
-------------------------------------------- ------------
----------- -------
control file parallel write 243
1 76.44
log file parallel write 11
0 8.79
db file sequential read 28
0 8.72
log file sync 3
0 3.04
rdbms ipc reply 2
0 1.35
-------------------------------------------------------------
Wait Detail Section
The Wait Detail section shows details of wait
conditions. It is critical to understanding the nature of any data
waits within the snapshot period.
The most common sources of waits are waits on
disk I/O and waits on network communication. Special attention should
be paid to SQL*Net message entries and db file entries.
Waits on db file entries can indicate disk I/O contention, and
SQL*Net message waits may indicate a network bottleneck.
Wait Events for DB: DIA2 Instance: dia2
Snaps: 1 -2
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ----------
------ --------
control file parallel write 243 0 1
3 121.5
log file parallel write 11 0 0
6 5.5
db file sequential read 28 0
0 3 14.0
log file sync 3 0
0 8 1.5
single-task message 2 0 0
5 1.0
control file sequential read 108 0 0
0 54.0
db file parallel write 10 10
0 0 5.0
SQL*Net message from client 25 0 1,064
42565 12.5
virtual circuit status 25 25 733
29307 12.5
SQL*Net message to client 26 0
0 0 13.0
-------------------------------------------------------------
Background Wait Events Section
The Background Wait Events section lists all
waits incurred by the Oracle background processes. This section is
rarely helpful when tuning a database because the behavior of the
background processes cannot be adjusted.
Background Wait Events for DB: DIA2
Instance: dia2 Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ----------
------ --------
control file parallel write 243 0 1
3 121.5
log file parallel write 11 0 0
6 5.5
rdbms ipc reply 2 0 0
5 1.0
control file sequential read 72 0 0
0 36.0
db file sequential read 1 0 0
0 0.5
db file parallel write 10 10 0
0 5.0
rdbms ipc message 740 729 2,185
2953 370.0
pmon timer 249 249 729
2928 124.5
smon timer 2 2 600
###### 1.0
-------------------------------------------------------------
SQL Section
The SQL section is arguably the most important
in the STATSPACK report, because inefficient SQL statements are the
primary cause of poor performance. As we know, STATSPACK gathers SQL
information inside the stats$sql_summary table based upon the
STATSPACK snapshot thresholds.
Once the SQL statements are gathered, the
section displays them according to the major SQL metrics, including
buffer gets, executions, and disk reads. For details on tuning
individual SQL statements, see Chapter 11.
SQL ordered by Gets for DB: DIA2 Instance:
dia2 Snaps: 1 -2
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources
used by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU
Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time
(s) Hash Value
--------------- ------------ -------------- ------ --------
--------- ----------
33,747 1 33,747.0 92.5 0.29
0.33 671327864
select -- substr(seg.tablespace_name,1,10) c1, su
bstr(tab.owner,1,10) c2, substr(tab.table_name
,1,30) c3, -- seg.bytes/1024 c4, -
- seg.extents c5 -- tab.max_e
xtents c6, -- tab.initial_extent/1024
60 3 20.0 0.2 0.00
0.00 1819073277
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ wh
ere d_obj#=:1 and p_obj#=obj#(+) order by order#
60 11 5.5 0.2 0.00
0.00 1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1
46 16 2.9 0.1 0.01
0.00 1705880752
SQL ordered by Reads for DB: DIA2 Instance: dia2 Snaps: 1 -2
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time
(s) Hash Value
--------------- ------------ -------------- ------ --------
--------- ----------
16 1 16.0 57.1 0.29
0.33 671327864
select -- substr(seg.tablespace_name,1,10) c1, su
bstr(tab.owner,1,10) c2, substr(tab.table_name
,1,30) c3, -- seg.bytes/1024 c4, -
- seg.extents c5 -- tab.max_e
xtents c6, -- tab.initial_extent/1024
1 2 0.5 3.6 0.00
0.01 365454555
select cols,audit$,textlength,intcols,property,flags,rowid from
view$ where obj#=:1
1 5 0.2 3.6 0.00
0.00 931956286
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#
1 9 0.1 3.6 0.00
0.01 2591785020
SQL ordered by Executions for DB: DIA2 Instance: dia2 Snaps: 1 -2
-> End Executions Threshold: 100
CPU per
Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
16 16 1.0 0.00 0.00
1705880752
select file# from file$ where ts#=:1
11 11 1.0 0.00
0.00 189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
0 2 0.00 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1
0 0 0.00 181436173
select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from id
l_sb4$ where obj#=:1 and version<=:2 and (part=0 or part=2) an
d piece#=0
0 0 0.00 315090940
update con$ set con#=:3 where owner#=:1 and name=:2
0 0 0.00 411033441
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_times
tamp,d_owner#, property)values (:1,:2,:3,:4,:5,:6, :7)
0 0 0.00 449029010
delete from dependency$ where d_obj#=:1
0 0 0.00 636388251
insert into ccol$(con#,obj#,intcol#,pos#,col#) values(:1,:2,:3,d
ecode(:4,0,null,:4),:5)
SQL ordered by Parse Calls for DB: DIA2 Instance: dia2 Snaps: 1 -2
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
0 0 0.00 641766606
insert into col$(obj#,name,intcol#,segcol#,type#,length,precisio
-------------------------------------------------------------
Instance Activity Section
The Instance Activity section shows the major
instance activity during the snapshot period. Many of the metrics in
this section are internal and undocumented, and valuable only to
people from Oracle technical support who possess the highly-secret
DSECT manual that show internal operations.
The major areas of importance include the CPU
usage metric, the SQL*Net messages, the chained row fetches, and PGA
usage. Let’s look at each one.
-
Table
Scans (short tables) This
is the number of full-table scans performed on small tables. On
short tables, it is optimal to perform full-table scans (rather than
to use indexes) and to place those small tables in the KEEP buffer
pool. Note that Table Scans (long tables) plus Table Scans (short
tables) is equal to the number of full-table scans.
-
Table Scans (long tables) This
is the total number of full-table scans performed on large tables.
This metric should be carefully evaluated to see if the full-table
scan can be removed by adding an index, or if the query speed might
be improved by invoking Oracle parallel query (OPQ).
-
Table Fetch by ROWID This
is usually the number of rows that were accessed using an index,
normally with nested loop joins.
-
Table Fetch by Continued Row This
is the number of rows that are chained to another block. However,
there are several anomalies in this metric, and a high value here
may not necessarily indicate chained rows. Of course, the value will
also be high if tables contain large objects with LOB, BLOB, or CLOB
datatypes, because those rows commonly exceed the database block
size, thereby forcing the row to chain onto multiple blocks.
Instance Activity Stats for DB: DIA2
Instance: dia2 Snaps: 1 -2
Statistic Total per
Second per Trans
--------------------------------- ------------------ --------------
------------
CPU used by this session 147
0.2 73.5
CPU used when call started 117
0.2 58.5
CR blocks created 2
0.0 1.0
DBWR checkpoint buffers written 131
0.2 65.5
DBWR checkpoints 0
0.0 0.0
DBWR revisited being-written buff 0
0.0 0.0
DBWR transaction table writes 3
0.0 1.5
DBWR undo block writes 26
0.0 13.0
SQL*Net roundtrips to/from client 24
0.0 12.0
background timeouts 729
1.0 364.5
buffer is not pinned count 21,448
28.7 10,724.0
buffer is pinned count 393
0.5 196.5
bytes received via SQL*Net from c 4,794
6.4 2,397.0
bytes sent via SQL*Net to client 5,423
7.3 2,711.5
calls to get snapshot scn: kcmgss 367
0.5 183.5
calls to kcmgas 18
0.0 9.0
calls to kcmgcs 258
0.3 129.0
change write time 1
0.0 0.5
cleanouts only - consistent read 0
0.0 0.0
cluster key scan block gets 11,257
15.1 5,628.5
cluster key scans 6,380
8.5 3,190.0
commit cleanout failures: buffer 0
0.0 0.0
commit cleanout failures:
callbac
4 0.0 2.0
commit cleanouts 70
0.1 35.0
commit cleanouts successfully com 66
0.1 33.0
consistent changes 3
0.0 1.5
consistent gets 35,667
47.7 17,833.5
consistent gets - examination 17,955
24.0 8,977.5
cursor authentications 4
0.0 2.0
data blocks consistent reads - un 3
0.0 1.5
db block changes 613
0.8 306.5
db block gets 813
1.1 406.5
deferred (CURRENT) block cleanout 23
0.0 11.5
enqueue releases 563
0.8 281.5
enqueue requests 563
0.8 281.5
enqueue timeouts 0
0.0 0.0
execute count 309
0.4 154.5
free buffer requested 73
0.1 36.5
immediate (CR) block cleanout app 0
0.0 0.0
immediate (CURRENT) block
cleanou
17 0.0 8.5
index fast full scans (full) 2
0.0 1.0
leaf node splits 6
0.0 3.0
logons cumulative 1 0.0
0.5
messages received 16
0.0 8.0
messages sent 16
0.0 8.0
no buffer to keep pinned count 15,385
20.6 7,692.5
no work - consistent read gets 14,788
19.8 7,394.0
opened cursors cumulative 154
0.2 77.0
parse count (hard) 16
0.0 8.0
parse count (total) 156
0.2 78.0
parse time cpu 23
0.0 11.5
parse time elapsed 29
0.0 14.5
physical reads 28
0.0 14.0
physical reads direct 0
0.0 0.0
physical writes 132
0.2 66.0
physical writes direct 0
0.0 0.0
physical writes non checkpoint 82
0.1 41.0
prefetched blocks 0
0.0 0.0
process last non-idle time 1,007,861,359 1,347,408.2
############
recovery blocks read 0
0.0 0.0
recursive calls 3,546
4.7 1,773.0
recursive cpu usage 56
0.1 28.0
redo blocks written 615
0.8 307.5
redo entries 354
0.5 177.0
redo ordering marks 0
0.0 0.0
redo size 319,336
426.9 159,668.0
redo synch time 2
0.0 1.0
redo synch writes 2
0.0 1.0
redo wastage 3,792
5.1 1,896.0
redo write time 0
0.0 0.0
redo writes 11
0.0 5.5
rollback changes - undo records a 0
0.0 0.0
rollbacks only - consistent read 2
0.0 1.0
rows fetched via callback 3,276
4.4 1,638.0
session connect time 1,007,861,359 1,347,408.2
############
session logical reads 36,480
48.8 18,240.0
session pga memory 1,446,820
1,934.3 723,410.0
session pga memory max 1,437,404
1,921.7 718,702.0
session uga memory 132,520
177.2 66,260.0
session uga memory max 711,284
950.9 355,642.0
shared hash latch upgrades - no w 2,865
3.8 1,432.5
sorts (memory) 164
0.2 82.0
sorts (rows) 6,777
9.1 3,388.5
summed dirty queue length 0
0.0 0.0
switch current to new buffer 1
0.0 0.5
table fetch by rowid 3,580
4.8 1,790.0
table fetch continued row 0
0.0 0.0
table scan blocks gotten 1,560
2.1 780.0
table scan rows gotten 3,323
4.4 1,661.5
table scans (long tables) 4
0.0 2.0
table scans (short tables) 20
0.0 10.0
transaction rollbacks 0
0.0 0.0
user calls 29
0.0 14.5
user commits 2
0.0 1.0
write clones created in
foregroun
0 0.0 0.0
-------------------------------------------------------------
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
