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.