enqueue memory scripts

Shared Memory Structures

Enqueues are shared memory structures that serialize access to database resources and are associated with a session or transaction. In Real Application Clusters (RAC), enqueues can be global to a database. If Real Application Clusters are not enabled, enqueues are then local to one instance.

Also see Oracle enqueues.

An enqueue is a lock that protects a shared resource, such as data, in order to prevent processes from updating the same data simultaneously. An enqueue includes a First In First Out (FIFO) queuing mechanism.

Enqueue waits usually point to TX enqueues, TM enqueues, ST enqueues and HW enqueues, explained as follows:

TX Enqueue - This type of enqueue is the most common enqueue wait. For example, one issue could be multiple updates to the same bitmap index fragment. A single bitmap fragment may contain multiple rowids. When multiple users are trying to update the same fragment, a commit or rollback needs to be issued to free the enqueue. The situation is most likely to surface when multiple users are updating the same block. If there are no free ITL slots, a block-level lock could occur. This scenario can be avoided by increasing the initrans and/or maxtrans to allow multiple ITL slots, and/or by increasing the pctfree on the table.

TM Enqueue - This type of enqueue occurs during DML to prevent DDL to the affected object. Indexes on foreign keys should be used to avoid this general locking issue.

ST Enqueue - This type of enqueue is used for space management and allocation for dictionary-managed tablespaces. Use LMTs, or try to preallocate extents or at least make the next extent larger for problematic dictionary-managed tablespaces.

HW Enqueue - This type of enqueue is used with the high-water mark of a segment; manually allocating the extents can circumvent this wait.

The following script will display three simple reports for analyzing the enqueues of the database:

rem
rem ENQUEUES9i.SQL
rem Mike Ault
rem
ttitle 'Enqueues Report'
spool enqueues
prompt Enqueues
col name format a25
col lock format a4 heading 'Lock'
col gets format 9,999,999 heading 'Gets'
col waits format 9,999,999 heading 'Waits'
col Mode format a4

SELECT *
FROM v$sysstat
WHERE class=4
;

SELECT chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1, 16711680)/65535) "Lock",
to_char( bitand(p1, 65535) ) "Mode"
FROM v$session_wait
WHERE event = 'enqueue'
/

Prompt Enqueue Stats

select * from v$enqueue_stat where cum_wait_time>0
order by cum_wait_time desc
/
spool off
ttitle off
Code Depot Username = reader, Password = arsenal
Enqueues

Wed Sep 22 page 1
Enqueues Report

INST_ID STATISTIC# NAME CLASS VALUE
------- ---------- ------------------------- ---------- ----------
4 22 enqueue timeouts 4 65
4 23 enqueue waits 4 54133624
4 24 enqueue deadlocks 4 0
4 25 enqueue requests 4 252483462
4 26 enqueue conversions 4 425394
4 27 enqueue releases 4 252482896

6 rows selected.

Lock Stats

Wed Sep 22 page 1
Enqueues Report

INST_ID Lock Mode
------- ---- ----
4 US 6
4 US 6
4 US 6
4 TX 6
4 TX 6

5 rows selected.

Enqueue Stats


Wed Sep 22 page 1
Enqueues Report

INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
------- -- ---------- ----------- ---------- ----------- -------------
4 US 54876879 53410542 54876822 0 1533644257
4 TX 63851995 461325 63867209 0 11031417
4 SQ 585304 243592 585304 0 5375456
4 CF 234529 1617 234470 59 24744
4 FB 12902 9571 12902 0 26668
4 HW 10517 4581 10517 0 16212
4 TA 2609 1568 2609 0 5491
4 CU 1594631 35 1594625 0 1426

8 rows selected.

The enqueues of concern will show the greatest amount of cumulative wait time (cum_wait_time) and will be shown first in the listing. The report also shows the enqueue related wait events and the current lock status for enqueue related activity. Since the report uses the GV version of the dynamic performance tables, it will list results for all instances in a RAC cluster. In the report shown above, instance 4 was the only active instance when the report was run.