Oracle sequential read disk I/O tuning

http://www.dba-oracle.com/art_builder_io_speed.htm

 

10gr2 Note:  Starting in Oracle 10g release2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.  For more details, see my notes on 10gR2 automatically tuned multi-block reads . Also, see my related important notes on tuning full-scan I/O .

---

Because all Oracle databases retrieve and store data, the relative cost of physical disk access is an important topic. In Oracle, we see two types of data block access:

• • db file sequential read 桝 single-block read (i.e., index fetch by ROWID)
     
  • db file scattered read 桝 multiblock read (a full-table scan, OPQ, sorting)

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.

For example, 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.

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. The most important of these metrics is the STATSPACK top-five timed events. This report is critical because it shows the database events that constitute the bottleneck for the system. We can also see the same phenomenon where a system is disk I/O bound. In the STATSPACK report in Listing A, we see 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.

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.  Below is a great script you can use to 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';

Scattered reads and full-table scans

Contrary to some opinions, full-table scans are not necessarily a detriment to performance, and they are often the fastest way to access the table rows. The CBO (cost-based optimizer) choice of performing a full-table scan depends on the settings for Oracle Parallel Query, the db_block_size , the clustering_factor , the estimated percentage of rows returned by the query (according to the CBO statistics), and many other factors.

Once Oracle has chosen a full-table scan, the speed of performing a full-table scan (SOFTS) depends on internal and external factors:

• • The number of CPUs on the system
  • The setting for Oracle Parallel Query (parallel hints, alter table)
  • Table partitioning
  • The speed of the disk I/O subsystem (e.g., hardware-cached I/O, solid-state disk RAM 3)

With all of these factors, it may be impossible to determine the exact best setting for the weight in optimizer_index_cost_adj. In the real world, the decision to invoke a full-table scan is heavily influenced by run-time factors such as:

• • The availability of free blocks in the data buffers
  • The amount of TEMP tablespace (if the FTS has an order by clause)
  • The current demands on the CPUs

Hence, it follows that the optimizer_index_cost_adj should change frequently, as the load changes on the server.

However, is it safe to assume that all of the SOFTS factors are reflected in the relative I/O speed of FTS vs. index access? If we make this assumption, we've measured the relative speed in v$system_event and have a foundation for creating a self-tuning parameter. To do this, we must accept the following assumptions:

• • No systems are alike, and good DBAs must adjust optimizer_index_cost_adj according to their configuration and data access patterns.
     
  • The SOFTS is measurable and is reflected in the wait times in v$system_event.
     
  • The overall amount of time spent performing full-table scans is equal to the percentage of db file sequential read waits as a percentage of total I/O waits from v$system_event :

  (avg-wait-for-db-file-sequential-read /

    avg-wait-for-db-file-scattered-read)       * 100

Control disk I/O

Disk I/O-intensive systems are common to Oracle databases, and the time spent performing disk I/O often consumes the majority of the wait time. The job of the Oracle professional is to examine all SQL statements to ensure that they're performing the minimum amount of disk I/O and to know their relative costs of index access vs. full-table scan access.

Here is sample output from a real system showing an empirical showing the numbers.  We always expect scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential prefetch (db_file_multiblock_read_count ):

 

- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)

- starting setting for optimizer_index_cost_adj at 36:


C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
13,824     5,072      13         86         36
 

Here is another variant, showing changes to optimizer_index_cost_adj (see important note below) wait components over time:

set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O' format 999999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format
999.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format
999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999

select a.snap_id "Snap",
       sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,
       sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
       (sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
       (sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
       (sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read'
group by a.snap_id
order by 1
/

 
  Snap       Full Scan Read I/O    Index Read I/O    Full Scans    Index Scans
 ---------- ------------------ ----------------- ------------- --------------
       5079               .936              .074         10.14          89.86  
       5080               .936              .074         10.14          89.86  
       5081               .936              .074         10.14          89.86  
       5082               .936              .074         10.14          89.86
       5083               .936              .074         10.13          89.87  
       5084               .936              .074         10.13          89.87  
       5085               .936              .074         10.13          89.87

 

Important Note:   Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats .  As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book " Oracle Tuning: The Definitive Reference ".