Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use Oracle waiting events in Oracle Study". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "how to use Oracle waiting events in Oracle Study".

Oracle Study-- Oracle wait event

What is enqueue?

Enqueue can be explained either as a noun or as a verb. As a noun, it refers to a type of lock, such as Tx enqueue. As a verb, it refers to the operation of putting a lock request into the request queue.

We know that lock is a lock implementation mechanism that needs to be queued, which is different from latch. Latch is a lightweight lock that does not need to be queued. Enqueue is the implementation of the queuing mechanism of lock.

Lock is used to implement concurrent access to shared resources. If the lock of the two session requests is compatible, the resource can be locked at the same time. If the lock of the two session requests is not compatible, then one of the session must wait for the other session to release the lock it holds before it can acquire a lock on the shared resource. At this point, the waiting lock request for session needs to enter a queue, which is called enqueue waiting.

What is enqueue resource?

Lock has three possible states: acquired, transitioned, or waiting. For a certain lock, some session may have obtained the lock, some session have requested conversion, and some session have been held in the request but failed, resulting in waiting. So, for a lock, you need three queues to describe the status of these session. These three queues, called Owner,waiter and convert queues, are managed by a single structure, enqueue resource.

Enqueue has a name, which is made up of the type of the corresponding lock plus the ID1,ID2 parameter, such as. The structure of the Enqueue resource, whose header is the name of the enqueue, followed by a pointer to the three queues.

The v$resource view allows you to view the enqueue in the current system.

Since there are many types of lock in the system at the same time, the enqueue resource structure is actually an array whose length is controlled by the implicit parameter _ enqueue_resources, that is, it controls the number of lock that can exist simultaneously in the system. If it is exceeded, the following error will be reported:

ORA-00052: "maximum number of enqueue resources exceeded"

Through the v$resource_limit view, you can see the utilization of various resources in the system.

What is enqueue lock?

Enqueue lock is lock itself. Oracle uses another array separate from enqueue reouserce to manage enqueue lock, whose length is controlled by the implicit parameter _ enqueue_locks.

You can see the details of the array through the view v$enqueue_lock.

What is enqueue hash?

From the above description of enqueue resource, we can see that when oracle looks for a lock, it needs to find the location of the lock on the enqueue resource number.

It is obviously inefficient to search sequentially on the array every time. We know that hash is an efficient search algorithm, so oracle also uses hash to find enqueue resource, introducing a hash array whose size is controlled by the implicit parameter _ enqueue_hash.

Through the hash calculation of the name of enqueue, the result is the position of an enqueue resource in the hash array, that is, to locate the specific hash bucket. If multiple enqueue resource have the same hash value, a linked list is formed in the same bucket.

Accordingly, to protect the hash array, you need to introduce a latch:enqueue hash chain. The latch has several sub-latch controlled by the implicit parameter _ enqueue_hash_chain_latches

What is enqueue freelist?

Similarly, the free locations in the enqueue resource array need to be managed through a freelist list so that each time a new location is requested, the entire array is not scanned. Enqueue freelist is protected by enqueues latch.

In fact, the Hash management style of enqueue resource is very similar to that of buffer cache/library cache. If you want to learn more about enqueue hash,resource and lock, you can dump to see the specific results:

Alter session set events' immediate trace name enqueues level 3'

Enqueue

The word Enqueue is actually another description of lock.

When we find a long enqueue wait event in the AWR report, it indicates that there are blocking and waiting in the database, and you can associate the enqueue activity section of the AWR report to determine which lock has experienced a long wait.

This wait event has two parameters:

Name: the name and type of enqueue.

Mode: the mode of enqueue.

You can use the following SQL to view the enqueue name and type of the current session wait:

SELECT event, CHR (TO_CHAR (BITAND (p1,-16777216)) / 16777215) | | CHR (TO_CHAR (BITAND (p1, 16711680)) / 65535) "Lock", TO_CHAR (BITAND (p165535)) "Mode" FROM v$session_wait WHERE event like 'enq%'

The enqueue for Oracle contains the following modes:

Mode Code interpretation 1 Null (NULL) 2 Row-S (SS) 3 Row-X (SX) 4 Share (S) 5 S/Row-X (SSX) 6 Exclusive (X)

There are the following types of enqueue for Oracle:

Enqueue abbreviations interpret BL Buffer Cache managementBR Backup/RestoreCF Controlfile transactionCI Cross-instance Call InvocationCU Bind EnqueueDF DatafileDL Direct Loader Index CreationDM Database MountDR Distributed Recovery ProcessDX Dirstributed TransactionFP File ObjectFS File SetHW High-water LockIN Instance NumberIR Instance RecoveryIS Instance StateIV Library Cache InvalidationJI Enqueue used during AJV snapshot refreshJQ Job QueueKK Redo Log "Kick" KO Multiple Object CheckpointL [Amurp] Library Cache LockLS Log start or switchMM Mount DefinitionMR Media recoveryN [Amurz] Library Cache binPE Alter system set parameter = valuePF Password filePI Parallel slavesPR Process startupParallel slave synchronizationQ [Amurz] Row CacheRO Object ReuseRT Redo ThreadRW Row WaitSC System Commit NumberSM SMONSequence NumberSQ Sequence Number EnqueueSR Synchronized replicationSort segmentST Space management transactionSV Sequence number ValueTA Transaction recoveryTC Thread CheckpointTE Extend TableTM DML enqueueTO Temporary Table Object EnqueueTS Temporary Segement (also TableSpace) TT Temporary TableTX TransactionUL User-defined LocksUN User nameUS Undo segment SerializationWL Being Written Redo LogXA Instance Attribute LogXI Instance Registration Lock

Case study:

12:54:01 SYS@ prod > conn scott/tigerConnected.12:54:05 SCOTT@ prod > select * from tab TNAME TABTYPE CLUSTERID---BONUS TABLEDEPT TABLEDEPT1 TABLEEMP TABLESALGRADE TABLET1 TABLE6 rows selected.12:54:09 SCOTT@ prod > grant all on dept1 to tom Grant succeeded.Elapsed: 00 Grant succeeded.Elapsed 0012 00.3712 SCOTT@ prod 54 SCOTT@ prod > update dept1 set dname='beijing' where deptno=10;2 rows updated.Elapsed: 00 update dept1 set dname='beijing' where deptno=10;2 rows updated.Elapsed 0012 00.0412 SCOTT@ prod 54 SCOTT@ prod > rollback;Rollback complete.Elapsed: 00 0012 00.0312 55 SCOTT@ prod 22 SCOTT@ prod > update dept1 set dname='NetWork' where deptno=10;2 rows updated.Elapsed: 000012 00.0212 51 SYS@ prod > conn tom/tomConnected.12:54:56 TOM@ prod > update scott.dept1 set loc='hongkong' where deptno=10 12:59:18 SYS@ prod > col event for a5012 SELECT event 5926 SYS@ prod > r 1 SELECT event, CHR (TO_CHAR (BITAND (p1,-16777216)) / 16777215) 2 | | CHR (TO_CHAR (BITAND (p1, 16711680)) / 65535) 3 "Lock", 4 TO_CHAR (BITAND (p1) 65535) "Mode" 5 FROM v$session_wait 6 * WHERE event like 'enq%'EVENT Lock Mode -enq: TX-row lock contention TX 6Elapsed: 0000 row lock contention TX 6Elapsed 00.011322 row lock contention TX 6Elapsed 48 SYS@ prod > select a.ORACLE_USERNAME A. SESSION _ ID _ from v$locked_object _ b. Type _ Dba_objects o 2 * where a.session_id=b.sid and a.object_id=o.object_idORACLE_USERNAME SESSION_ID TY LMODE REQUEST BLOCK OBJECT_NAM -- SCOTT 42 TX 6 0 1 DEPT1TOM 38 TM 3 00 DEPT1SCOTT 42 TM 3 00 0 DEPT1TOM 38 TX 0 6 0 DEPT1TOM 38 AE 4 0 0 DEPT1SCOTT 42 AE 4 0 0 DEPT16 rows selected. The above is all the contents of the article "how to use Oracle waiting events in Oracle Study". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.