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 wait time,
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
-------------------------------------------------------
Another section of the STATSPACK report which
must be utilized for determining I/O characteristics is the data
file I/O section. The listing below shows the I/O section of the
STATSPACK report:
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s
Waits Wt(ms)
-------------- ------- ------ ------- ------------ --------
---------- ------
DSS_DATA /u08/oracle/oradata/dss/dss_data01.dbf
33,299,194 76 4.4 1.1 0
0 0
/u09/oracle/oradata/dss/dss_data02.dbf
29,693,254 68 4.3 1.1 0
0 0
/u10/oracle/oradata/dss/dss_data03.dbf
30,079,180 69 4.4 1.1 0
0 0
DSS_INDEX /u11/oracle/oradata/dss/dss_index01.dbf
159,622 0 6.2 1.0 0
0 0
/u13/oracle/oradata/dss/dss_index02.dbf
151,677 0 7.2 1.0 0
0 0
/u13/oracle/oradata/dss/dss_index3.dbf
11,549 0 0.1 1.0 0
0 0
/u14/oracle/oradata/dss/dss_index03.dbf
149,736 0 7.3 1.0 0
0 0
PERFSTAT /u05/oracle/oradata/dss/perfstat1.dbf
10 0 2.0 1.5 172
0 0
SYSTEM /u08/oracle/oradata/dss/system01.dbf
11,375 0 12.6 4.0 998
0 0
TEMP2 /u01/oracle/oradata/dss/temp021.dbf
138,030 0 0.3 3.4 46,979
0 0
/u02/oracle/oradata/dss/temp022.dbf
139,933 0 0.2 2.3 33,505
0 0
UNDO2 /u03/oracle/oradata/dss/undo021.dbf
56 0 4.5 1.0 4,162
0 17 30.6
/u04/oracle/oradata/dss/uno022.dbf
116 0 2.6 1.0 1,241
0 0
-------------------------------------------------------------
From this listing, one can see that the data
files are seeing the majority of the I/O stress showing the most
I/O per second and high read times. Once the data files are moved
to the SSD drive, these values change dramatically as shown by the
listing below:
File I/O
Stats for DB: DSS Instance: dss Snaps: 1 -2
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av
Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s
Waits Wt(ms)
-------------- ------- ------ ------- ------------ --------
---------- ------
DSS_DATA /u01/oracle/oradata/dss/dss_data01.dbf
35,975,161 602 0.3 1.0 0
0 0
/u02/oracle/oradata/dss/dss_data02.dbf
31,756,793 532 0.3 1.0 0
0 0
/u03/oracle/oradata/dss/dss_data03.dbf
32,279,053 540 0.3 1.0 0
0 0
DSS_INDEX /u11/oracle/oradata/dss/dss_index01.dbf
46,863 1 0.3 1.0 0
0 0
/u13/oracle/oradata/dss/dss_index02.dbf
50,032 1 0.3 1.0 0
0 0
/u14/oracle/oradata/dss/dss_index03.dbf
49,166 1 0.3 1.0 0
0 0
PERFSTAT /u12/oracle/oradata/dss/perfstat.dbf
37 0 18.6 1.0 92
0 0
SYSTEM /u08/oracle/oradata/dss/system01.dbf
5,358 0 4.3 2.0 1,549
0 0
TEMP /u12/oracle/oradata/dss/temp011.dbf
113,460 2 4.5 2.7 35,450
1 0
UNDOTBS /u08/oracle/oradata/dss/undo01.dbf
1,626 0 2.7 1.0 2,138
0 1 60.0
/u09/oracle/oradata/dss/undo02.dbf
537 0 3.4 1.0 631
0 0
-------------------------------------------------------------
I/O per second has increased by a factor of
five to six times, while the average read time has dropped from
four to five milliseconds to less than a millisecond. This
difference is reflected in the query times reported for earlier
runs.
As a matter of conjecture, moving data to the
SSD and then dropping selected indexes could actually improve
performance by eliminating slow index I/O, assuming they are on
SCSI or ATA disks, and moving that I/O instead to faster SSD full
table scans.
Based on this analysis, moving the data and
index tablespaces would give the best results since the highest
stress is on the data and index datafiles based on I/O readings
and on the waits.
db_file_scattered_reads
are full table or full index scans, and
db_file_sequential_reads
are single point reads of tables or indexes. In tests where the
undo and temporary tablespaces where moved to the SSD array for
this database, there were no appreciable gains in performance.
However, if the majority or a significant
percentage of the waits shown were due to undo segments or
temporary tablespace related activity, such as sort and hash
related waits or direct I/O related waits, moving them to the SSD
asset would make sense.
The most important fact to remember about
moving files to the SSD asset is that the only gain in performance
will be the percentage of time spent waiting on that asset. If the
amount waited on for a temporary tablespace is less than one
percent of the total application wait time, moving the temporary
tablespace to the SSD can only gain a maximum of one percent in
performance.
In the example presented so far, the physical
I/O in the system, most of which was directed at the data
tablespace datafiles, was causing the significant amount of system
wait time, so by moving the data tablespace datafiles, there were
significant gains in performance.
If there was sufficient room on the SSD asset,
moving the index tablespace data files would be the next logical
move.
The following listing shows the timing related
part of the header from another systems STATSPACKreport:
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------
Begin
Snap: 11 27-Oct-03 12:00:05 19 3.4
End Snap: 19 27-Oct-03 20:00:03 14 3.3
Elapsed: 479.97 (mins)
The time span is long enough to guarantee a
good sample. Users need to be sure that the STATSPACK used is not
just for a specific transaction but covers a period of normal
activity in the database. The following listing shows the
resulting wait profile:
Top 5
Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Ela Time
-------------------------------------------- ------------ --------
control file parallel write 9,306 30.15
db file scattered read 34,516 19.27
db file sequential read 86,156 18.18
SQL*Net message from dblink 15,882 13.99
CPU time 12.42
-------------------------------------------------------------
I/O related waits dominate this listing. In
this listing, it is odd that
control file parallel writes
are the predominant wait activity. This is in no doubt due to
their being collocated with the other database files. Logic would
seem to indicate that moving the control files to SSD assets would
be the best course of action. However, they are usually low I/O
files and if they were moved to another disk asset, their wait
contribution would probably disappear. This leaves the data and
index related I/O and reflects a need to look at the I/O profile
for the database. The following listing shows an excerpt from the
I/O section of the same report showing all datafiles with I/O
greater than 500.
File I/O
Stats for DB: TSTPROD Instance: TSTprod Snaps: 11 -19
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av
Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s
Waits Wt(ms)
-------------- ------- ------ ------- ------------ --------
---------- ------NAME_ADDRESS_JUNCTION_XI
G:\ORADATA\GLOBAL_NAMES\INDEXES\NAME_ADDRESS_JUNCTIO
502 0 6.2 1.0 218
0 0
NAME_ADDRESS_JUNCTION_XI
G:\ORADATA\GLOBAL_NAMES\INDEXES\NAME_ADDRESS_JUNCTIO
332 0 6.8 1.0 425
0 0
NAME_ADDRESS_JUNCTION_XU
G:\ORADATA\GLOBAL_NAMES\INDEXES\NAME_ADDRESS_JUNCTIO
797 0 6.3 1.0 637
0 0
NAME_ADDRESS_TBL
F:\ORADATA\GLOBAL_NAMES\TABLES\NAME_ADDRESS_TBL.DBF
480 0 9.0 1.0 496
0 0
NAME_ADDRESS_XID
G:\ORADATA\GLOBAL_NAMES\INDEXES\NAME_ADDRESS_XID.DBF
2,131 0 7.3 1.0 2,334
0 0
NAME_TBL
F:\ORADATA\GLOBAL_NAMES\TABLES\NAME_TBL.DBF
489 0 7.1 1.0 189
0 0
NAME_XID
G:\ORADATA\GLOBAL_NAMES\INDEXES\NAME_XID.DBF
574 0 8.1 1.0 733
0 0
RBS
H:\ORADATA\GLOBAL_NAMES\RBS1.DBF
5 0 0.0 1.0 2,198
0 0
SMALL_TBL
F:\ORADATA\GLOBAL_NAMES\TABLES\SMALL_TBL.DBF
36,288 1 3.4 14.3 15
0 44 2.3
SYSTEM
H:\ORADATA\GLOBAL_NAMES\SYSTEM01.DBF
75,856 3 0.8 1.0 164
0 0
TOOLS
D:\ORACLE\ORADATA\GLOBAL_NAMES\TOOLS01.DBF
474 0 4.6 1.0 1,076
0 0
USERS
D:\ORACLE\ORADATA\GLOBAL_NAMES\USERS01.DBF
423 0 6.1 4.8 415
0 0
XDB
D:\ORACLE\ORADATA\GLOBAL_NAMES\XDB01.DBF
603 0 2.9 1.0 0
0 0
From the I/O profile in this listing, moving
small_tbl or
system would yield the
biggest gain followed by moving
name_address_xid and then
RBS. However, anytime I/O to SYSTEM is as excessive as it
is in this case, its causes should be determined and eliminated.
