Oracle Guide for Disk I/O tuning!!

 Author: Burleson

Page: 1 2

The relative cost of physical disk access is an important issue as all Oracle databases retrieve and store data. In this article I will discuss sequential read disk I/O tuning in Oracle.

Oracle Data block access:

There are two types of data block access in Oracle

Physical disk speed is an important factor in weighing these costs. Faster disk access speeds can reduce the costs of a full-table scan vs. single block reads to a negligible level.

The new solid state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices. In a solid-state disk environment, disk I/O is much faster and multiblock reads become far cheaper than with traditional disks.

STATSPACK report:

The standard STATSPACK report can be generated when the database is processing a peak load, and you can get a super-detailed report of all elapsed-time metrics. This report is critical because it shows the database events that constitute the bottleneck for the system. The STATSPACK top-five timed events are most important. Below STATSPACK report shows that the system is clearly constrained by disk I/O.

Top 5 Timed Events

                                                           % Total

Event                        Waits         Time (s)       Ela Time

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

db file sequential read       2,598          7,146          48.54

db file scattered read       25,519          3,246          22.04

library cache load lock         673          1,363           9.26

CPU time                                     1,154           7.83

log file parallel write      19,157            837           5.68

Here we see that reads and a write constitute the majority of the total database time. In this case, we would want to increase the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/O, or invest in a faster disk I/O subsystem.

Optimizer settings:

The ideal optimizer settings depend on your environment and are heavily influenced by your system's costs for scattered disk reads vs. sequential disk reads. 

Measuring I/O costs:

Below script can measure these I/O costs on your database.

col c1 heading 'Average Waits|forFull| Scan Read I/O'        format 9999.999

col c2 heading 'Average Waits|for Index|Read I/O'            format 9999.999

col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99

col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99

col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999

select

   a.average_wait                                  c1,

   b.average_wait                                  c2,

   a.total_waits /(a.total_waits + b.total_waits)  c3,

   b.total_waits /(a.total_waits + b.total_waits)  c4,

   (b.average_wait / a.average_wait)*100           c5

from

  v$system_event  a,

  v$system_event  b

where

   a.event = 'db file scattered read'

and

   b.event = 'db file sequential read'; Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Important Oracle Issues and Solutions: A Must Read!!
   The Power of Oracle External Tables
   Handling Tricky Oracle Job Scheduling Problem!!
   Great Tips on Tuning Database Materialized Views