SQL statement with High Disk reads

SQL statement with High Disk reads

As per oracle, ‘Disk reads’ are physical I/O which bring data blocks from disk into oracle memory structure. Since physical reads need to access the physical storage tier with additional power, it will have more impact on the performance than buffer gets (logical i/o).

• Always keep relationship between disk reads and buffer gets because in either way, one will lead to the other and vice versa.
• Keep in mind that buffer gets may not require a disk read, and high buffer gets can cause high disk reads.When oracle uses direct path read operation, disk reads incurred may or may not count for buffer gets. 

o   Direct path read occurs when oracle needs data blocks from disk directly into process private memory (PGA), bypassing database buffer cache.

o   Oracle uses direct path read either against datafile or tempfile.  Oracle uses direct path read against datafile under situations such as reading LOB segments, scanning a table or index using parallel buffer gets and it performs Full table scan for large tables. High disk reads will have high buffer gets and hence the SQL statements with high disk reads occurred will still be counted for buffer gets.

o   Oracle also uses direct path read when it is reading sort segments on tempfile.  The disk reads thereby not counted for buffer gets , which leads to ‘high disk reads than buffer gets’ symptom for SQL statement performing a large amount of data sorting.

Tuning strategies are as follows

Ø  Adjust paramenters ‘ filesystemio_options ‘and ‘ db_writer_processes’  to facilitate good i/o processing.

Ø  If we see buffer gets which is causing high disk reads, then follow the steps in identifying the query with high buffer gets and see why it is doing high 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.

Scenarios of when SQL statements with high disk reads occurs

a.       Due to SQL with high buffer gets.

b.      Due to SQL with direct path read for Full table scan.

c.       Due to SQL using direct path read for LOB

d.      Due to SQL using direct path read for Parallel query

e.      Due to SQL using direct path read temp for sorting.