Avoiding I/O Disk Contention in Oracle 11gR2

By Richard Niemiec on April 19, 2013

Disk contention occurs when multiple processes try to access the same physical disk simultaneously. Disk contention can be reduced, thereby increasing performance, by distributing the disk I/O more evenly over the available disks. Disk contention can also be reduced by decreasing disk I/O.

To monitor disk contention, review the Database Files Metrics in Database Control. This Metric group contains two sets of metrics. The Average File Read Time and Average File Write Time apply to all datafiles associated with your database. If you find that one or two datafiles seem to have especially high values, you click one and then use the Compare Objects File Name link to view collected statistics between them. If they are both busy at the same time and are on the same disk, you may choose to relocate one datafile to another filesystem, if you are concerned about performance during that time.

You can also determine file I/O problems by running a query:

 

0141_001

Here is a partial query output:

 

0141_002

 

NOTE

You may also have SYSAUX01.DBF, USERS01.DBF, and EXAMPLE01.DBF.

A large difference in the number of physical writes and reads between disks may indicate that a disk is being overburdened. In the preceding example, filesystems 1–3 are heavily used whereas filesystems 4–5 are only lightly used. To get a better balance, you’ll want to move some of the datafiles. Splitting datafiles across multiple disks or using partitions would also help move access to a table or an index to an additional disk.

TIP

Query V$FILESTAT and V$DATAFILE to see how effectively datafiles have been balanced. Note that temporary tablespaces are monitored using V$TEMPFILE and V$TEMPSTAT.

 

Related Posts

Leave a Reply