Chapter Download

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: