 |
|
Oracle Tips by Burleson |
When a
System Will Not Benefit From Moving to SSD
The move to SSD assets from standard SCSI, ATA or
SATA disks can be a blessing when it answers a specific performance
problem related to disk I/O saturation. However, one must be careful
when diagnosing the I/O related problems on a system. This section
will provide some example STATSPACKs and use them to show
whether or not there is a benefit to moving to SSD assets.
The following listing includes an events report
for a system that will not benefit from SSD:
Top 5 Timed
Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
CPU
time 1,127
73.25
global cache cr
request 213,187 122 7.95
db file
sequential read 152,521 96 6.27
control file
sequential read 118,104 78 5.06
SQL*Net message
from dblink 890 38 2.48
-------------------------------------------------------------
The system in the above listing spends 73% of its
time in the CPU with only 11 percent of the time spent waiting on
disks. If the system were tuned to eliminate the CPU bottleneck,
chances are the bottleneck will move to the disks and at that time, it
would benefit from SSD technology. As the system in the listing stands
right now, moving to SSD could actually hurt performance as it would
place more stress on the already over worked CPU assets.
In the next example, the move to SSD might be
beneficial since there is reserve CPU capacity of 40%, and it is
spending the other wait time waiting on disks as shown in the
following listing:
Top 5 Timed
Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
CPU
time 1,300
60.46
db file
sequential read 342,625 616 28.67
db file
scattered read 12,986 66 3.07
log file
parallel write 2,889 65 3.03
db file parallel
write 1,080 59 2.75
-------------------------------------------------------------
The system in the above listing indicates index
and table stress as well as stress on log files. Assuming that the
sample amount of time in the STATSPACK is representative of the
overall system performance, the DBA needs to look further to determine
what should be moved, tables or indexes, to the SSD asset. The file
I/O profile from this same report is shown in the following listing:
Tablespace
------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms)
Blks/Rd Writes Writes/s Waits Wt(ms)
--------------
------- ------ ------- ------------ -------- ---------- ------
SRCD
12,680 4 6.7 1.0 18,943 6 0
0.0
SYSTEM
30,282 10 3.0 2.5 623 0 0 0.0
UNDOTBS1
14 0 35.7 1.0 28,733 9 0 0.0
SRCX
2,799 1 4.5 1.0 18,038 6 0
0.0
NOMADD
16,604 5 1.8 1.0 8 0 0 0.0
TST_GLOBALX
7,560 2 1.6 1.0 18 0 0 0.0
TST_GLOBALD
6,242 2 2.0 1.2 36 0 0 0.0
XDB
5,636 2 1.5 1.0 4 0 0 0.0
REEX
4,240 1 2.0 1.0 4 0 0 0.0
ZENX
3,812 1 2.1 1.0 4 0 0
0.0
ESRX
3,656 1 1.6 1.0 4 0 0 0.0
The heavy-hitters in this listing are the
srcd,
system,
nomad,
tst_globallx and
tst_globald tablespaces. The
actual report from which the listing is extracted is over ten pages
long for this section on datafiles, but these are the largest
contributors to the I/O profile. Analysis of the system showed
improper use of the SYSTEM tablespace. Once this was corrected, the
others were left as the I/O stress points. Moving the heavy hitters to
an SSD asset would do the following for this system:
-
Shift the load to the CPUs
-
Reduce I/O stress on the I/O subsystem allowing
other datafiles to be accessed more efficiently.
-
Speed access to the data/indexes contained in
the moved datafiles.
The above benefits might actually provide greater
than the percentage benefit quoted above. One of the other waits deals
with redo log files, specifically
log file parallel write
. Since this is a log file
write specific wait, moving the redo logs would also show some benefit
but not as great as that shown by moving tables and indexes.
The following listing provides a false positive
indicator for use of SSD:
Top 5 Timed
Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
db file
sequential read 6,261,550 691 96.72
control file
parallel write 1,274 19 2.73
CPU
time 2
.24
db file parallel
write 28 1 .14
db file
scattered read 2,248 1 .12
-------------------------------------------------------------
What is a false positive indicator? In this case,
the STATSPACK seems to indicate that the database is doing a lot of
full table scans and that this is 96-97 percent of wait times, which
should indicate a move to SSD would be beneficial. However, a review
of the entire report should be conducted. The header for the file is
shown in the following listing:
STATSPACK report
for
DB Name
DB Id Instance Inst Num Release Cluster Host
------------
----------- ------------ -------- ----------- ------- -----------
TSTPRD
3265066449 tstprd 1 9.2.0.3.0 NO test08
Snap
Id Snap Time Sessions Curs/Sess Comment
-------
------------------ -------- --------- -------------------
Begin
Snap: 3 09-Nov-03 13:20:20 10 2.1
End
Snap: 4 09-Nov-03 14:26:01 10 2.1
Elapsed: 65.68 (mins)
Cache Sizes
(end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K
The tiny size for the buffer cache and shared pool
should be noted along with the restricted time period monitored.
Unless the server has severe memory limitations, the company using
this database would be better off increasing the memory allocated to
the instance and then looking at SSD if the waits are still an issue.
The small elapsed time indicates that this STATSPACK run was probably
for a specific transaction and is not indicative of full system load.
While a move to SSD may benefit many systems, the
DBA should carefully review all information to ensure that another fix
is not more appropriate.
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. |
|
