Enqueue Structures

1. Enqueue Structures

2. Objectives • After completing this lesson, you should be able to • do the following: • Identify the internal resources and structures that are associated with enqueues • Explain the process of acquisition, conversion, and release of enqueues • Describe how to tune waits for enqueues • Interpret diagnostic enqueue information

3. Introduction to Oracle Lock Types • Oracle locks fall into one of the following categories: • Data Dictionary Locks (DDL) • Row cache locks • Library cache locks and pins • Data Manipulation Locks (DML) • Row locks • Table locks • Internal locks and latches • Distributed locks • Parallel Cache Management (PCM) locks

4. Enqueues • Enqueues are a service that is provided by the Kernel Service Enqueues (KSQ) layer. • Other layers that make use of this service are called client layers. • Enqueues are a locking mechanism for managing access to shared resources. • Scope can be local to one instance, or can span all instances. • Interfaces are provided to acquire, convert, and release enqueues. • If an enqueue is unavailable, the client layer can choose to wait indefinitely or with timeout.

5. Enqueue Identifiers • Each enqueue is identified by a unique name, which is also known as the lock or resource name. • This identifier has the form <Type-ID1-ID2>: • Type is a two-character string, for example “TM.” • ID1 and ID2 are positive numbers (4 bytes).

6. Mode Null SS SX S SSX X Internal ID KSQMNULL KSQMSS KSQMSX KSQMS KSQMSSX KSQMX Value 1 2 3 4 5 6 Description null subshare subexclusive share share/subexclusive exclusive Enqueue Modes

7. Held/Get Null SS SX S SSX X Null SS SX S SSX X Enqueue Compatibility • When a lock is held, an attempt to get another one follows these compatibility rules:

8. Resources and Locks • Enqueues are acquired, converted, and released by sessions (not processes). • At any given time, there may be sessions that have acquired a lock, and also sessions waiting to acquire or convert a lock. • For each enqueue there is a resource structure (ksqrs), which maintains a list of owners, waiters, and converters. • Each owner, waiter, and converter has a lock structure (ksqlk).

10. Resource Structure <TM-432-0> Owners Waiters Converters S X S -> X S SX Lock Structures(showing modes) S

11. Client and Managed Enqueues • Enqueues may be of two different types: • Client enqueues • The lock structures are allocated by the client layer. • For example, DML locks (TM) • Managed enqueues • The lock structures are allocated by KSQ itself. • For example, media recovery enqueues (MR)

13. State Objects • All lock structures are embedded within a state object to allow PMON to recover in the event of process failure. • Client lock structures are embedded within customized state objects, for example: • DML lock (TM) structures are embedded in a DML lock state object. • Transaction (TX) lock structures are embedded in a transaction state object. • Managed lock structures are embedded within an enqueue state object.

14. Hashing and Latching • All resource structures form part of the resource table. • To find a resource within the resource table, a hashing algorithm is used. • The resource name (<Type-ID1-ID2>) is hashed to produce an index into the table of hash buckets (ksqht). • Each hash bucket contains a linked list of resources known as a hash chain. • The hash chains are covered by one or more latches (children of the enqueue hash chainslatch) that are allocated in a round-robin fashion.

15. Hashing and Latching Hash Buckets Resource Structures hash(<MR-3-0>) -> 5 0 . . . 1 2 enqueue hash chain 3 4 5 6 7

16. Hashing and Latching Hash Buckets Resource Structures 0 . . . 1 2 enqueue hash chain 3 4 5 (<MR-3-0>) hash(<TM-432-0>)->0 6 7 enqueues

17. Resource Free List • To enable reuse, unused resource structures are placed on a linked list, known as the resource free list. • Operations in the resource free list are covered by the enqueues latch.

18. Initialization Parameters • ENQUEUE_RESOURCES: resource structures • _ENQUEUE_LOCKS: enqueue lock structures • _ENQUEUE_HASH: size of hash table for resource structures • _ENQUEUE_HASH_CHAIN_LATCHES: number of latches protecting the hash chains of the resource structure

19. Lock Acquisition • Lock acquisition occurs when a session gets a lock that it did not have. • A session is allowed to acquire a lock if: • There are no converters or waiters ahead of it. • The mode that is requested is compatible with the modes that are already held by other sessions. • Acquisition steps: • Locate the resource structure. • Obtain a free lock structure. • Populate it for the resource that is requested. • Link the lock structure to the resource structure.

20. Lock Conversion • Lock conversion occurs when a session requests to change the mode of a currently held lock. • A session is allowed to convert a lock if the mode that it requests: • Is a subset of the mode that it already holds • Is compatible with the modes that are held by others

21. Lock Release • A session is always allowed to free a lock that it has on a resource. • Release operation: • Locate the resource structure. • Unlink the lock structure from the resource. • Link the lock structure to the lock free list. • Post the next converter or waiter (if any).

22. Enqueue Operations • When a new lock is requested, the Oracle server checks the queues to verify whether the request can be granted or not. • When a lock is released or converted, the converters and waiters are also rechecked to see whether they can be acquired. • The converters queue is processed first, followed by the waiters queue.

23. Enqueue Operations Example <TM-432-0> Owners Waiters Converters 1. S X S->X 1 2 1 S 3 S Owner releases lock 4 <TM-432-0> Owners Waiters Converters 2. X X 1 2 S 3 S Owner releases lock 4

24. Enqueue Operations Example 3. <TM-432-0> Owners Waiters Converters X S 2 3 S 4 Owner releases lock 4. <TM-432-0> Owners Waiters Converters S 3 S 4

25. Wait Event: Enqueue • When a session is waiting on a local enqueue, it can be found to be waiting on the enqueue wait event. • This can be seen by querying V$SESSION_WAIT as follows: SQL> select * 2 from v$session_wait 3 where event = 'enqueue';

27. Tuning Enqueue Waits • Tuning of enqueue waits depends on the type of enqueue that is causing the waits. • Possible approaches: • Reduce the number of requests for the enqueue. • Reduce the amount of time for which the enqueue is being held.

28. Deadlock Detection • If a lock has not been acquired or converted after waiting, then a deadlock check is made by calling ksqded(). • Starting with the session that requests the lock, this function scans all the queues that are looking for the blocking session. • Recursively, the function is called again with the blocking session, and a network of dependencies is built. • A deadlock occurs when a session is revisited by the recursive call and ORA-60 is signaled.

29. Deadlock Detection <TM-432-0> Owners Waiters Converters S X 1 2 <TM-320-0> Owners Waiters Converters S X 2 1

30. Diagnostics: State Object Dump • Information in a State Object Dump offers significant insight on enqueues: • Lock structures are embedded within state objects and can be found in system or process state dumps. • The enqueue’s name is shown along with the mode that is held (mode) and the mode that is requested (req). • This indicates which queue it resides in: • Owner: mode only • Waiter: req only • Converter: mode and req

34. Diagnostics: Enqueues Dump • Obtained with oradebug or the following command: SQL> alter session set events 2 'immediate trace name enqueues level 2'; • This trace allows the following levels: • Level 1: dump a summary of active resources and locks, the resource free list, and the hash table • Level >= 2: include a dump of resource structs • Level >= 3: include a dump of lock structs

36. Diagnostics: V$LOCK • V$LOCK contains a list of all the lock structures that are currently held in the system. • TYPE, ID1, and ID2, can be used to join with V$RESOURCE. • LMODE and REQUEST indicate which queue the session is waiting for, as follows: • LMODE > 0, REQUEST = 0 owner • LMODE = 0, REQUEST > 0 waiter (acquirer) • LMODE > 0, REQUEST > 0 converter

37. Diagnostics: V$RESOURCE • V$RESOURCE gives a list of all the resource structures that are currently locked in the system. • Each row is associated with one or more rows in V$LOCK. • V$RESOURCE is based on X$KSQRS.

38. Diagnostics: V$LOCKED_OBJECT • V$LOCKED_OBJECT shows all the locks on objects (TM locks) in the system. • Provides the following cross-reference information: • Rollback segment associated with the transaction that is run by the locking session (if any) • Username at the operating system level • Process ID

39. Diagnostics: Scripts • The script catblock.sql creates the following views: • DBA_LOCKS like V$LOCK • DBA_DML_LOCKS DML (TM) locks only • DBA_DDL_LOCKS DDL locks (not enqueues!) • DBA_WAITERS waiters and converters • DBA_BLOCKERS blockers • The script utllockt.sql gives a tree of locks. It requires catblock.sql to have been run first.

41. Summary • In this lesson, you should have learned about: • Enqueue internal concepts • Enqueue operations • Acquisition • Conversion • Release • Tuning enqueues • Enqueue diagnostics • Object dumps • Data dictionary views

42. References • WebIV Note:33372.1 • Source: ksq.h,ksq1.h,ksq.c