Reducing Disk Contention

• what disk contention is

• how to monitor disk activity

• how to reduce disk activity

What Is Disk Contention?

Monitoring Disk Activity

• Oracle file I/O statistics

• operating system statistics

Additional Information: For more information about monitoring and tuning I/O, refer to your Oracle operating system-specific documentation.

Monitoring Oracle Disk Activity

PHYRDS	The value of this column is the number of reads from each database file.
PHYWRTS	The value of this column is the number of writes to each database file.

Monitor these values over some period of time while your application is running with this query:

SELECT name, phyrds, phywrts

   FROM v$datafile df, v$filestat fs

   WHERE df.file# = fs.file#;

This query also retrieves the name of each datafile from the dynamic performance table V$DATAFILE. The output of this query might look like this:

NAME                                             PHYRDS    PHYWRTS

-------------------------------------------- ---------- ----------

/oracle/ora70/dbs/ora_system.dbf                   7679       2735

/oracle/ora70/dbs/ora_temp.dbf                       32        546

The PHYRDS and PHYWRTS columns of V$FILESTAT can also be obtained through SNMP.

The total I/O for a single disk is the sum of PHYRDS and PHYWRTS for all the database files managed by the Oracle instance on that disk. Determine this value for each of your disks. Also determine the rate at which I/O occurs for each disk by dividing the total I/O by the interval of time over which the statistics were collected.

Monitoring Operating System Disk Activity

Use your operating system facilities to examine the total I/O to your disks. Try to reduce any heavy access to disks that contain database files.

Distributing I/O

To reduce the activity on an overloaded disk, move one or more of its heavily accessed files to a less active disk. Apply this principle to each of your disks until they all have roughly the same amount of I/O. This is referred to as distributing I/O.

This section discusses guidelines for distributing I/O:

• Separate datafiles and redo log files on different disks.

• Separate, or "stripe", table data on different disks.

• Separate tables and indexes on different disks.

• Reduce disk I/O not related to Oracle.

Separating Datafiles and Redo Log Files

Place each datafile on a separate disk. Multiple processes can then access different files concurrently without disk contention.

Place each set of redo log files on a separate disk with no other activity. Redo log files are written by the Log Writer process (LGWR) when a transaction is committed. Information in a redo log file is written sequentially. This sequential writing can take place much faster if there is no concurrent activity on the same disk.

Dedicating a separate disk to redo log files usually ensures that LGWR runs smoothly with no further tuning attention. Performance bottlenecks related to LGWR are rare. For information on tuning LGWR, see the section "Reducing Contention for Redo Log Buffer Latches" .

Note: Mirroring redo log files, or maintaining multiple copies of each redo log file on separate disks, does not slow LGWR considerably. LGWR writes to each disk in parallel and waits until each part of the parallel write is complete. Since the time required for your operating system to perform a single-disk write may vary, increasing the number of copies increases the likelihood that one of the single-disk writes in the parallel write will take longer than average. A parallel write will not take longer than the longest possible single-disk write. There may also be some overhead associated with parallel writes on your operating system.

Dedicating separate disks and mirroring redo log files are important safety precautions. Dedicating separate disks to datafiles and redo log files ensures that both the datafiles and the redo log files cannot be lost in a single disk failure. Mirroring redo log files ensures that a single redo log file cannot be lost in a single disk failure.

"Striping" Table Data

To create a "striped" table:

1. Create a tablespace with the CREATE TABLESPACE command. Specify the datafiles in the DATAFILE clause. Each of the files should be on a different disk.

	CREATE TABLESPACE stripedtabspace

	   DATAFILE 'file_on_disk_1' SIZE 500K,

	      'file_on_disk_2' SIZE 500K,

	      'file_on_disk_3' SIZE 500K,

	      'file_on_disk_4' SIZE 500K,

	      'file_on_disk_5' SIZE 500K;

2. Then create the table with the CREATE TABLE command. Specify the newly created tablespace in the TABLESPACE clause.

Also specify the size of the table extents in the STORAGE clause. Store each extent in a separate datafile. The table extents should be slightly smaller than the datafiles in the tablespace to allow for overhead. For example:

	CREATE TABLE stripedtab (

	   col_1  NUMBER(2),

	   col_2  VARCHAR2(10) )

	   TABLESPACE stripedtabspace

	   STORAGE ( INITIAL 495K  NEXT 495K

	      MINEXTENTS 5  PCTINCREASE 0 );

These steps result in the creation of table STRIPEDTAB. STRIPEDTAB has 5 initial extents, each of size 495 kilobytes. Each extent takes up one of the datafiles named in the DATAFILE clause of the CREATE TABLESPACE statement. These files are all on separate disks. These 5 extents are all allocated immediately, since MINEXTENTS is 5. For more information on MINEXTENTS and the other storage parameters, see the Oracle7 Server Administrator's Guide.

Separating Tables and Indexes

Follow these steps to separate a table and its index:

1. Create a tablespace with the CREATE TABLESPACE command. Specify the datafile in the DATAFILE clause:

	CREATE TABLESPACE tabspace_1 
		DATAFILE 'file_on_disk_1';

2. Create the table with the CREATE TABLE command. Specify the tablespace in the TABLESPACE clause:

	CREATE TABLE tab_1 (

	   col_1  NUMBER(2),

	   col_2  VARCHAR2(10) )

	TABLESPACE tabspace_1;

3. Create another tablespace. Specify a datafile on another disk:

	CREATE TABLESPACE tabspace_2

	   DATAFILE 'file_on_disk_2';

4. Create the index. Specify the new tablespace:

	CREATE INDEX ind_1 ON tab_1 (col_1)

	   TABLESPACE tabspace_2;

These steps result in the creation of table TAB_1 in the file FILE_ON_DISK_1 and the creation of index IND_1 in the file FILE_ON_DISK_2.

Eliminating Other Disk I/O