9i (and below) One Wait : 'enqueue' had to decode name and mode 10g 208 enqueue waits Specific to each type of enqueue No longer need to decode name but the mode can still be important to decode For example, what was called an 'enqueue' wait before 10g is now broken out like enq: HW - contention Notice that the query uses 'v$active_session_history' but could also be used on v$session starting in 10g, or v$session_wait since version 7.

Wrapping this information up in one query we get: SELECTĬhr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1, 16711680)/65535) Type, mod(p1,16) lmode from v$active_session_history where event like 'enq%' Blocking SQL is Missing (Possibly with log miner)ĪSH data Waiter SESSION_ID SESSION_SERIAL# USER_ID Object CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# SQL Waiting SQL_ID Blocker (as of 10gR2) BLOCKING_SESSION BLOCKING_SESSION_STATUS BLOCKING_SESSION_SERIAL# Lock Type and Mode Event = Type (name) P1 = Type | Mode Lock Mode and Name Select parameter1 from v$event_name where name=‘enqueue’ Parameter1 - Name| mode Select p1, p1raw from v$session where event like 'enq%' P1 P1RAW - 1415053318 54580006 Type = 5458 Mode = 0006 Hex Decimal ASCII 54 = 84 = ' T' 58 = 88 = ' X' Lock = TX 6 Oracle: Enqueues Part I: Intro Information we need to solve locking issues :ĥ. 1.2.7 Three types of TX row contention mode 4 as seen from ASH data:.1.2.6 enq: TX - row lock contention, mode 4, bitmap indexes.1.2.5 enq: TX - row lock contention, mode 4, foreign key.1.2.4 enq: TX - row lock contention, mode 4, unique index.

1.2.3 enq: TX - row lock contention, mode 4.

1.2.2 enq: TX - row lock contention, mode 6.

1.1.1 Information we need to solve locking issues :.

Embarcadero db optimizer