SQL with High Disk Reads

From Oracle database's standpoint, 'disk reads' are physical I/O which bring data blocks from disk into Oracle memory structure. Disk reads require the processing power of I/O subsystem and also may need access to physical storage tier. Therefore, disk reads can have more compound impact on database performance than buffer gets (the logical I/O).

We need to keep an open mind about the relationship between disk reads and buffer gets: one may or may not lead to the other, and vice versa.

  •   As we said before, a buffer get may not require a disk read, and high buffer gets can cause high disk reads.

  •   When Oracle uses direct path read operation, the disk reads incurred may or may not count for buffer gets. Direct path reads occur when Oracle reads data blocks from disk directly into process’s private memory (PGA), bypassing database buffer cache. Oracle can use direct path read against either datafile or tempfile. When Oracle perform direct path read against datafile under situations such as reading LOB segments, scanning a table or index using parallel queries, performing full table scan on a large table (11g new), etc., the disk reads incurred are still counted for buffer gets and high disk reads will have high buffer gets. Another common situation where Oracle use direct path read is reading sort segments on tempfile. The disk reads thereby are not counted for buffer gets, which leads to the ‘higher disk reads than buffer gets’ symptom for SQL statement performing a large amount of data sorting.

The SQL statements with high disk reads are usually I/O bound. We consider the following tuning strategies:

  •   Adjust database parameters such as filesystemio_options and db_writer_processes to facilitate the most efficient I/O processing.
  •   If it is the buffer gets which cause high disk reads, we use the same tuning strategies as what we do for high buffer gets.
  •   Pay special attention to disk path read operation if it drives up disk reads. Oracle 11g has changes in the heuristics to choose between direct path reads or reads through database buffer cache for serial table scans (metalink note 793845.1), which could be more likely to have SQL with high disk reads.

 Case Study Summary

The case scenarios include:

 Case 1 : SQL with high disk reads is driven from high buffer gets

A SQL statement was identified with high disk reads. It is a 'merge' statement which keeps doing full table scan through buffer gets, which also driving up disk reads due to the fact that dirty data blocks will be flushed to disk, and then read back for new updates again.

 Case 2 : SQL with high disk reads is driven from direct path read for table scan

A SQL statement was identified with high disk reads because Oracle uses direct path read for full table scan.

 Case 3 : SQL with high disk reads is driven from direct path read for LOB

A SQL statement was identified with a considerable amount of disk reads, but no table scan was involved. By drilling down to the table structure, it turns out that one of the table involved has CLOB data type. Oracle performs direct path reads against LOB, which cause high disk reads.

 Case 4 : SQL with high disk reads is driven from direct path read for parallel query

Oracle uses direct path read for parallel queries during scanning tables, table partitions, indexes, index partitions, etc. The following SQL statement with high disk reads was identified as a parallel query.

 Case 5 : SQL with high disk reads is driven from direct path read temp for sorting

A SQL statement was identified with more disk reads than buffer gets, which is a common situation when Oracle performs sorting - direct path read/write against TEMP tablespace. This type of disk reads won’t counted for buffer gets.

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us