Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Tips

Measuring Disk I/O Speed

The relative cost of physical disk access is an important topic since all Oracle databases retrieve and store data.

 

A significant factor in weighing these costs is physical disk speed.  Quicker disk access speeds can diminish the costs of a full table scan versus single block reads to a negligible level.

 

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

 

The standard STATSPACK report can be generated when the database is processing a peak load, and it is also possible to get a detailed report of all elapsed time metrics.  The STATSPACK top-five timed event report is the most important of these metrics.  The report is critical for it displays the database events that constitute the bottleneck for the system.  The listing below from a 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

 

Reads and writes constitute the majority of the total database time as shown above.  In such a case, the RAM size of the db_cache_size should be increased in order to reduce disk I/O, the SQL tuned to reduce disk I/O, or a faster disk I/O subsystem should be invested in.

 

Not only do the ideal optimizer settings rely on the environment, they are heavily swayed by the system's costs for scattered disk reads versus sequential disk reads.  A great script to measure these I/O costs on the database is shown below.

 

<      sum_read_waits.sql

 

 

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'

;

 

While there are varied opinions regarding full-table scans, they are not necessarily a detriment to performance.  Indeed they are often the quickest way to access the table rows.  The CBO option of performing a full table scan depends on many factors, some being the settings for Oracle Parallel Query, the db_block_size, the clustering_factor; and the estimated percentage of rows returned by the query according to the CBO statistics.

 

Once Oracle selects a full-table scan, the speed of performing a full-table scan (SOFTS) rests with internal and external factors:

§       Table partitioning

§       The number of CPUs on the system

§       The setting for Oracle Parallel Query (parallel hints, alter table)

§       The speed of the disk I/O subsystem (e.g., hardware-cached I/O, solid-state disk RAM 3)

When factoring in all these elements, it may be impossible to decide the exact best setting for the weight in optimizer_index_cost_adj.  In reality, the decision to petition a full-table scan is heavily influenced by run-time factors such as:

§       The present demands on the CPUs

§       The attainability of free blocks in the data buffers

§       The amount of TEMP tablespace, if the FTS has an order by clause

No two database systems are the same and good DBAs must adjust optimizer_index_cost_adj according to database configuration and data access patterns.

 

The encompassing amount of time 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.

 

The following section will introduce how to measure system I/O wait events in real-time.

 

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.