Disable retention on Workflow Queues

Ahmed Alomari, the ex-head of Oracle E-Business Suite performance group suggested in his famous presentation on performance tuning of Apps to disable retention of workflow queues.

SQL> select name,retention
from dba_queues
where owner='APPLSYS' and retention != '0';
SQL> /

NAME RETENTION
----------------------------------------
WF_IN 604800
WF_OUT 604800
WF_DEFERRED 86400
WF_REPLAY_IN 604800
WF_REPLAY_OUT 604800
WF_NOTIFICATION_IN 86400
WF_NOTIFICATION_OUT 86400
WF_JAVA_DEFERRED 86400

8 rows selected.


SQL> exec DBMS_AQADM.ALTER_QUEUE(queue_name=>:b1,retention_time=>0);

A search on metalink for the string DBMS_AQADM.ALTER_QAUEUE returns three hits 273848.1, 468650.1, 358262.1. All of them are related to workflow performance

Oracle Workflow accesses several tables that can grow quite large. Workflow leaves lot of runtime data in certain workflow tables which needs to be purged on a periodic basis. Since a number of modules make use of workflow, the amount of obsolete runtime data that is retained in these tables is huge. The size of these tables and indexes can adversely affect performance.

Will update more on this.