Oracle Database Tips by Donald Burleson

Finding the Baselines

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 as shown in Figure 1.9. 

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_filestatxsview:

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.

See code depot for complete scripts

This is an excerpt from the book Oracle RAC & Tuning with Solid State Disk.  You can get it for more than 30% by buying it directly from the publisher and get immediate access to working code examples.

Oracle RAC & Tuning with Solid State Disk Expert Secrets for High Performance Clustered Grid Computing Includes Oracle10g! Get immediate access to working code examples Buy it now for 30% off - Only $9.95

---

HTML-DB support:

For HTML-DB development support just call to get an Oracle Certified professional for all HTML-DB development projects.