RE: Interpreting "enq: UL - contention"

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 19 Nov 2015 19:30:27 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED1FE5DBE9_at_USA7109MB012.na.xerox.net>

Thanks Jonathan, it helped in identifying where the lock was coming from.

select * from dbms_lock_allocated where LOCKID=1073741928 ;

NAME                                                                                 LOCKID EXPIRATIO
-------------------------------------------------------------------------------- ---------- ---------
oracle_odi_oracle.odi.runtime.agent.loadplan.LoadPlanInstance_10510              1073741928 27-NOV-15

This is indeed coming from the ODI code and is part of the load plan which is what is running slow and we are trying to figure out the reason behind it.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, November 19, 2015 2:01 PM To: oracle-l
Subject: RE: Interpreting "enq: UL - contention"

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<mailto: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:30:27 CET