Oracle Disk Access Speed, Some Concerns!!

 Author: Don Burleson

Page: 1 2

We must carefully distinguish between Oracle's perception of disk latency and the reality in order to properly tune the Oracle I/O subsystem. The back-end disk devices are black boxes and the only real information we have about disk latency are the salient metrics collected by Oracle.  The Oracle metrics can easily be skewed by the back-end devices but Oracle only knows the end-to-end latencies like physical write waits, physical read waits, db file scattered reads waits and db file sequential reads waits.

Multiblock disk I/O:

Multiblock disk I/O operations like table access full, index fast full scan, index range scans were considered faster because the access to physically contiguous blocks required only a single seek delay, the largest component of platter latency. 

Oracle Optimizer:

Oracle optimizer is very important as it decides between index access vs. full-scan access. There are several important optimizer settings that influence this behavior. 

CBO Decisions:

The relative cost of sequential vs. scattered read waits, and analyzing system statistics provides the optimizer with real-world empirical timings to help the CBO make better decisions about the best access method.  The db_file_multiblock_read_count and the _optimizer_cost_model parmeters also play an important role in this regard. 

Read-ahead caching:

The read ahead caching started on IBM mainframes as sequential prefetch. The concept of read-ahead caching is simple. Over 90% of I/O latency is consumed in the read-write head movement, as the heads are placed under the target cylinder. Once in-place, the disk continues to spin and the read-write head can simultaneously transmit back the original block request at the same time as the next sequential block passes below the read-write heads.

For scan operations like index range scans, index fast full scans, and full-table scans, a read-ahead cache can be very useful for speeding up the scattered read operations. If your database is requesting adjacent data blocks, the read-ahead cache may improve I/O throughput. However, using RAID10 (SAME, stripe and mirror everywhere), like with ASM, the blocks are not adjacent, and a read-ahead cache may not be as useful as a database where the data blocks are laid-out sequentially.

Not all disk configurations are created equal.  There are some Oracle conditions that may adverse effect disk access speed for full-scan operations. These include observations of disk speed and the optimizer, factors that effect multi-block disk access speeds, Samples from the real world and the Oracle STATSPACK & AWR reports showing scattered read timings

Scattered read waits:

 The scattered read waits (multiblock reads from full-scans) report as higher latency than sequential read waits (single block requests) because the multi-block I/O pulls-in many data blocks as opposed to a single block.  However, the first I/O that locates the read-write heads under the proper cylinder can be 70%-80% of total access time, and many databases can do multiblock I/O faster than sequential I/O. 

Full-scan access speed: 

Full-scan access speed is aggravated by Oracle willy-nilly block placement in Automated Storage Management (ASM) and using bitmap freelists (Automated Segment Storage Management).

Increased workload:

As system workload increases, it is possible to detect disk enqueues, especially where the data blocks are not "randomized".

Automatic Storage Management:

ASM place data blocks in a non-sequential fashion within the logical tablespace.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for Statistics gathering on Oracle Data Dictionary Objects!!
   Tricky Oracle Recovery for missing Archive Log!!
   Oracle Date Formats
   Great Tips on Tuning Database Materialized Views