1 / 42

Enqueue Structures

Enqueue Structures. 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

molly
Télécharger la présentation

Enqueue Structures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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).

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

  10. 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)

  11. 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.

  12. 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.

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

  14. 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

  15. 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.

  16. 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

  17. 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.

  18. 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

  19. 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).

  20. 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.

  21. 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

  22. 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

  23. 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';

  24. 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.

  25. 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.

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

  27. 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

  28. 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

  29. 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

  30. 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.

  31. 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

  32. 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.

  33. 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

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

More Related