RE: Interpreting "enq: UL - contention"

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 19 Nov 2015 19:00:50 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282B1E88_at_EXMBX01.thus.corp>

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
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Hameed, Amir [Amir.Hameed_at_xerox.com] 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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 19 2015 - 20:00:50 CET