 |
|
Oracle Tips by Burleson |
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:
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.
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. |
|
