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 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 NUMBERThe 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.
This was first published in June 2005