latch free

The process waits for a latch that is currently busy (held by another process). Wait Time:

The wait time increases exponentially and does not include spinning on the latch (active waiting). The maximum wait time also depends on the number of latches that the process is holding. There is an incremental wait of up to 2 seconds.

select *
from v$latchname
where latch# = number;

Depending on the name of the latch, there is a different advice to give: KCL freelist latch

This latch protects the PCM lock element freelist in Fine Grain Locking. There is one latch for the one freelist. Only Lock Elements that have the free bit set are on the freelist. The free bit gets set when a buffer gets unlinked from the Lock Element. Only the releasable locks are put on the freelist.

KCL name table latch

This latch protects the name translation for the Lock Element. The name translation uses the Data Block Address and the Class of the block to hash to a Lock Element. If the Lock Element is used for Fine Grain or DBA locking, the name of the lock will be the DBA of the block. For Hashed locking the name will be Lock Element Index.

NLS data objects

This latch protects the loading and unloading of NLS data in the SGA. The NLS data is cached in the SGA.

archive control

The archive control latch protects the archive destination string in the SGA. If a session wants to update or read the archive destination, it will get this latch to make sure that the session gets a consistent view.

cache buffer handles

This latch protects the State Objects that are needed by a process to make a change to a block/buffer. Before the change, a buffer handle is acquired either from the process' private pool or the global pool if none are present. The access to the global pool is protected by this latch.

cache buffer chains

Before modifying a buffer the foregrounds need to get a latch to protect the buffer from being updated by multiple users. Several buffers will get hashed to the same latch. The buffers get hashed to a cache buffer chain latch depending on the DBA of the block that is in the buffer.

cache buffer lru chain

Protects the Least Recently Used list of cache buffers. For moving buffers around on this list the Oracle kernel needs to get this latch.

cache protection latch

During the cloning of a buffer the buffer header is automaticaly updated to reflect the new position in the buffer cache. Only one buffer at the time may be cloned, as it is protected by this latch. A lot of contention on this latch would indicate that there is a lot of concurrent cloning going on.

cached attr list

This latch protects a cache of attributes that are only used in Parallel Server. The attributes that are cached are, for example, the instance number. It also keeps track of the instance map (which instances are up and running). By caching this information it becomes available (relatively) to whole instance.

cost function

The cost function latch protects the resource limit table in the SGA. If multiple sessions would execute the alter resource cost statement, the new cost values would need to be protected when copied into the SGA.

dml lock allocation

This latch protects the list of State Objects (dml locks). Every time a transaction modifies a table, a DML lock is gotten and released when the change is committed. The number of State Objects for dml locks is determined by the init.ora parameter dml_locks.

enqueue hash chains

_enqueue_hash_chain_latches (default number of CPU's or cpu_count). Protects the individual hash buckets.

enqueues

This latch is used to add and remove the enqueue State Object to a parent Object State. It is also used if there are no free state objects on enqueue hash chain. The hash chain is calculated by:

((type[0] << 5) + type[1] + id1 + id2) % # buckets.

error message lists

Used by the Parallel Query Option to signal errors to the Query Coordinator.

global transaction

This latch is used to control the setting of a savepoint in a transaction control block (stored in the SGA).

global tx freelist

Only used when the Distributed Database Option is enabled.

global tx hash mapping

Only used when the Distributed Database Option is enabled.

instance latch

Only used when the Parallel Server Option is enabled. This latch is used to protect the freelist of Lock Context State Objects. And it is used to protect the count of LCK processes that are participating in a startup, shutdown or recovery phase.

latch wait list

Certain latches that are held a long time have a waiting list. This waiting list is protected by a single latch for all latches.

list of block allocation

This is the latch that protects the pool of State Objects for "cleanout on commit".

messages

There is a pool of message blocks in the SGA. This pool is controlled by the init.ora parameter _messages. If a process needs a message it will get the latch, search a linked list for a free message block, unlink this message block, put this message on the message queue for the intended process and then release this latch. The event rdbms ipc message indicates that a process is waiting for a message to arrive on this message queue. The event rdbms ipc message reply is used to indicate that a message is expected back by the sending process.

modify parameters values

This latch protects the modification of changeable/dynamic init.ora parameters.

multiblock read objects

This latch protects the allocation and release of multi block read objects from the SGA.

redo allocation

The redo allocation latch is used to allocate the redo buffers in the redo log buffer. It is also gotten when Oracle needs to advance to SCN as part of a checkpoint. This way we can make sure that nobody can use the new SCN number.

redo copy

When a redo buffer has been allocated and the size of the redo is larger than log_small_entry_max_size, the kernel will allocate a redo copy latch. The number of redo copy latches is controlled by the init.ora parameter log_simultaneous_copies (defaults to the number of CPUs). The LGWR will get all the redo copy latches before it will write redo to make sure that nobody is writing in redo buffers that are currently written.

row cache objects

This latch protects the access of the datadictionary cache in the SGA. When loading, referencing and freeing objects in the datadictionary cache you need to get this latch.

sequence cache

This latch protects the lists of State Objects that are used for sequence numbers. Allocating from and Releasing to this freelist is protected with this latch.

session allocation

This latch is used for a lot of things. It protects the session State Object freelist. The size of this freelist is determined by the init.ora parameter sessions. It also controls the updating of the systemwide statistics (v$sysstat). Licensing information is also controlled by this latch. The allocation of calls is also protected by this latch.

session idle bit

Every time the session starts to execute a call, the active bit will be set by the session and when the session finishes the call, it will clear the active bit. The status field in v$session will change from ACTIVE to INACTIVE (protected by this latch).

session switching

This latch protects the switching from sessions in XA.

sort extent pool

This latch protects the sort extents in the SGA. There is a cache of extents and descriptors. Access to these is protected by this latch. This latch will get used if a tablespace is marked temporary.

system commit number

This latch protects the SCN in the SGA. Any reference to an SCN (Recent, Current) means that you need to get this latch first.

trace latch

This latch protects the Trace State Objects in the SGA. The number of Trace State Objects is equal to the number of processes.

transaction allocation

This latch protects the pool of transaction state objects in the SGA. The number of Transaction State Objects is determined by the init.ora parameter transactions.

undo global data

This latch serializes the access to the Undo (aka Rollback) segment information in the SGA. Every time a session wants to know about the state of the Undo Segments, it has to get this latch.

user lock

This latch protects the user. It makes sure that a user can't be dropped while logging on or while logged on.

virtual circuit buffers

One latch per session.

virtual circuit queues

This latch serializes the access to queues. Each dispatcher process has its own queue. There are a total of mts_max_dispatcher + 2 queues. Basically the dispatcher will put a virtual circuit on a queue that is going to be picked up by a shared server process.

virtual circuits

This latch protects the pools of Virtual Circuit State Objects. If the Dispatcher allocates a Virtual Circuit, it is incoming. If a Shared Server process allocates a Virtual Circuit, it is outgoing. Also the ownership of the Virtual State Circuit gets moved from the Dispatcher to Shared Server and vice-versa.