15.2 How do I…Identify unbalanced I/O and I/O contention?

Problem

I am trying to improve database performance. I want to detect unbalanced physical disk loading and disk I/O contention. How do I identify I/O contention and load imbalance?

Technique

The UTLESTAT report contains a section summarizing file statistics. This section appears in Listing 15.1. Table 15.1 summarizes each column in this section of the UTLESTAT report.

Listing 15.1 An excerpt from the FILE I/O section of the UTLESTAT report

SVRMGR> select table_space, file_name,

2> phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time,

3> phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time,

4> megabytes_size megabytes

5> from stats$files order by table_space, file_name;


TABLE_SPACE FILE_NAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME
------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ----------
AUTO09 C:\AUTO09.DAT 0 0 0 0 0 0
RBS C:\RBS.DAT 8 8 10 8 8 8
SYSTEM C:\SYSO8HT.ORA 1204 1632 517 86 86 146
TEMP C:\TEMP.DAT 0 0 0 0 0 0
USERS C:\USERS.DAT 13 13 21 28 28 24

Table 15.1 An explanation of the columns in the FILE I/O section of the UTLESTAT report

Column Column Description TABLE_SPACE Name of the tablespace FILE_NAME Name of physical file name READS Number of physical reads completed BLKS_READ Number of blocks read Column Column Description READ_TIME Time in milliseconds for reads WRITES Number of physical writes completed BLKS_WRT Number of blocks written WRITE_TIME Time in milliseconds for writes MEGABYTES (not shown) Size of tablespace in megabytes

Steps

1. Make sure that the initialization parameter TIMED_STATISTICS is set to TRUE in the INIT.ORA file. If TIMED_STATISTICS is FALSE, then the read and write times in the UTLESTAT report are always 0.

To change the value of TIMED_STATISTICS, shutdown the database, change the INIT.ORA so that the parameter TIMED_STATISTICS is TRUE, and restart the database.

2. Repeat the steps in How-To 15.1 to create the statistics report. The section of this report that focuses on I/O performance appears in Listing 15.1. Alternatively, start SQL*Plus, connect as the WAITE user, and use the START command to load and execute the script CHP15_3.SQL. The script and its output appear in Figure 15.3.

3. Examine the section of the UTLESTAT report exhibited in Listing 15.1 or review the results of the CHP15_3.SQL script.

How It Works

Step 1 ensures that timing information is part of the UTLESTAT report. Step 2 generates the report which is the subject of analysis in Step 3. The goal is to evenly distribute reads and writes across all of the I/O devices. If moving a data file furthers this aim, then take the tablespace containing the data file offline and use the ALTER TABLESPACE command with the RENAME DATAFILE option to move the datafile to an I/O device with lighter traffic.

If the physical disk load is balanced, then you can identify contention across I/O devices by examining the READ_TIME and WRITE_TIME columns in the section of the UTLESTAT report reproduced in Listing 15.1. If the I/O devices on a database server are of the same speed rating and there are significant differences between the read or write times for different devices, then contention is a problem. The only way to address this is to balance loads for all applications on the system, including non-Oracle applications, or to acquire more or faster I/O devices.

Comments

Remember that the UTLESTAT report can only indicate disk I/O generated by the Oracle8 server and that UTLESTAT will not reveal I/O from other applications. This is why, if possible, it is ideal to configure database servers so that Oracle and non-Oracle files exist on separate devices.

The exact causes of load imbalance depend on the focus of the database. In online transaction processing (OLTP) systems featuring heavy delete activity, one cause of load imbalance can be the placement of all the rollback segments in a single tablespace on a single device. In decision support systems (DSS) with significant sort activity caused by ORDER BY or GROUP BY operations, load imbalance can exist because there is only a single temporary storage area and every user needs it at the same time.

Load imbalance and contention may be alleviated by using tablespace striping, which is the subject of the next How-To. Another method is to utilize table and index partitioning to spread large and heavily-used tables across multiple I/O devices. Information on table partitioning can be referred to in Chapter 4.