enq: WL – contention – joviano silva

I received a complaint about a database slowdown, so I generated an ADDM report, which identified the event ‘enq: WL – contention’ as a contributing factor.

The ADDM report:

Finding 1: Unusual "Other" Wait Event
Impact is 24.98 active sessions, 93.85% of total activity.
----------------------------------------------------------
Wait event "enq: WL - contention" in wait class "Other" was consuming
significant database time.

I identified the sessions with this event with the following sql:

select * from v$session where event='enq: WL - contention'

In my specific case were 30 RMAN concurrent sessions.

Find the blocking sessions:

SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
   || ' ( SID=' || s1.sid || ','|| s1.serial#||s1.status||  '  )  is blocking '
   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
   WHERE s1.sid=l1.sid AND
    s1.inst_id=l1.inst_id AND
    s2.sid=l2.sid AND
    s2.inst_id=l2.inst_id AND
    l1.BLOCK=1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l2.id2 = l2.id2 ;

  Instance 1 @lxdbaseodb01d.cmvm.pt ( SID=577,16390ACTIVE  )  is blocking SYS@lxdbaseodb01d.cmvm.pt ( SID=1538 )

The blocker session was identified above.

The next step was to terminate all 30 concurrent RMAN sessions before addressing the blocker session.

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where event='enq: WL - contention'

The next step was to terminate the blocker session, which was the archiver process, and force the archival process to restart.

The result on alert log:

Archived Log entry 124852 added for T-1.S-155912 ID 0xc4a92383 LAD:1
2024-12-18T16:13:10.613942+00:00
ARCH: Detected ARCH process failure
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
2024-12-18T16:13:10.755301+00:00
ARC0 started with pid=111, OS id=20885
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
2024-12-18T16:13:10.765831+00:00
ARC0: Becoming a 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH

Finally, I ran the RMAN process to back up the archive logs and verified that the issue was resolved.