Interpreting "enq: UL - contention"

[Hameed, Amir]

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

[Powell, Mark]

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-***@freelists.org [mailto:oracle-l-***@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

[Hameed, Amir]

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-***@freelists.org [mailto:oracle-l-***@freelists.org] 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-***@freelists.org<mailto:oracle-l-***@freelists.org> [mailto:oracle-l-***@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

[Jonathan Lewis]

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-***@freelists.org [oracle-l-***@freelists.org] on behalf of Hameed, Amir [***@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

[Hameed, Amir]

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-***@freelists.org [mailto:oracle-l-***@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
@jloracle
________________________________
From: oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org> [oracle-l-***@freelists.org] on behalf of Hameed, Amir [***@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