Example STATSPACK Results from the
TPC-H SSD Runs
This section will provide a review of the
STATSPACKreport for the same query and data profiles against
the SSD array instead of the SCSI/ATA arrays. The following
listing shows the top five wait events from SSD run number five:a
Instance
Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 18.83 In-memory Sort %: 99.67
Library Hit %: 98.72 Soft Parse %: 98.55
Execute to Parse %: 67.94 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 150.00 % Non-Parse CPU: 100.00
Shared
Pool Statistics Begin End
------ ------
Memory Usage %: 47.18 48.16
% SQL
with executions>1: 79.52 84.83
% Memory
for SQL w/exec>1: 64.58 80.39
Top 5 Timed
Events
~~~~~~~~~~~~~~~~~~
% Total
Event
Waits Time (s) Ela
Time
-------------------------------------------- ------------
----------- --------
CPU
time
2,962 72.43
db file
scattered read 1,630,994 1,101
26.92
db file
parallel read 6,373 16
.39
db file
sequential read 80,346 7
.18
control file
parallel write 1,831 3 .07
-------------------------------------------------------------
The waits are still occurring for the I/O just
as they were for the SCSI and ATA runs; however, they are now less
than the CPU related waits. The I/O profile from the run five
report shows the majority of I/O going against the data and index
datafiles similar to the SCSI and ATA array results. The following
listing shows the I/O profile for the SSD array from the same
STATSPACK report as the listing immediately above:
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
648,400 115 0.6 11.4 0 0 0
/u02/oracle/oradata/dss/dss_data02.dbf
571,656 102 0.6 11.5 0 0 0
/u03/oracle/oradata/dss/dss_data03.dbf
581,510 103 0.6 11.5 0 0 0
DSS_INDEX
/u04/oracle/oradata/dss/dss_index01.dbf
4,029 1 0.2 2.7 0 0 0
/u06/oracle/oradata/dss/dss_index02.dbf
3,815 1 0.2 2.8 0 0 0
/u07/oracle/oradata/dss/dss_index03.dbf
3,944 1 0.2 2.8 0 0 0
PERFSTAT /u05/oracle/oradata/dss/perfstat01.dbf
3 0 0.0 10.0 803 0 0
SYSTEM /u01/oracle/oradata/dss/system01.dbf
50 0 0.4 3.2 350 0 0
TEMP
/u05/oracle/oradata/dss/temp1.dbf
19,568 3 1.3 13.2 20,621 4 0
UNDOTBS1 /u02/oracle/oradata/dss/undotbs101.dbf
0 0 60 0 0
/u04/oracle/oradata/dss/undotbs102.dbf
0 0 32 0 0
/u05/oracle/oradata/dss/undotbs103.dbf
4 0 0.0 1.0 132 0 0
-------------------------------------------------------------
From a review of the STATSPACK report from SSD
run six with reduced buffer cache, there is not much evidence of a
shift in the profile based on the loss of 500 megabytes of
preloaded data. This is shown in the following listing. In fact,
the hit ratio increased:
Instance
Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 20.31 In-memory Sort %: 99.66
Library Hit %: 93.73 Soft Parse %: 94.71
Execute to Parse %: 58.87 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 4.53 % Non-Parse CPU: 99.98
Shared
Pool Statistics Begin End
------ ------
Memory Usage %: 33.65 37.94
% SQL
with executions>1: 52.23 60.09
% Memory
for SQL w/exec>1: 35.76 54.60
Top 5 Timed
Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time
(s) Ela Time
-------------------------------------------- ------------
----------- --------
CPU
time
2,473 69.78
db file
scattered read 1,313,555 1,026
28.95
db file
parallel read 6,564 26
.72
control file
parallel write 1,194 10 .29
db file
sequential read 36,043 6
.17
-------------------------------------------------------------
While the shift is there, it is only a couple
of percentage points. Compare the time, in seconds, waiting for
the read events in the above listing with the ATA array results
listing shown in the previous section in this chapter titled
“Reviewing the STATSPACK Report for SSD.” The total wait events
and their associated wait times where reduced by a factor of 376
when compared to those in the ATA array results based on total
wait time for I/O related events.
The following section will cover more systems
that might benefit from a move to SSD base on analysis of waits
and file I/O characteristics.
