Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 575

In the preceding output, the columns

•

Gets indicates the number of times the latch was requested in willing-to-wait mode;

•

Misses indicate the number of times the latch was requested in willing-to-wait mode and the
requester had to wait;

•

Sleeps indicate the number of times a willing-to-wait latch request resulted in a session
sleeping while waiting for the latch;

•

Immediate Gets indicate the number of times a latch was requested in no-wait mode;

•

Immediate Misses indicate number of times a no-wait latch request did not succeed;

Wait Time is the elapsed time displayed in seconds spent waiting for the latch.
In the preceding output, few of the latches show high wait time. We look at drilling down into the reasons for high
waits in these latches.

•

Cache Buffers Chains
This latch is acquired whenever a block in the buffer cache is accessed, normally due to high logical I/O (LIO). SQL
statements reading more buffers than required; and when multiple sessions are trying to access the same block, this
causes sessions to wait on this latch.
Reducing contention for the cache buffer chains latch will usually require reducing logical I/O rates by tuning
and minimizing the I/O requirements of the SQL involved. Optimizing the access paths, such as adding additional
indexes, could reduce I/O rates. High I/O rates could also be a sign of a hot block, which would mean looking at
storage distribution; and tuning access paths would benefit in reducing these latch times.
Typical solutions include the following:

• Look for SQL statements that access the blocks in question and determine if the repeated

reads are necessary. This may be within a single session or across multiple sessions.

• Check for suboptimal SQL. Look at the execution plan for the SQL being run and try to reduce

the gets per executions, which will minimize LIO, the number of blocks being accessed, and
therefore reduce the chances of multiple sessions contending for the same block.

How do we find what blocks or objects are hot and are the cause for this high latch contention? We try to analyze
through the following workshop how to troubleshoot and identify the hot blocks.

Workshop
For the output discussed previously, lets try to understand the details a little.

Step 1
To find the various latches currently having high wait times, execute the following query to get the latch details:

Script: MVRACPDnTap_dlmlatch.sql

SELECT inst_id INT,
latch#,
level#,
name,
gets,
misses,