AWR Report Shows Lock Contention with DBMS_LOCK

There may be some concern about lock contention when seeing DBMS_LOCK in the Automated Workload Repository (AWR) Report. This wait event is the garbage collector session trying to place a hold on a resource that another garbage collector session from another database has already locked. Thus, only the current garbage collector session waits. The wait for the garbage collector process does not block other processes, except other garbage collectors.

For example, the following shows a contention event in the AWR report:

AWR 
 
Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: UL - contention                  2,388       6,997   2930   72.0 Application
 

In addition, only a small amount of CPU time is used for the garbage collector, as shown in the "SQL ordered by CPU Time" section in the PERF AWR report.

SQL ordered by CPU Time              DB/Inst: REM0LNX/REM  Snaps: 14976-14977
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
 
    CPU      Elapsed                  CPU per  % Total
  Time (s)   Time (s)  Executions     Exec (s) DB Time    SQL Id
---------- ---------- ------------ ----------- ------- -------------
         0      3,508          120        0.00    36.1 0mt5pk2501gph
Module: [e-mail] (TNS V1-V3)
DECLARE v_lockHandle VARCHAR2(200); BEGIN dbms_lock.allocate_unique(
'ORATT$ORA_GC1_CACHEADMIN', v_lockHandle); :retval := dbms_lock.request(
v_lockHandle, dbms_lock.x_mode, 30, FALSE); END;
         0      3,499          120        0.00    36.0 bb07h2a1v817x
Module: [e-mail] (TNS V1-V3)
DECLARE v_lockHandle VARCHAR2(200); BEGIN dbms_lock.allocate_unique(
'ORATT$ORA_DDSMONITOR1_CACHEADMIN', v_lockHandle); :retval := 
dbms_lock.request(v_lockHandle, dbms_lock.x_mode, 30, FALSE); END;