SQL with High Buffer Gets

When Oracle requires a data block, it first checks to see if this data block is already in memory (database buffer cache). If the data block is found there, Oracle will read it. This process is called as 'buffer get'. If the data block is not found, Oracle will need to bring the data block from disk into buffer cache first. This process is called as 'disk read'. From Oracle database's standpoint, the 'buffer get' is 'logical I/O', while 'disk read' is 'physical I/O'. A buffer get may or may not require a disk read.

The SQL statements with high buffer gets are always the concern of database performance tuner:

  •   High buffer gets can cause high disk reads, which causes I/O workload increase and I/O path bottleneck. When accessing tables with a large amount of data, or database buffer cache with insufficient size, or combination of both, the SQL statements with high buffer gets will also have high disk reads because not all data blocks will be able to fit in database buffer cache and need to swap out to disk.

  •   Even under situations where the majority of data blocks already exist in database buffer cache and disk reads are low, high buffer gets put pressure on the Oracle memory structure, causing contention as well as burning CPU cycle. It is not rare to find out that the SQL statements with high buffer gets also have high CPU time, or get stuck with wait events such as 'free buffer waits', 'buffer busy wait', etc.

The key tuning strategies include:

  •   Review application logic to adjust I/O demands.
  •   Increase database parameters such as database cache size to accommodate high I/O workload.
  •   Ensure optimal SQL plans for SQL statements and try to avoid those high I/O cost algorithms such as 'TABLE ACCESS FULL', 'MAT_VIEW ACCESS FULL', etc.
  •   Address concurrency issue and reduce contention.

 Case Study Summary

The following example describes a case scenario of performance tuning a SQL statement with high buffer gets. The root cause of high buffer gests turned out to be the full table scan involved on a big table. A new index was offered as the solution.

The list of steps include:

 Step 1 : Identify the SQL with high buffer gets

 Step 2 : Check SQL stats to identify the pattern

 Step 3 : Check SQL text and SQL plan to identify the full table scan as the issue

 Step 4 : Create a index to confirm the new SQL plan

 Step 5 : Check SQL stats to confirm the improvement

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