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.
