Database Reference
In-Depth Information
Further, from the output of
gv$ges_enqueue
, we can see that Process with PID 404 is requesting a lock on the
resource
[0xb6dff3][0x0],[TM]
in
KJUSEREX
mode. Since that resource is already held by other two processes in
KJUSERCW
mode (which is incompatible with
KJUSEREX
mode), PID 404 is waiting for the lock to be available. State of
the lock for PID 404 is set to
OPENING
, indicating that lock is not granted yet.
col state format a10
col inst_id format 99 head Inst
col owner_node format 99 head 'Owner|Node'
SQL> SELECT inst_id, resource_name1, pid, state,
owner_node , grant_level, request_level
FROM gv$ges_enqueue
WHERE resource_name1 LIKE '[0xb6dff3][0x0],[TM]%' ;
Owner
Inst RESOURCE_NAME1 PID STATE Node GRANT_LEV REQUEST_L
---- ------------------------------ ---------- ---------- ----- --------- ---------
1 [0xb6dff3][0x0],[TM][ext 0x0,0 21877 GRANTED 0 KJUSERCW KJUSERCW
2 [0xb6dff3][0x0],[TM][ext 0x0,0 20444 GRANTED 1 KJUSERCW KJUSERCW
3 [0xb6dff3][0x0],[TM][ext 0x0,0 404 OPENING 2 KJUSERNL KJUSEREX
Reviewing the output of
ges_blocking_locks.sql
script, we can identify that SID 8013 is requesting the lock in
KJUSEREX mode and waiting on event “enq: TM - contention.”
INST_ID : 3
RESOURCE_NAME1 : [0xb6dff3][0x0],[TM][ext 0x0,0
GRANT_LEVEL : KJUSERNL
REQUEST_LEVEL : KJUSEREX
STATE : OPENING
SID : 8013
EVENT : enq: TM - contention
SEC : 3516
Table
11-3
lists the most commonly seen TM resource request modes in a typical application life cycle. This table
also guides common solutions to the locking contention with the listed request modes.
Table 11-3.
TM Lock Contention and Modes
Lock Mode
Request Mode
Comment
RAC Equivalent
Possible causes
3
6
Exclusive
KJUSEREX
Identify why a table is locked in exclusive
mode. Possibly, DDL statements on the table.
3, 4
4
Share
KJUSERPR
Unindexed foreign keys, lock in share mode, etc.
HW Enqueue Contention
High-Water Mark (HWM) of a segment indicates the highest-ever used block of that segment; essentially, a marker
between used space and unused space. Full Table Scan access path reads all blocks below HWM. During a DML
operation, to populate rows, new blocks may need to be formatted and that would require HWM to be increased.
Changes to the HWM are performed under the protection of HW enqueues. During excessive concurrent
insert/update workload, processes can wait for HW enqueue contention.
