Thanks Mark. I am trying to find a way to enable DB trace for the ODI (Oracle
Data Integrator) Agent to get a clear picture of how it is using this command.
Since this is the out-of-the-box ODI behavior, we do not know how it is using
the lock.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Powell, Mark
Sent: Thursday, November 19, 2015 12:36 PM
To: oracle-l
Subject: RE: Interpreting "enq: UL - contention"
I am not familiar with Oracle ODI however we use user locks fairly regularly.
Normally User Locks are used to single thread tasks that are not designed to
run concurrently in order to protect the data. You have a choice when you
request the user lock to either wait for it or if it is in use to limit the
wait then terminate. I think you need to find the process (program?) that uses
this specific Lock-ID and see if you can determine how it uses the lock and if
two such sessions should run at one time and why multiple sessions were running
at one time.
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hameed, Amir
Sent: Thursday, November 19, 2015 11:53 AM
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
