Page 643
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.
Page 644
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.
NOTE |
Note that the SGA must fit into a contiguous chunk of real memory, so if the buffer cache is enlarged, you must ensure that there is enough contiguous memory available on the system to service the increase.n |
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
Page 645
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;