Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter