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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto: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
