RE: Interpreting "enq: UL - contention"

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 19 Nov 2015 19:00:50 +0000



If the code has used the dbms_lock.allocate_unique to turn an
application-specific lock name into a lock id then you could query
sys.dbms_allocated_locks where lockid = {your p2 value} to find the
application-supplied NAME of the lock; this might give you some idea of the
purpose of the lock.

You could also query v$sql for all occurrences of code calling DBMS_LOCK and
then enable tracing on all those SQL statements by SQL_ID - this would give you
a sequence of calls to acquire, convert and release the lock, and that might be
helpful.

I suspect that it's probably a lock that is trying to serialise some activity,
or ensure that only one copy of a process is allowed to run.



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: 19 November 2015 16:52
To: oracle-l
Subject: Interpreting "enq: UL - contention"

Hi folks,
When I query DBA_HIST_ACTIVE_SESS_HISTORY for a specific SQL_ID, I see “-1” for
the CURRENT_OBJECT# field. From the AWR report, the SQL_ID belongs to the
following statement:
select DBMS_LOCK.REQUEST(:1 ,:2 ) from dual ;

An abbreviated output from the DBA_HIST_ACTIVE_SESS_HISTORY for the SQL_ID is
shown below:
select
, sql_id
, session_type
, event
, p1
, mod(p1,16) as "Mode"
, p2
, p3
, wait_time
, session_state sess_state
, time_waited
, current_obj#
, current_file#
, current_block#
, current_row#
, blocking_session blocking_sess
from
dba_hist_active_sess_history
where
sql_id='a3gj4pfsuqdrm'
;

SQL_ID SESSION_TY EVENT P1 Mode
P2 P3 WAIT_TIME SESS_ST TIME_WAITED CURRENT_OBJ# CURRENT_FILE#
CURRENT_BLOCK# CURRENT_ROW# BLOCKING_SESS
------------- ---------- ------------------------------ ----------- -----
---------- ----- ---------- ------- ----------- ------------ -------------
-------------- ------------ -------------
a3gj4pfsuqdrm FOREGROUND enq: UL - contention 1431044102 6
1073741928 0 0 WAITING 0 -1 0
0 0 293
a3gj4pfsuqdrm FOREGROUND enq: UL - contention 1431044102 6
1073741928 0 0 WAITING 0 -1 0
0 0 293

The statement seems to be issued by the Oracle ODI code. This database is
hosting the Oracle Business Intelligence Applications (OBIA) objects. Since
this is a user lock, how should this be interpreted in terms of what is it
trying to lock as the object# mentioned is “-1”?

Thanks,
Amir

Other related posts: