| |
 |
|
Oracle Tips by Burleson |
Finding the Baselines using
Oracle9i STATSPACK or Oracle10g AWR information
A critical point is Oracle databases are
always changing, and the database that was examined at 10:00 AM may
be completely different than the database examined at 3:00 PM. Does
this mean that a broad brush application of SSD is not valid?
When the performance of Oracle disk I/O is examined over time
periods, regular signatures appear when the I/O information is
aggregated by hours-of-the-day and day-of-the-week.
Most Oracle professionals use Oracle9i STATSPACK or Oracle10g AWR
information to gather these baselines. Once the repeating I/O trends
have been identified, it will be possible to apply a broad brush to
the use of SSD, placing the fast I/O devices where they will do the
most good.
I/O information can be captured at the file level, and this can give
insights into the best data files to place on super fast SSD. The
following script extracts the physical read information from the
Oracle 10g dba_hist_filestatxs view:
break on begin_interval_time skip
2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
;
The result below shows a running total of physical reads by datafile.
The snapshots are collected every half-hour. Starting from this
script, a where clause criteria could easily be added resulting in a
unique time-series exception report.
SQL> @reads
BEGIN_INTERVAL_TIME FILENAME PHYRDS
------------------------- ---------------------------------------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
164,700
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,082
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 472,008
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,794
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,123
24-FEB-04 11.30.18.296 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
167,809
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,248
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 476,616
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,795
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,244
25-FEB-04 12.01.06.562 AM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF
169,940
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,946
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 483,550
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,799
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,248
Of course, a little tweaking to the script could result in a report
on physical writes, read time, write time, single block reads, and a
host of other interesting metrics from the dba_hist_filestatxs view.
The above book excerpt is from:
Oracle RAC
& Grid Tuning with Solid State Disk
Expert Secrets for High Performance Clustered Grid Computing
ISBN:
0-9761573-5-7
Mike Ault, Donald K. Burleson
http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm |
