Internally, Oracle uses various types of structures, the access of which is controlled using a variety of mechanisms, including latches. In this article, Oracle expert Megh Thakkar discusses how contention for Oracle latches can be minimized.

Internally, Oracle uses various types of structures. Access to these structures is controlled using a variety of mechanisms:

· Latches

· Enqueues

· Distributed locks

· Global locks (used in parallel instance implementations)

This article discusses how contention for Oracle latches can be minimized.

Latches control the access to internal data structures and thereby provide a way to protect them. If a process can't obtain a latch immediately, it spins while waiting for the latch. Spinning processes should be minimized because they can lead to additional CPU use and a slowing of the system.

There are various types of latches, which are commonly referenced by the data structure to which they control access. Table 1 lists the most important latches that you should be concerned with.

Several data dictionary views can be helpful in identifying latch contention:

· v$latch

· v$latchholder

· v$latchname

The following queries can provide useful information about latches:

• This query provides the name of the latch by using the latch address:

svrmgr> SELECT name

2> FROM v$latchname ln, v$latch l

3> WHERE l.addr = '&addr'

4> AND l.latch# = ln.latch# ;

• This query provides system-wide latch statistics:

svrmgr> SELECT ln.name, l.addr, l.gets, l.misses, l.sleeps,

2> l.immediate_gets, l.immediate_misses, lh.pid

3> FROM v$latch l , v$latchholder lh , v$latchname ln

4> WHERE l.addr = lh.laddr (+)

5> AND l.latch# = ln.latch#

ORDER BY l.latch# ;

·                 This query provides statistics for any latch 'Z':

svrmgr> SELECT ln.name, l.addr, l.gets, l.misses, l.sleeps,

2> l.immediate_gets, l.immediate_misses, lh.pid

3> FROM v$latch l , v$latchholder lh , v$latchname ln

4> WHERE l.addr = lh.laddr (+)

5> AND l.latch# = ln.latch#

6> AND ln.name like '%Z%'

7> ORDER BY l.latch# ;

If either of the following is true for a latch, it indicates contention:

· The ratio of MISSES to GETS exceeds 1 percent.

· The ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_MISSES and IMMEDIATE_GETS exceeds 1 percent.

Table 1: Important Latches That Can Cause Contention

The following recommendations can help reduce latch contention:

· The cache buffers chains latch is needed when the SGA is scanned for database cache buffers. Contention for this latch can be reduced by increasing the DB_BLOCK_BUFFERS init.ora parameter.

· The cache buffers LRU chain latch is needed when the LRU chain containing all the dirty blocks in the buffer cache is scanned. Contention for this latch can be reduced by increasing the DB_BLOCK_BUFFERS and DB_BLOCK_WRITE_BATCH init.ora parameters.

· The row cache objects latch is needed when the cached data dictionary values are being accessed. Contention for this latch can be reduced by increasing the SHARED_POOL_SIZE init.ora parameter.

· Minimize contention for library cache latches by using the following guidelines:

o Minimize the fragmentation of the shared pool.

o Increase the use of shared SQL statements, and thereby decrease the reloads. Identify the SQL statements that are receiving many parse calls with the following query:

svrmgr> SELECT sql_text, parse_calls, executions
2> FROM v$sqlarea
3> WHERE parse_calls > 100
4> AND executions < 2*parse_calls;

o Then, try to use sharable SQL wherever possible.

o Set the CURSOR_SPACE_FOR_TIME init.ora parameter to TRUE to keep shared SQL areas pinned in the shared pool. This prevents them from aging out of the pool as long as an open cursor references them, which results in faster execution. However, be sure that your shared pool is large enough to hold all the cursors.

o Use fully qualified table names.

o The redo allocation latch controls the allocation of space for redo entries in the redo log buffer. Before an Oracle process can allocate space in the redo log buffer, it must obtain this latch. Contention for the redo allocation latch can be minimized by decreasing the value of the LOG_SMALL_ENTRY_MAX_SIZE init.ora parameter because this parameter determines the number and size of redo entries copied on the redo allocation latch.

· The redo allocation latch is released as soon as space is allocated. However, the copy is then performed under the redo copy latch. On multiple CPUs, the LOG_SIMULTANEOUS_COPIES parameter determines the number of redo copy latches. Contention for redo copy latches can be reduced by increasing the value of LOG_SIMULTANEOUS_COPIES because having multiple redo copy latches enables multiple processes to concurrently write the redo entries. The init.ora parameter LOG_ENTRY_PREBUILD_THRESHOLD (default value is 0) places a limit on the size of redo entries so that those redo entries that are smaller than this threshold are prebuilt before requesting the redo copy latch. Therefore, increasing LOG_ENTRY_PREBUILD_THRESHOLD can reduce the contention for the redo copy latch.

In general, you don't have control over which latch is used or when it is used. However, by setting certain init.ora parameters, you can optimize the use of these latches, resulting in improved system performance.

I. Tuning Overview

The DBA, the application developer, management, the system administrator, the network administrator.

Considered on the ROI (Return on Investment) outline here are the tunning recommendation in order:

A. Do a proper logical design: In practice this often means more tables with fewer rows per table. In turn, this means the capability for faster searching. The fewer rows that are stored and must be searched through, regardless of the search techinque, the quicker you'll be able to find what you're looking for.

B. Do a proper physical design : Separate the datafiles (tablespaces ) into different disks to avoid I/O contention , use striping.

C. Redesign if necessary Sometimes the best way to correct the database without extensive tuning efforts is a re-analysis and redesign. Consider redesign when the initial design was hurried, or when multiple databases need to be integrated.

D. Write Efficient Application Code: If some SQL code uses an inefficient seach or sort routine, despite the best efforts of the Oracle optimizer, the application will run slowly.

E. Rewrite code if necessary: If application code efficiency comes into question and resource and management permit , re-analyze and re-write the code.

F. Tune the database memory structures Oracle can offer substantial improvments through the tuning of its database buffer cache. Also the shared pool caches SQL code via the library cache component and caches the data dictonary component through ,obviously ,data dictonary cache. The redo log buffer is a separatly tunable area in the SGA.

G. Tune OS memory Structures if necessary : The SWAP area can become a bottleneck since it functions as an OS temporary storage, user temporary storage, and the OS virtual memory backing store. The SA and DBA musht work together so that the OS provides enough shared memory and semaphores to give the Oracle processes enough breathing room to operate efficiently.

H. Tune Database I/O Database I/O is affected by both the RDBMS and the OS of course, but tuning the I/O means relocating logical and physical structures to reduce contention. If this point in tunning is reached you will have already tuned the database buffer cache. Now the main focus will be to simply adjust the physical design. You physically do more redesiging , if necessary with I/O in mind exclusively.

I. Tune OS I/O if necessary The OS fullfils all write and reads requests by all processes, including the Oracle background procceses DBWR and LGRW. As OS typically buffers these requests, performs reads and writes, and then returns the acknowlegment and data back to the process upon completion.

File system are data structures that contain metadata about the files they manage, suche as the location of each files starting sector address, its sector length, its directory tree location, its attributes (permissions,size,timestamps,etc). In UNIX, file systems have their own logical block sizes, which correspond to something greater than or equal to a physical block size (512 bytes), usually 8KB by default. The oracle Block size should be at least 8KB or a multiple of it,such as 16KB.

Other importatn OS and Oracle I/O tuning issues include: read-ahead capabilities, asynchronous I/O, multiblock reads, RAID stripe sizes, disk geometry issues, controlles issues,and many more.

J. Tune the network if necessary A saturated network can cancel out imporvments made by database tuning.

K. Tune the clients if necessary

L. Consider more exotic solutions Oracle Multithreaded Server ("MTS") , transaction processing (TP) monitors, Oracle Parallel Query and other parallel capabilities, Oracle's clustering capabilities, Oracle's bitmapped indexing, MPP machines, solid state disks, memory-resident (RAM) disks, hardware accelerators and queuing systems.

There are different ways of determining the goals of a performance tuning effort, consider the application type, also sampling the database on various quantative measaurs is further defining the tuning goals:

a. Throughput : Work per unit time , as measured by transactions per second; higher is obviously better.

b. Response Time: The time it takes for an application to respond, measured in milliseconds or seconds, lower is better.

c. Wall Time : The elapsed time a program takes to run, lower is better.

In most systems throughput and response time run counter to one another as tuning goals. If response time is high (bad), throughput might be high (good). If throughput is low (bad) then response time might be low (good).

Typically OLTP systems want low response time or high throughput, in terms of transaction on the application needs. A DSS wants low response time , and a batch system normally wants lower wall times.

Always consider the two central tuning goals :

8. Maximize your return on invesetment Invest your time and effort wisely by working on the problems most likely to yield the most improvement.

9. Minimize contention : Bottlenecks are characterized by delays and waits;eliminate or reduce these whenever possible.

Also consider these general-purpose tuning goals :

10. Minimize the number of blocks that need to be accessed; review and rewrite code as necessary.

11. Use caching, buffering and queueing whenever possible to compensate for the electromechanical disadvantage ( memory is faster than disk);prefetch

12. Minimize data transfer rates ( the time it takes to read or write data); fast disks,RAID and parallel operations help do this.

13. Schedule Programs to run as noncompetetively as possible, they might run concurrently and yet still be noncompetetive for the most part.

The two main configurations for running a database are ARCHIVELOG and NOARCHIVELOG. Assuming a recovery operation is needed the following scenarios could arise:

If the database is in ARCHIVOLOG there are two possibilities:

0. Complete-Recovery : The database is restored and recovered through the applicaion of ALL redo information (this includes both the online and archived redo log files) since the last backup. This type of recovery is performed normally when one of more data files or control files are damaged, the damaged files are recovered using al the redo information generated since the last full backup.

1. Incomplete-Recovery: In this scenario the database is restored and recovered through the application of only SOME of the redo information generated since the last backup. This type of recovery is normally used when an on-line redo log file is lost due to hardware failure or a certain user requires to backup to a certain point in time,in simpler terms, an incomplete recovery only rollsback certain transactions without the need to involve ALL the redo-log files.

If the database is in NOARCHIVELOG:

You benefit from not having to save all the on-line redo log files, on heavily accesed systems this type of configuration alleviates disk-space usage,because in ARCHIVELOG every on-line redo log file is eventually backed-up; these archived redo-log files can easily fill up a WHOLE disk (10-12 GB) in a matter of hours. However the disadvantage of running this configuration is that in the event of a failure your only means of backup is by your last backup tape, Oracle cannot help in this case because it does not have a history of previous redo-log files , the ones that are ARCHIVED in ARCHIVELOG mode.

III. Oracle Alert Log and Trace Files

The alert log records the commands and command results of major events in the life of the database,e.g. Tablespace creation, redo log switches, recovery operations, and data base startups.

This file is located at the location specified by the parameter BACKGROUND_DUMP_TEST in the init.ora file.

When a background processes is terminated or abnormally aborts an operation, it usually produces a trace file containing an error message causing the failure. Dumps of the current process stacks, currently executing cursors, and many other information pertinent to the problem. A background dump tes