Shulou(Shulou.com)06/01 Report--

How to sort out Enqueue, in view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

When dealing with enqueue waits, keep these points in mind:

1 Enqueues is the locks applied to database objects. Different from latch (applied to SGA memory).

2 Enqueues is initiated by the application and is transactional.

3 Oracle session is waiting to get a specific enqueue. The name of enqueue and mode are recorded in the P1 parameter. Different ways should be adopted to solve different types of enqueue competition.

4 all the way up to Oracle 9iRegent enqueue wait event represents all enqueue waits

Starting with Oracle 10g, enqueue waits is classified and defined (described in more detail below).

What Is an Enqueue?

What an enqueue is is determined by the specific environment. If it is regarded as a verb, it means the action of putting a lock request in a queue. If it is regarded as a noun, it indicates a specific lock, such as TX's enqueue.

Enqueues is a very sophisticated locking mechanism used to manage access to database shared resources, such as (objects, background jobs, and redo threads). Oracle uses enqueues for two purposes: first, when enqueue is an incompatible mode (mode), enqueues prevents concurrent sessions from sharing database resources. Second, when enqueue is in compatibility mode, enqueues allows concurrent sessions to share database resources.

When session A requests a lock resource on a database object, if the requested lock mode is in an incompatible mode and the database object is already held by another session in an incompatible mode, session A places its lock request in a queue and waits sequentially for it to be called (to prevent livelock). This event is called enqueue wait.

Enqueue waits not only includes buffer locks (discussed in the "Buffer Busy Waits" section), library cache locks, row cache locks, and PCM (Parallel Cache Management) locks, but also waits for all kinds of local locks.

What Is an Enqueue Resource?

An enqueue resource is due to an enqueue lock database resource.

Oracle is managed through an internal array structure.

You can see the specific entries through the X$KSQRS (kernel service enqueue resource) or V$RESOURCE view.

SQL > select * from v$resource

ADDR TY ID1 ID2

-

6D304434 XR 4 0

6D304594 CF 0 0

6D3046F4 RT 1 0

6D30474C RS 25 1

6D304904 MR 30

6D30495C MR 4 0

6D3049B4 MR 5 0

6D304B24 MR 1 0

6D304B7C MR 2 0

6D304BD4 MR 201 0

6D305634 TA 6 1

ADDR TY ID1 ID2

-

6D305B5C PW 1 0

6D3069DC TS 3 1

13 lines have been selected.

SQL >

Based on the query output above, we can see that enqueue resource structure consists of lock type and two parameters. Oracle uses two character symbols such as (TX, TM, TS, MR, RT, etc) to denote lock type. The two parameters of ID1,ID2 have different meanings due to the difference of lock type.

For more information, please see Chapter 6, part Enqueue, Table6-3.

Prior to Oracle10g, the meaning of ID1 and ID2 for different types of lock was not disclosed.

In Oracle10g, you can easily get it through the following query.

Col name for a20

Col parameter2 for a20

Col parameter3 for a20

Select name,parameter2,parameter3 from v$event_name

/

ID1 and ID2 will be introduced in detail later in the enqueue resource contention section of this article.

The maximum number of enqueue resources that can be locked in parallel by lock manager is controlled by the ENQUEUE_RESOURCES initialization parameter. The default value of this parameter is usually sufficient. If you use parallel DML operations in your application, you can consider increasing the value of ENQUEUE_RESOURCES.

About ENQUEUE_RESOURCES [10.. 65535]

At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20.For three or fewer sessions, the default value is the number of database files + 20.For 4 to 10 sessions, the default value is the number of database files + ((SESSIONS-3) * 5) + 20.For more than 10 sessions, it is the number of database files + ((SESSIONS-10) * 2) + 55.

If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then Oracle uses the value you provide. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number specified by ENQUEUE_RESOURCES is exceeded. You can check resource usage by querying V$RESOURCE_LIMIT.

Processes that cannot get an enqueue resource will report ORA-00052: "maximum number of enqueue resources exceeded" error.

The V$RESOURCE_LIMIT view provides information about the use of system resources. This view allows you to monitor the consumption of many database resources (resources, locks, or processes).

Notice several fields in this view:

MAX_UTILIZATION indicates the maximum consumption of such resources after the instance is started.

The following monitors enqueue-related resource consumption:

Col name for a18

Col limit_usage for a15

Select RESOURCE_NAME name

CURRENT_UTILIZATION cur_usage

MAX_UTILIZATION max_usage

LIMIT_VALUE limit_usage

INITIAL_ALLOCATION init_allo

From v$resource_limit

Where resource_name in ('enqueue_resources','enqueue_locks'

'dml_locks','processes','sessions')

/

NAME CUR_USAGE MAX_USAGE LIMIT_USAGE INIT_ALLO

--

Processes 19 24 90 90

Sessions 23 30 104 104

Enqueue_locks 13 24 1480 1480

Enqueue_resources 13 13 UNLIMITED 676

Dml_locks 0 7 UNLIMITED 456

What Is an Enqueue Lock?

An enqueue lock is lock itself. Oracle is managed by a separate array. You can try to see it through X$KSQEQ (kernel service enqueue object) or V$ENQUEUE_LOCK. The implicit parameter _ ENQUEUE_LOCKS affects the size of the array.

Oracle uses different structures to manage TX and TM queues.

X$KTCXB (kernel transaction control transaction object-the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock). The TRANSACTIONS and DML_LOCKS initialization parameters determine the structural size of the managed TX and TM queue.

Of course, you can get all the database locks information by querying V$LOCK. It is also an important view for us to diagnose enqueue contention.

Enqueue Architecture

Within the database, the cache buffers architecture in enqueue architecture and sga is very similar. (friends who have read eygle can refer to learning enqueue architecture.)

The main components of enqueue architecture are composed of enqueue hash chains latches, enqueue hash table, enqueue hash chains and and enqueue resources.

The quantitative relationship between them:

Enqueue hash chains latch (1) a hash bucket (1:1) an enqueue hash chain.

(this means that a hash chains latch can manage multiple hash bucket, and each hash bucket contains only one hash chain.)

Sub-enqueue hash chains latches protects enqueue hash table and hash chains. By default, the number of enqueue hash chains latches is equal to CPU_COUNT, which can be adjusted by the implicit parameter _ ENQUEUE_HASH_CHAIN_LATCHES.

According to the resource type and identifiers v$resource (TY,ID1,ID2) Enqueue resources, it is hash to enqueue hash table and placed in the corresponding enqueue hash chains. To use an enqueue resource, you must get the corresponding enqueue hash chain. The size of enqueue hash table is derived from the SESSIONS initialization parameter, or it can be set by setting _ ENQUEUE_HASH.

If you ever need to increase the ENQUEUE_RESOURCES parameter significantly from its default value, you might want to keep an eye on the sleep rate of the enqueue hash chains latches. This is because the enqueue hash table length will remain the same because it is derived from SESSIONS, not from ENQUEUE_RESOURCES. The combination of a high demand for enqueue resources and a small enqueue hash table will result ina higher hash collision rate and potentially lengthy hash chains. This problem manifests itself as latch contentions for the enqueue hash chains latches. In this case, you need to increase the _ ENQUEUE_HASH.

Enqueue hash table length = (SESSIONS-10) * 2) + 55

Learn further through the dump enqueue structure:

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

Decoding Enqueue Type and Mode

By decoding the P1 column in Oracle, we can know that enqueue type and mode.

Select sid, event, p1, p1raw

Chr (bitand (P1maim 16777216) / 16777215) | | chr (bitand (P1Mague 16711680) / 65535) type

Mod (P1, 16) "MODE"

From v$session_wait

Where event = 'enqueue'

/

Another way. You can also use the v$session_wait (p1raw) column.

The values from the above example are from a 64-bit Oracle Database. You can ignore the leading zeros and focus on the last 4 bytes (that is, the last eight numbers). The high order 2 bytes give the enqueue type. Using 54580006 as an example, the 2 high order bytes are 0x5458Hex. Now, 54Hex is decimal 84 and 58Hex is decimal 88, so the enqueue type can be discovered as follows: (Appendix B has a complete list of enqueue types.)

Select chr (84) | | chr (88) from dual;CH--TX

After 10g Oracle, enqueue wait event name can be obtained directly by v$session_wait (event).

Processes request enqueue locks in one of these six modes: Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X)

This section can be seen in the Oracle-related documentation.

Common Causes, Diagnosis, and Actions

Because of the wide variety of enqueue type, the reasons for an enqueue wait event are also different. We can find different solutions according to the type and mode of multiple session competing enqueue.

A corresponding record is maintained within the enqueue,Oracle of each type through X$KSQST. In Oracle9i, through the V$ENQUEUE_STAT view, you can get the request statistics of different type enqueue (statistics after instance startup).

-- Oracle 7.1.6 to 8.1.7

Select inst_id

Ksqsttyp inst_lock

Ksqstget inst_Gets

Ksqstwat inst_Waits

From x$ksqst

Where ksqstwat > 0

Order by inst_id,ksqstwat

/

-- Oracle9i Database and above

Select *

From v$enqueue_stat

Where cum_wait_time > 0

Order by inst_id, cum_wait_time

/

Some of the most common enqueue wait events are discussed below.

Wait for TX Enqueue in Mode 6

Waiting for TX enqueue in mode 6 is the most common enqueue wait

(In Oracle Database 10g, the wait event name is enq: TX-row lock contention.) This wait event occurs when one thing attempts to update or delete one or more rows of records that have been locked by another thing. Usually this is caused by the application.

Eg.

In Session A:

Update a set object_id=0 where object_id=11

Meanwhile, execute the same statement in Session B, Session B is hung:

Update a set object_id=0 where object_id=11

Session B will always wait for session A to submit or roll back this thing.

There is no other way to release row exclusive lock held by Session A (Kill session A will cause things to be rolled back by pmon and release corresponding lock resources)

The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK-A3950688 A395069C 10 TM 188154 0 3 0 3 0

A304E2A0 A304E2B0 10 TX 65585 147836 0 6 30

01AD23D4 01AD24A4 20 TX 65585 147836 6 0 10 1A3950A28 A3950A3C 20 TM 188154 0 3 0 10 0

Whenever you find a TX enqueue waiting event, the first task is to find out which session is the lock blocker (session A) and which session is the lock waiting (session B). In a busy system, there can be multiple lock waiters.

Use the following sql to locate:

/ *

BLOCKER_SID: lock holder sid (blocking sid)

WAITER_SID: waiting for sid

MODE_HELD: lock holding mode

REQUEST_MODE: request mode

, /

Select / * + ordered * / a.sid

Blocker_sid

A.username

Blocker_username

A.serial#

A.logon_time

B.type

B.lmode mode_held

B.ctime time_held

C.sid waiter_sid

C.request request_mode

C.ctime time_waited

From v$lock b

V$enqueue_lock c

V$session a

Where a.sid = b.sid

And b.id1 = c.id1 (+)

And b.id2 = c.id2 (+)

And c.type (+) = 'TX'

And b.type = 'TX'

And b.block = 1

Order by time_held, time_waited

/

You can also use the following query to locate which database resources are being requisitioned.

Select c.sid waiter_sid

A.object_name

A.object_type

From dba_objects a

V$session b

V$session_wait c

Where (a.object_id = b.row_wait_obj# or a.data_object_id = b.row_wait_obj#)

And b.sid = c.sid

And chr (bitand (c.p1mam Lay 16777216) / 16777215) | | chr (bitand (c.p1mem16711680) / 65535) = 'TX'

And c.event like 'enq%'

/

When a TX Enqueue in Mode 6 wait event occurs

It is important to get the sql statements that waiting session and blocking session are executing. We can use this sql statement to locate the approximate scope of the possible problems in the application (1) it is very likely that the application does not have commit 2 without timely commit or rollback DML operations, which needs to be adjusted according to the application requirements.

Wait for TX Enqueue in Mode 4-ITL Shortage

The waiting event of TX enqueue in mode 4 is divided into three parts.

1 > ITL (interested transaction list) shortage

2 > Unique key enforcement

3 > Bitmap index entry

Ok, let's start with ITL Shortage.

SQL > select dbms_rowid.ROWID_RELATIVE_FNO (rowid) file_no

2 dbms_rowid.ROWID_BLOCK_NUMBER (rowid) blk_no

3 from a

4 where rownum alter system dump datafile 4 block 1550

The system has changed.

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.017.00000128 0x00802ee0.004b.2a Cmurmuri-0 scn 0x0000.00076ef1

0x02 0x0005.01c.00000133 0x00803f33.0059.28-- U9 fsc 0x0009.00079e2b

0x03 0x0002.02c.0000012a 0x00800605.005b.1b Cmurmuri-0 scn 0x0000.00076d65

ITL is the list of things of interest within the data block, and it is the transaction slot of things within the data block. It is determined by the INITRANS and MAXTRANS clauses when the table is created. "--U -" indicates that this ITL is in use.

By default, the table contains 1 ITL per data block, the index has 2 ITL, and each ITL occupies 24 bytes, with USN.SLOT#.WRAP#. The form includes transaction ID. Every DML transaction must occupy the ITL space in the data block before being processed. When all available ITL in a database block is in use, and there is no space in PCTFREE for ORACLE to dynamically allocate a new ITL transaction slot, ITL contention occurs. In this case, the session will continue to wait until a transaction is committed or rolled back.

The following listing shows an example of the TX enqueue wait in mode 4 that is caused by ITL shortage, as seen in the V$LOCK view.

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK-8A2B6400 8A2B6414 8 TM 3172 0 3 0 248 0

89EF3A0C 89EF3A1C 8 TX 131147 13 0 4 248 0

01A4177C 01A41848 9 TX 131147 13 6 0 376 18 A2B6388 8A2B639C 9 TM 3172 0 3 0 376 0

Starting from Oracle 9i, database objects requisitioned by ITL can be located through the following sql.

SELECT s.owner

S.object_name

S.subobject_name

S.object_type

S.tablespace_name

S.VALUE

S.statistic_name

FROM v$segment_statistics s

WHERE s.statistic_name = 'ITL waits'

AND s.VALUE > 0

ORDER by VALUE DESC

/

Wait for TX Enqueue in Mode 4-Unique Key Enforcement

Unique or primary key enforcement is yet another reason you might see contention for the TX enqueue in mode 4. (In Oracle Database 10g, the wait event name is enq: TX-row lock contention.)

This only occurs when multiple concurrent sessions insert the same key value into a table that has one or more unique key constraints. The first session to insert the value succeeds, but the rest freeze and wait until the first session commits or rolls back to see if "ORA-00001 unique constraint (% s% s) violated" should be raised.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view that is due to unique key enforcement. What is the difference between this listing and the one caused by ITL shortage? Notice that the waiter (SID=8) has two TX entries in the V$LOCK view. This doesn't mean that it owns two transactions. In fact, the V$TRANSACTION view shows only two transactions-one for SID 8 and another for SID 9. This shows SID 8 is waiting for the TX lock held by SID 9, and it wants a share lock (mode 4) on the object. SID 8 also holds a TX lock for its own transaction. Another thing you should be aware of is the object ID that is recorded in ID1 of the DML transaction (TM) is always the table ID, not the index ID, although a unique key is enforced through an index.

Wait for TX Enqueue in Mode 4-Bitmap Index Entry

A wait for the TX enqueue in mode 4 can also occur when multiple sessions try to update or delete different rows that are covered by the same bitmap entry. Of course, this does not apply if the application does not use bitmap indexes.

Unlike the B-tree index entry, which contains a single ROWID, a bitmap entry can potentially cover a range of ROWIDs. So when a bitmap index entry is locked, all the ROWIDs that are covered by the entry are also locked.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view due to bitmap entry. What is the difference between this listing and the preceding one in the unique key enforcement? Can you tell if you are dealing with a bitmap index entry or unique key enforcement issue by looking at the V$LOCK view output? No, you can't. The object ID in the TM lock doesn't help either, as it is the object ID of the table and not the index. That is why it is very important for you to capture the SQL statement of the waiting and blocking sessions.

If the waiting session is attempting an insert, you are dealing with a unique key enforcement issue.

If waiting session is trying to do insert operation.

If the waiting session is attempting an update or delete, most likely you are dealing with a bitmap entry issue. If waiting session is trying to do a update or delete operation

In order to resolve the contention, you have to hunt down the offending user. However, the offending user is not always the user who holds the lock. That user was there first, for crying out loud. If the user has a legitimate reason to hold the lock, the waiters should back out of their transactions.

Wait for ST Enqueue

There is only one ST lock per database.

Database operations to modify UET$ (user scope table) and FET$ (idle range table) require ST locks, which include actions such as delete, intercept, join, and so on.

ST lock contention indicates that multiple active sessions perform dynamic disk space allocation or de-allocation in dictionary-managed tablespaces. Temporary tablespaces that are not created using TEMPORARY clauses and tablespaces managed by dictionaries that undergo widespread allocation and de-allocation are the main causes of ST lock contention.

Ways to reduce ST lock contention:

1) using locally managed tablespaces, all tablespaces in ORACLE9i can be managed locally.

2) use CREATE TEMPORARY TABLESPACE TEMPFILE... Command to create all temporary tablespaces.

This is the answer to the question about how to organize Enqueue. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

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.