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