Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

The Approach to Locating Hot Disks Oracle Tips by Burleson

For other operating environments, we are concerned whenever we see a backlog of I/O tasks waiting to access data on a single disk. For other operating systems, the iostat utility can be used to detect I/O issues.

Once you've identified the hot disks, look closely to find out which files and tables on the disks experience most of the activity so that you can move them to less-active disks as needed. The actual process of identifying hot files and disks involves running data collection utilities, such as STATSPACK and the UNIX iostat utility, and then using the collected I/O data to pinpoint the sources of excessive I/O measurements.

Here are the cardinal rules for disk I/O:

• There is a difference between a busy disk and a disk that is waiting for I/O to complete. In the next section we will explore the UNIX iostat utility and show how you can identify busy disks.

• If you are using RAID such as RAID 0+1, the Oracle data blocks will be spread randomly across all of the disks, and load will rise and fall in a uniform fashion.

• Senior Oracle DBAs often prefer not to implement RAID striping so that they have more control over the disk I/O subsystem.

• Many disk arrays such as EMC provide sophisticated disk monitoring tools such as Open Symmetrics Manager and Navistar. These tools report on more than simple disk waits, and highlight contention for disks, channels, and disk adapters.

Now that we understand the basic principles behind locating hot disks, let's see how STATSPACK can be extended to capture disk I/O information.

Extending STATSPACK for Disk I/O Data

Our data collection approach relies on I/O information from Oracle and from the physical disks. We will start by using existing STATSPACK tables, but we will also extend STATSPACK to add the disk I/O information. We will use the UNIX iostat utility to capture detailed disk I/O because almost every dialect of UNIX has the iostat utility. However, there is a dialect issue. Just as vmstat has different dialects, iostat is slightly different in each version of UNIX, and the Oracle DBA will need to customize a data collection mechanism according to his or her requirements. Even within the same dialect, there are arguments that can be passed to the iostat utility that change the output display.

The basic iostat utility

The UNIX iostat command syntax looks like this:

L 8-8

For example, to request five samples, spaced at 10 seconds apart, we would issue the command as follows:

L 8-9

Unlike the vmstat utility when all of the data is displayed on one line, the iostat output will have many lines per snapshot, one for each physical disk. Let's begin by taking a short tour of the different dialects of the iostat command. We will begin by showing differences between iostat for Solaris and HP/UX, and then show a method for extending STATSPACK to capture STATSPACK data for AIX servers.

iostat on AIX

L 8-10

Here we see each of the disks displayed on one line. For each disk we see:

• The percentage tm_act

• The Kbytes per second of data transfer

• The number of disk transactions per second

• The number of Kbytes read and written during the snapshot period

iostat on HP/UX

L 8-11

In the HP/UX output we see the following columns:

• Device name

• Kilobytes transferred per second

• Number of seeks per second

• Milliseconds per average seek

iostat on Solaris

L 8-12

Unlike the iostat output for HP/UX, here we see each disk presented horizontally across the output. We see disks sd0, sd1, sd6, and sd35.

The -x option of the HP/UX iostat utility changes the output from vertical to horizontal. For each disk, we report the reads per second, writes per second, and percentage disk utilization.

L 8-13

Now that we see the differences between the dialects of iostat, let's see how this information can be captured into STATSPACK extension tables.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.