 |
|
Oracle Tips by Burleson |
Using the STATSPACK Report to Analyze
I/O Performance
The STATSPACKutility has shipped
with every Oracle Database since version 8.1.7.2. This commonality
makes it a logical choice for analyzing databases for use with SSD.
STATSPACK provides a plethora of I/O related statistics as well all
the needed wait interface statistics.
Installing STATSPACK
Installation of STATSPACK is actually quite
simple:
§
Create a tablespace in which to store the captured
STATSPACK statistics. Usually
100-500 Megabytes is enough, but one should certainly set aside 500
Megabytes if STATSPACK is to be used
on a continuing basis. This tablespace is normally called
perfstat.
§
From the sys
user, the statspack user,
table, and packaged creation script should be run. Usually this is:
$ORACLE_HOME/rdbms/admin/spcreate.sq;
however, this may be different on older Oracle releases.
§
Log in as the perfstat
user and execute the statspack.snap
procedure.
§
Following a reasonable collection interval, the
statspack.snap
procedure should be re-run.
§
From the perfstat
user, the STATSPACK reporting
script: $ORACLE_HOME/rdbms/admin/spreport, should be run for the interval over which statistics
were just collected.
Reviewing the STATSPACK Report for SSD
The STATSPACK report can run to dozens of pages
depending on the number of files, amount of SQL generated, and a
number of other parameters. However, in determining what files should
be placed on SSD assets, the most important section of the report is
the one dealing with the wait interface. On the first page of the
report, the top five wait events are shown. The listing below shows
the STATSPACK report for a run with the data files on the ATA drives.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits
Time (s) Ela Time
----------------------------- ------------
----------- --------
db file sequential read 93,211,687
398,236 96.80
CPU time
10,892 2.65
db file scattered read
344,252 1,512 .37
control file parallel write
141,759 583 .14
latch free
8,947 90 .02
-----------------------------------------------------
For those not familiar with the various waits
captured by the wait interface, Table 5.1 shows the major I/O related
waits:
|
WAIT EVENT |
DESCRIPTION |
|
Datafile I/O-Related Wait Events: |
|
|
db file sequential read |
Wait for single block read of a table or
index |
|
db file scattered read |
Wait for Multi-block read of a table or index
(full scan) |
|
db file parallel read |
Used when Oracle performs in parallel reads
from multiple datafiles to non-contiguous buffers in memory (PGA
or Buffer Cache). Similar to db file sequential read |
|
direct path read |
Used by Oracle when reading directly into PGA
(sort or hash) |
|
direct path write |
Used by Oracle when writing directly into PGA
(sort or hash) |
|
direct path read (lob) |
Read of a LOB segment |
|
direct path write (lob) |
Write of a LOB segment |
|
Controlfile I/O-Related Wait Events: |
|
|
control file parallel write |
Waiting for the writes of CF records to the
CF files |
|
control file sequential read |
Occurs on I/O to a single copy of the
controlfile |
|
control file single write |
Occurs on I/O to a single copy of the
controlfile |
|
Redo Logging I/O-Related Wait Events: |
|
|
log file parallel write |
Waiting for the writes of redo records to the
redo log files |
|
log file sync |
User session waits on this wait event while
waiting for LGWR to post after commit write of dirty
blocks |
|
log file sequential read |
LGWR background process waits for this event
while it is copying redo records from the memory Log Buffer cache
to the current redo group's member logfiles on disk. |
|
log file single write |
This Wait Event is I/O-related so it is
likely to appear together with 'log file parallel write' |
|
switch logfile command |
Wait cause by manual redo log switch command |
|
log file switch completion |
Wait generated while buffers are written
during log switch |
|
log file switch (clearing log file) |
Wait generated while buffers are written
during log switch |
|
log file switch (checkpoint incomplete) |
Wait generated while buffers are written
during log switch, only for when checkpoint takes longer than
normal |
|
log switch/archive |
Wait generated while buffers are written
during log switch |
|
log file switch (archiving needed) |
Wait generated while buffers are written
during log switch, only for when cannot write immediately to
archive log location |
Table 5.1:
I/O Related Wait Events
The db file
sequential readwait event dominates the events display in Top Five Timed Event
output above. From Table 5.1, this is due to single block reads of
indexes or tables. With nearly 97% of non-idle waittime, this is clearly what needs to be examined.
By looking at the listing below, where the results
from a STATSPACK taken with the data files on the SSD drives are
shown, one can see that if only the data files are moved to the SSD
asset, there will be a 93% drop, from 398,236 to 31,030, in I/O
related wait time for the same queries even though actual waits
remained virtually unchanged. CPU time has also dropped to 30% of its
previous value.
Moving the data files was predicated by review of
the I/O rate information from previous listings correlated with the
I/O waits seen in the STATSPACK listing.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits
Time (s) Ela Time
------------------------------- ------------
----------- --------
db file sequential read
99,991,232 31,030 88.65
CPU
time 3,343 9.55
control file parallel write
19,300 375 1.07
db file scattered read 174,949
157 .45
control file sequential read
7,764 50 .14
-------------------------------------------------------
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my 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. |
|
