Oracle 10g datafile I/O statistics

The Oracle10g database significantly improves the I/O subsystem, but it is still important to constantly monitor the I/O workload on the database. This excerpt, taken from the book "Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts" by Mike Ault, Harry Conway and Don Burleson, offers a few tips and scripts to do so.

The Oracle10g database significantly improves the I/O subsystem, but it is still important to constantly monitor the I/O workload on the database. This excerpt, taken from the book Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts by Mike Ault, Harry Conway and Don Burleson, offers a few tips and scripts to help you do so. Click here to view the entire chapter.

Oracle 10g Datafile I/O Statistics

The Oracle10g database significantly improves the I/O subsystem through the addition of such features as the Automatic Storage Management (ASM) facility. However, it is important to constantly monitor the I/O workload on the database because in a well tuned application, I/O remains a bound factor that can cause significant wait times in data access. I/O layout design is a complex process and includes the consideration of the following points:

  • Sufficient disk capacity for business needs.
  • Appropriate data protection level using RAID levels, hardware, LVM, etc.
  • Sufficient I/O throughput that does not exceed disk I/O bandwidth.
The AWR has several views that can be used to isolate datafile I/O related statistics as well as tablespace space usage statistics.

The dba_hist_filestatxs and dba_hist_tempstatxs views display information about I/O activity for data and temporary database files, respectively:

SQL> desc DBA_HIST_FILESTATXS

Name              Null?    Type
----------------- -------- -------------
SNAP_ID                    NUMBER
DBID                       NUMBER
INSTANCE_NUMBER            NUMBER
FILE#                      NUMBER
CREATION_CHANGE#           NUMBER
FILENAME                   VARCHAR2(513)
TS#                        NUMBER
TSNAME                     VARCHAR2(30)
BLOCK_SIZE                 NUMBER
PHYRDS                     NUMBER
PHYWRTS                    NUMBER
SINGLEBLKRDS               NUMBER
READTIM                    NUMBER
WRITETIM                   NUMBER
SINGLEBLKRDTIM             NUMBER
PHYBLKRD                   NUMBER
PHYBLKWRT                  NUMBER
WAIT_COUNT                 NUMBER
TIME                       NUMBER
The view, dba_hist_tempstatxs, has the identical structure. Both views can be queried to monitor overall database I/O activity for a particular snapshot interval grouped by tablespaces using the query db_tbsp_io_10g.sql.

Click here to view the script and the entire chapter.

This was first published in June 2005

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close