Lokeshaggarwal's DBA

Live the life you love. Love the life you live

Oracle Latch Contention


 

 What Are Latches?

 Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the SGA). In simple terms, latches prevent two processes from simultaneously updating — and possibly corrupting — the same area of the SGA.When a session reads a block from disk, it must modify a free block in the buffer cache and adjust the buffer cache LRU chain

Oracle sessions need to update or read from the SGA for almost all database operations. For instance:

  •   When a session reads a block from the SGA, it will modify the LRU chain.
  •  When a new SQL statement is parsed, it will be added to the library cache within the SGA. As modifications are made to blocks, entries are placed in the redo buffer.
  •   The database writer periodically writes buffers from the cache to disk (and must update their status from “dirty” to “clean”).
  •   The redo log writer writes entries from the redo buffer to the redo logs. 

 Latches prevent any of these operations from colliding and possibly corrupting the SGA.

Causes of contention for specific latches

The latches that most frequently affect performance are those protecting the buffer cache,areas of the shared pool and the redo buffer.

Library cache latches: These latches protect the library cache in which sharable SQL is stored. In a well defined application there should be little or no contention for these latches, but in an application that uses literals instead of bind variables (for instance “WHERE lastname=’Aggarwal’” rather that “WHERE lastname=:lastname,” library cache contention is common.

Redo copy/redo allocation latches: These latches protect the redo log buffer, which buffers entries made to the redo log.

Shared pool latches: These latches are held when allocations or de-allocations of memory occur in the shared pool. Prior to Oracle 8.1.7, the most common cause of shared pool latch contention was an overly large shared pool and/or failure to make use of the reserved area of the shared pool.

Cache buffers chain latches: These latches are held when sessions read or write to buffers in the buffer cache. In Oracle8i, there are typically a very large number of these latches each of which protects only a handful of blocks. Contention on these latches is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).

Tuning the Application to Avoid Latch Contention

 

 

There are some things we can do within our application design that can reduce contention for latches.

 Using Bind Variables

We should always use bind variables in our application.We can also use CURSOR_SHARING parameter to cause Oracle to modify SQL to use bind variable.A setting of FORCE causes all literals to be converted to bind variable.

 

Avoiding Hot Blocks

 

Firstly, identify the blocks that are “hot.” Metalink note 163424.1, “How to Identify a Hot Block Within The Database”

 

To find out the latch ID

 

SQL> select CHILD# “cCHILD”
, ADDR “sADDR”
, GETS “sGETS”
, MISSES “sMISSES”
, SLEEPS “sSLEEPS”
from v$latch_children
where name = ‘cache buffers chains’
order by 5, 1, 2, 3;

 

SQL> column segment_name format a35
     select /*+ RULE */
       e.owner ||’.’|| e.segment_name  segment_name,
       e.extent_id  extent#,
       x.dbablk – e.block_id + 1  block#,
       x.tch,
       l.child#
     from
       sys.v$latch_children  l,
       sys.x$bh  x,
       sys.dba_extents  e
     where
       x.hladdr  = ‘ADDR’ and
       e.file_id = x.file# and
       x.hladdr = l.addr and
       x.dbablk between e.block_id and e.block_id + e.blocks -1
     order by x.tch desc ;

 

 

To find out the hot blocks

SELECT c.addr, sleeps, file#, dbablk, class, state, TCH
FROM sys.v$latch_children c, sys.v$latchname n , sys.x$bh
WHERE n.name=’cache buffers chains’
and c.latch#=n.latch#
and sleeps >10000
and x$bh.hladdr = c.addr
ORDER BY sleeps;

 

 

 

 

 

 

Advertisements

June 17, 2009 - Posted by | Latches

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: