enqueues_locks_resolutions_causes.doc

aknight

6页

0次

2025-04-02

免费下载

ORACLE ENQUEUE WAITS & CAUSES WITH POSSIBLE ACTIONS TO BE CHECKED OR VERFIED

Lock

Type

Wait Event Lock Mode &

Type

Causes Actions Remarks

User

Lock

Enq: Tx Row Lock

Contention

TYPE is TX,

REQUEST is 6

 Two transactions

simultaneously access the

same data record in change

mode.

Find the sessions that are contending to change the same blocks by using following query

col file# for 99999

col block# for 999999

col obj for a18

col type for a9

col lm for 99

col bsid for 9999

select count(*) cnt,

session_id sid,

substr(event,1,30) event,

mod(p1,16) as lm,

sql_id,

CURRENT_OBJ# || ' ' || object_name obj

, o.object_type type

, CURRENT_FILE# file#

, CURRENT_BLOCK# block#

, blocking_session bsid

from v$active_session_history ash,

all_objects o

where

event like 'enq: T%'

and o.object_id (+)= ash.current_obj#

group by

event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#,

CURRENT_BLOCK#, BLOCKING_SESSION

order by count(*)

1. Once you identify which object it is or the SID either kill or proceed to ask the application team to

commit the original sessions that is holding

2. If the object is an index, that means

User

Lock

Enq: Tx Row Lock

Contention

TYPE is TX,

REQUEST is 4

 Two transactions change data

records simultaneously, which

are identical in terms of a

unique or primary key

constraint.

Diagnosis Same as above the solution would be

To correct this problem, ensure that the same primary key is not inserted or changed repeatedly in parallel in

a table. (for example, by checking whether the key already exists). If this is not possible, increase the

commit-frequency in order to release the enqueues again earlier.

Mode 4, happens for 3 reasons

http://sureshgandhi.wordpress.com

ORACLE ENQUEUE WAITS & CAUSES WITH POSSIBLE ACTIONS TO BE CHECKED OR VERFIED

1. Unique key contention

2. Foreign Key contention

3. Bitmap index contention

User

Lock

enq: TX - index

contention

TYPE is TX,

REQUEST is 4

 Several DML operations want

to access the same index block

simultaneously

If such a 'multiple requested' index block is not in the buffer pool, it must be read by the first transaction from

the disk ("db file sequential read"). The other DML operations wait for a TX enqueue during this process.

Index Block splits,

User

Lock

enq: TX - allocate ITL

entry

TYPE is TX,

REQUEST is 4

 Several transactions want to

change rows from the same

block, but cannot find a free

entry in the Interested

Transaction list (ITL) in the

block header.

If the enqueues occur in a table in connection with massively parallel inserts or updates, this is usually a

problem to do with accessing the Interested Transaction List (ITL) in the lock header. The solution for this

requires increasing the INITRANS sufficiently, as described in Note 84348

ITL waits for parallel INSERT operations can be alleviated by increasing the FREELISTs or FREELIST

GROUPs, or by switching to ASSM.

User

Lock

enq: TX - row lock

contention

TYPE is TX,

REQUEST is 4

 Several DML operations want

to change data records in the

same block of a bitmap index.

Changes in bitmap indexes always lead to the whole area being locked. If several transactions want to

change the same area, row lock waits occur in level 4.

Drop the bitmap indexes before you begin with parallel loading. See also the relevant section in Note 84348

for further information.

User

Lock

Enq: TX – contention TYPE is TX,

REQUEST is 4

 this is then possible if parallel

INSERTs are running and one

of these INSERTs triggered an

AUTOEXTEND operation and

must wait for "Data file init

write"

this is then possible if parallel INSERTs are running and one of these INSERTs triggered an AUTOEXTEND

operation and must wait for "Data file init write"

 Setting Table space read only

o Session 1 – start transaction, don’t commit

o Session 2 – alter tablespace read only

 Free Lists

o Non-ASSM

o Sessions freeing block

o If no txs free lists available (min 16, grow up depending on block size) , pick one and

wait TX 4

 2PC – two phase commit

o First does a prepare the commit

o Any read or write access in the intervening time waits TX 4

User

Lock

enq: TM – contention TYPE = TM,  Object lock during operations

such as REBUILD INDEX or

Determine the session that holds the lock and determine its LMODE.

http://sureshgandhi.wordpress.com

ORACLE ENQUEUE WAITS & CAUSES WITH POSSIBLE ACTIONS TO BE CHECKED OR VERFIED

REQUEST = 3 VALIDATE STRUCTURE or

due to command "LOCK

TABLE ... IN EXCLUSIVE

MODE [NOWAIT]"

If LMODE = 4, the locking session currently executes an operation such as an index rebuild or a consistency

check using VALIDATE STRUCTURE without an ONLINE flag. Terminate this operation, so other

transactions can access the object again.

If LMODE = 3, the session that holds the lock is not the main problem, since several sessions can use a TM

lock with mode 3 at the same time and in the same table (for instance, if two transactions change different

records in the same table at the same time). Instead, you must analyze whether there is another waiter that

requires a TM enqueue with REQUEST = 4 (for example, REBUILD INDEX ONLINE, see below) and which

therefore blocks all requests in the queue lined up behind it.

enq: TM – contention TYPE = TM,

REQUEST = 4

 REBUILD INDEX ONLINE or

SHRINK waits due to an active

TX enqueue on the

corresponding table.

As described in Note 682926, a REBUILD INDEX ONLINE is only online-enabled to a limited extent. You

must therefore make sure that an online rebuild is only started if no long-running transactions are working in

the relevant table. See Note 910389 for information about Segment Shrinking

enq: US – contention

 US-enqueues occur in relation

to the automatic online or

offline setting of undo

segments by the SMON-

process or by other undo

segment activities.

To reduce US enqueues, you can set the following event. Among other things, this event suppresses the

automatic offline setting of undo segments that are no longer required.

event="10511 trace name context forever, level 2"

If US enqueues are responsible for performance problems in the Real Application Cluster, import the latest

Oracle patch set. If this does not solve the problem, create an SAP customer message to initiate further

analysis.

enq: CI – contention

 The CI enqueue is allocated

when a session executes a

cross instance call, in other

words, when a background

process is triggered in the

Oracle instance to carry out a

certain task

When a session has allocated the CI enqueue successfully, it waits for the Wait Event "rdbms ipc reply" until

the background process issues a response. Other sessions that also require the CI enqueue must wait for

it.this means that the optimization of CI enqueues corresponds to the optimization of "rdbms ipc reply" waits.

Therefore, refer to the "rdbms ipc reply" section in Note 619188.

enq: RO - fast object

reuse

 The reuse object enqueue is

used in exactly the same way

as the CI enqueue in

TRUNCATEs and DROPs.

refer to the details described above

enq: TT - contention

 The TT enqueue is used to

avoid deadlocks in parallel

tablespace operations (deleting

and creating tablespaces,

creating data files, tablespace

point-in-time recovery, creating

undo segments in the

tablespace, ALTER

If an ALTER DATABASE TEMPFILE DROP hangs due to a TT enqueue that is retained by the SMON

process in Oracle 9.2.0.6 or lower, Oracle bug 3833893 is responsible. This problem has been solved in

Oracle 9.2.0.7. For more information, see Note 867681.

Longer waits for the TT enqueue are generally caused by Oracle bugs. If you experience problems with this

enqueue type, you should therefore first import the latest Oracle patchset.

http://sureshgandhi.wordpress.com

of 6

免费下载

oracle lock

相关文档

评论