Performance Terms

FTS or Full Table Scan

Whole table is read upto high water mark

Uses multiblock input/output

Buffer from FTS operation is stored in LRU end of buffer cache 

Index Unique Scan

Single block input/output 

Index Fast Full Scan

Multi block i/o possible

Returned rows may not be in sorted order 

Index Full Scan

Single block i/o

Returned rows generally will be in sorted order

SQL with High Buffer Gets

****************************

Concept

Buffer get: 

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'. 

Disk read:

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.