11     cache buffers chains
    12     cache buffer handles
    13     multiblock read objects
    14     cache protection latch
    15     cache buffers lru chain
    16     system commit number
    17     archive control
    18     redo allocation
    19     redo copy
    20     KCL freelist latch
    21     KCL name table latch
    22     instance latch
    23     lock element parent latch
    24     loader state object freelist
    25     dml lock allocation
    26     list of block allocation
    27     transaction allocation
    28     sort extent pool
    29     undo global data
    30     ktm global data
    31     sequence cache
    32     row cache objects
    33     cost function
    34     user lock
    35     global tx free list
    36     global transaction
    37     global tx hash mapping
    38     shared pool
    39     library cache
    40     library cache load lock
    41     virtual circuit buffers
    42     virtual circuit queues
    43     virtual circuits
    44     NLS data objects
    45     query server process
    46     query server freelists
    47     error message lists
    48     process queue
    49     process queue reference
    50     parallel query stats
    51     parallel query alloc buffer
    52     device information

cache buffers lru (Least Recently Used) chainThe cache buffers lru chain latch is responsible for protecting the access paths to db block buffers in the buffer cache. The buffer cache size defined by the init.ora parameter db_block_buffers resides in the SGA and contains the cached copy of data read from data files. When processes need to read data, the presence of the data in this buffer as a result of the data being read in by some process makes the read very I/O-efficient.

The buffer cache is organized in two lists: the dirty list and the LRU list. The dirty list contains the buffers, that have been modified but not written to the disk yet. The LRU list is comprised of the pinned buffers, the dirty buffers that have not yet been moved to the dirty list, and the free buffers. The pinned buffers are buffers that are currently accessed by other processes.

The dirty buffers contain buffers that are to be written to the disk, and they then subsequently get moved to the dirty list. The free buffers are the buffers that are available for use.

When a process needs to read data from the disk that is not already present in the cache, it needs a free buffer to read in the new data. It scans the LRU list to check for free buffers. If there are excessive requests for free buffers in the buffer cache there will be a high access to the LRU list causing contention for the cache buffer LRU chain.

The contention for this latch can be minimized by the init.ora parameter db_block_lru_latches, which is available in Oracle 7.3. By increasing the db_block_lru_latches value, the contention can be minimized for this latch. The maximum value for this parameter is double the number of CPUs.

The basic reason for contention for this latch is a high request for free buffers. You can optimize the SQL statements to minimize the high demand for free buffers or increase the db_block_buffer parameter to increase the number of free buffers available on the system.

redo allocation and redo copyThe redo allocation and redo copy latches control the write access to the redo log buffer. When a process requires writing to the redo log buffer, one of these latches is to be acquired by the process. If the size of the redo log information written to the redo log buffer is less than log_small_entry_max_size parameter, the process will use the redo allocation latch. If the size is greater than this value, the process is copied using the redo copy latch.

A quick way to check whether there is any contention on the redo log buffer is to check whether there are any waits associated with writing to the redo log buffer. This can be done by using the system view v$sysstat:

Select name, value
From v$sysstat
Where name = `redo log space requests';

Output Table:

Name                                        Value

----------------------------------------     ----

redo log space requests                        12

The size of the redo log buffer will have to be increased if the number of waits is too high.

Contention for redo allocation LatchThe contention for redo allocation can be reduced on a multi-CPU system by forcing the process to use the redo copy latch instead. Because there can be multiple redo copy latches, the copy will be done more efficiently. The number of redo copy latches is defined by the init.ora parameter log_simultaneous_copies. The maximum number available latches on the system is double the number of CPUs. For a

single CPU system, this value is 0, and the redo allocation latch will be used. So, if there is a contention for the redo allocation latch, the value of log_small_entry_max_size can be decreased from its current value so that the redo copy latch is used.

Contention for the redo copy LatchIf the system is facing contention for the redo copy latch, it can be decreased by either increasing the value of log_small_entry_max_size (so that the redo allocation latch is used) or increasing the value of log_simultaneous_copies (so that it increases the number of redo copy latches available).

The init.ora parameter log_entry_prebuild_threshold can be increased so that the data that is written to the redo log buffer is grouped and written out. By increasing the parameter,
a number of write operations can be grouped so that they can be written out in one operation, thereby reducing requests for these latches and thus contention.

library cacheThis latch is primarily concerned with control of access to the library cache. The library cache includes shared SQL area, private sql areas, PL/SQL procedures packages, and other control structures. Shared SQL area contains SQLs that are shared among multiple sessions. By increasing the sharing of these SQLs, contention to this latch can be avoided.

Contention for this latch occurs when there is a lot of demand for space in the library cache. Very high parsing on the system and heavy demand to open a new cursor because of low sharing among processes are some of the common causes for contention for this latch.

Contention for this latch can be avoiding by using code that can be shared by multiple sessions. For example, the RDBMS treats the following two SQLs differently:

select name from employee where emp_id = 100;

and

Select name from employee where emp_id = 100;

Though both the SQL statements look the same, the hash values of these statements are different. One of the statements has select in upper case and the other one has select in lower case. Therefore, when this statement is issued, the RDBMS will parse both the statements individually as different statements, thus causing load on the latches. This situation can be avoided by developing coding standards wherein indentation and case standards are implemented so that every SQL statement written would have the same hash value as far as possible. Note that even putting more empty spaces causes one select to be different from another, causing increased parsing.

Using bind variables in SQL statements can increase the sharing of the same clauses. For example, consider the two cases, not using bind variables:

Select sal from employee where emp_id = 100;

Select sal from employee where emp_id = 200;

or, using bind variables:

Select sal from employee where emp_id  := emp_id;