RE: Interpreting "enq: UL - contention"

From: Powell, Mark <mark.powell2_at_hpe.com>
Date: Thu, 19 Nov 2015 17:36:15 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD13068B0A9_at_G9W0741.americas.hpqcorp.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

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

Received on Thu Nov 19 2015 - 18:36:15 CET