Workflow performance issue

[raja]

Hi Erman

After the go live to OCI from on premise, we can see that the dequeuing of messages from wf_deferred queue are taking much time and thus there is a delay in sending notifications to end users. Could you please assist on how we can improve the performance here so that dequeuing completes faster.

thanks
Raja

[ErmanArslansOracleBlog]

Hi Raja,

Interesting case..

It is a long story to write the throubleshooting instructions here.

Following might help:

*Creating specific agent listeners to process events or groupings of events
*Create additional generic agent listeners for WF_DEFERRED agent
*In case, you can't fix the performance problem no matter what you do, then you can use the folllowing to process the messages in small batches

sqlplus apps/apps

APPS>
  set serveroutput on size 1000000;
      declare
          msgCount integer := 10000;
          errCount integer := 10000;
begin
    wf_event.listen(p_agent_name => 'WF_DEFERRED',
                           p_correlation => '<correlation_to_processed>',
                           p_message_count => msgCount,
                           p_max_error_count => errCount);
dbms_output.put_line('MESAGES: '||to_char(msgCount)||' '||
                                  'ERRORED: '||to_char(errCount));
end;

Please go through the MOS document named "Troubleshooting WF_DEFERRED Agent Listeners Performance (Doc ID 468650.1)"


Update me accordingly..