SQL ordered by Gets

Class of the wait events

1.       Application – locks caused by row level locking or explicit locking commands

2.       Commits – waits for redo log write confirmation after a commit

3.       Idle – wait events that signify the session is inactive eg: “SQL* Net message from client”

4.       Network – waits for data to be over the network

5.       User I/O – waits for blocks to read from the disk

DB Time

Ø  Statistics represents the total time spent on the db calls and an indicator of the total instance workload.

Ø  Calculated by aggregating the CPU and wait times on all sessions not waiting on idle wait events (non-idle user sessions)

Ø  Cumulative from the time the instance started.

Ø  Because DB time is calculated by combining the times from all non-idle user sessions, it is possible that the DB time can exceed the actual time elapsed since the instance started up. For example, a instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is approximately 120 minutes.

SQL ordered by elapsed time

This shows the sql statements that took significant execution time during processing

SQL ordered by reads

It is the number of physical reads from the disks before we load the buffer cache and present the data to user access.

SQL ordered by Gets/Buffer Gets

It is the number of logical reads from the buffer cache to the user process.

When Oracle requires a data block, it first checks to see if this data block is available in the memory (database buffer cache). If the data block is found there, oracle will read it. This process is call ‘buffer get’.

If the data block is not found, oracle will bring the data from disk into buffer cache first.  This process is called ‘disk read’. 

From Oracle’s point of view, buffer get is logical I/O  and disk read is physical I/O.  

A buffer get may or may not require a disk read.

 SQL statements with high buffer gets are always the concern for database performance.

a.       High buffer gets can cause high disk reads, which causes I/O workload increase and I/O path bottleneck.  When we access tables with 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 a high disk reads because not all data blocks will be able accommodate in the buffer cache and it will swap out to disk.

b.      Even, when we have majority of data blocks already existing in the database buffer cache and disk reads are low, high buffer gets put pressure on the oracle’s memory structures, causing contention as well as burning CPU cycle. 

It is not too tough/rare to find out the SQL statements with high buffer gets also having high CPU time, or get stuck with wait events such as ‘free buffer waits’ ,’buffer busy wait’,etc.

Key for tuning the queries with high buffer gets are as follows

1.       Review application logic to adjust the demands for i/o

2.       Increase the parameters like database cache size or SGA _TARGET  to have high I/O workload

3.       Concurrency issues to be addressed and reduce contention

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

References : http://www.dbspeed.com/case_study/sql_with_high_disk_reads.html