ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F02571BACAC64B1A9FAE502607A2336FY select x.queue_table, x.corrid correlation_id, decode(x.state,0,'Ready',1,'Waiting',2,'Processed',3,'Expired') state, x.message_count, xxen_util.client_time(x.oldest_message) oldest_message, xxen_util.time((sysdate-x.oldest_message)*86400) oldest_message_age, xxen_util.client_time(x.newest_message) newest_message from ( select 'WF_DEFERRED' queue_table, wd.corrid, wd.state, count(*) message_count, cast(min(wd.enq_time) as date) oldest_message, cast(max(wd.enq_time) as date) newest_message from wf_deferred wd group by wd.corrid, wd.state union all select 'WF_DEFERRED_TABLE_M' queue_table, wdtm.corrid, wdtm.state, count(*) message_count, cast(min(wdtm.enq_time) as date) oldest_message, cast(max(wdtm.enq_time) as date) newest_message from wf_deferred_table_m wdtm group by wdtm.corrid, wdtm.state union all select 'WF_ERROR' queue_table, we.corrid, we.state, count(*) message_count, cast(min(we.enq_time) as date) oldest_message, cast(max(we.enq_time) as date) newest_message from wf_error we group by we.corrid, we.state union all select 'WF_JAVA_DEFERRED' queue_table, wjd.corrid, wjd.state, count(*) message_count, cast(min(wjd.enq_time) as date) oldest_message, cast(max(wjd.enq_time) as date) newest_message from wf_java_deferred wjd group by wjd.corrid, wjd.state union all select 'WF_JAVA_ERROR' queue_table, wje.corrid, wje.state, count(*) message_count, cast(min(wje.enq_time) as date) oldest_message, cast(max(wje.enq_time) as date) newest_message from wf_java_error wje group by wje.corrid, wje.state union all select 'WF_NOTIFICATION_OUT' queue_table, wno.corrid, wno.state, count(*) message_count, cast(min(wno.enq_time) as date) oldest_message, cast(max(wno.enq_time) as date) newest_message from wf_notification_out wno group by wno.corrid, wno.state union all select 'WF_NOTIFICATION_IN' queue_table, wni.corrid, wni.state, count(*) message_count, cast(min(wni.enq_time) as date) oldest_message, cast(max(wni.enq_time) as date) newest_message from wf_notification_in wni group by wni.corrid, wni.state union all select 'WF_INBOUND_TABLE' queue_table, wit.corrid, wit.state, count(*) message_count, cast(min(wit.enq_time) as date) oldest_message, cast(max(wit.enq_time) as date) newest_message from wf_inbound_table wit group by wit.corrid, wit.state union all select 'WF_OUTBOUND_TABLE' queue_table, wot.corrid, wot.state, count(*) message_count, cast(min(wot.enq_time) as date) oldest_message, cast(max(wot.enq_time) as date) newest_message from wf_outbound_table wot group by wot.corrid, wot.state ) x where 1=1 order by x.message_count desc US WF Queue Backlog Number of messages per Workflow Business Event System queue table, correlation id and state, with the age of the oldest message. A large number of Ready messages with an old Oldest Message shows that the agent listener, notification mailer or Workflow Background Process serving the queue is down or cannot keep up. The correlation id contains the business event or notification message name, and for WF_DEFERRED_TABLE_M the item type processed by the Workflow Background Process. Enginatics 1=1 1 10 1=1 x.state<>2 LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Exclude Processed Exclude messages that were processed and are only retained in the queue 2 20 1=1 x.message_count>=:message_count_from Number US Message Count From 3 30 1=1 x.oldest_message<=sysdate-:oldest_message_hours/24 Number US Oldest Message Hours From Show only queues whose oldest message has been waiting at least this number of hours 4 40 1=1 x.queue_table=:queue_table LOV custom Y select 'WF_DEFERRED' value, 'Deferred business event subscriptions' description from dual union all select 'WF_DEFERRED_TABLE_M' value, 'Deferred workflow activities' description from dual union all select 'WF_ERROR' value, 'Business event errors' description from dual union all select 'WF_JAVA_DEFERRED' value, 'Deferred Java business event subscriptions' description from dual union all select 'WF_JAVA_ERROR' value, 'Java business event errors' description from dual union all select 'WF_NOTIFICATION_OUT' value, 'Outbound notifications for the mailer' description from dual union all select 'WF_NOTIFICATION_IN' value, 'Inbound notification responses' description from dual union all select 'WF_INBOUND_TABLE' value, 'Inbound business events' description from dual union all select 'WF_OUTBOUND_TABLE' value, 'Outbound business events' description from dual US Queue Table