Low Performance Processing Messages in WF_DEFERRED Queue (Doc ID 334348.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

Some workflow item type processes do not seem to get processed fast enough from the WF_DEFERRED queue.
Other item types are being processed at much higher rate.

This query will display the volume of messages in WF_DEFERRED classified by event, status, enqueue time, dequeue time and inform the number of hours the messages have been waiting to be processed.

col event format a35
col enq_time format a17
col deq_time format a17
col elapsed_hours format 9999
col state format a12
set linesize 120
select a.user_data.event_name event
    , to_char (trunc(a.enq_time, 'HH24'), 'DD-MON-YYYY HH24:MI') enq_time
    , to_char (trunc(a.deq_time, 'HH24'), 'DD-MON-YYYY HH24:MI') deq_time
    , to_char ((nvl (trunc(a.deq_time, 'HH24'), trunc (sysdate, 'HH24'))
       - trunc(a.enq_time, 'HH24')) * 24, '999999.99') Elapsed_Hours
    , a.msg_state status
    ,count(*)
from applsys.aq$wf_deferred a
Group by a.user_data.event_name
    , to_char (trunc(a.enq_time, 'HH24'), 'DD-MON-YYYY HH24:MI')
    , to_char (trunc(a.deq_time, 'HH24'), 'DD-MON-YYYY HH24:MI')
    , to_char ((nvl (trunc(a.deq_time, 'HH24'), trunc (sysdate, 'HH24'))
        - trunc(a.enq_time, 'HH24')) * 24 , '999999.99')
    , a.msg_state
order by 1, 2;

 

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.