Incredibly Useful SQL Queries for Oracle SOA Suite 11g

These are the collection of the incredibly useful SQL queries for Oracle SOA Suite 11g. Parameters highlighted are customizable.

All SQL should should be executed as the <PREFIX>_SOAINFRA user.



Component avg/min/max performance

SELECT 'Mediator' type,
       SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       DECODE(component_state, '0', 'Completed', '16', 'Stale', '2', 'Faulted', '4', 'Recovery Needed', '8', 'Running') state,
       TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4))),'999990.000') Avg,
       TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4))),'999990.000') Min,
       TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4))),'999990.000') Max,
       COUNT(1) count
FROM   mediator_instance
WHERE  created_time >= TO_DATE('2015-04-22 00:00', 'YYYY-MM-DD HH24:MI')
--AND    created_time <= TO_DATE('2013-07-03 23:59', 'YYYY-MM-DD HH24:MI')
GROUP BY component_name, component_state
UNION
SELECT 'BPEL' type,
       domain_name partition,
       component_name component,
       DECODE(state,'5','Completed','9','Stale','10','Faulted') State,
       TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') Avg,
       TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') Min,
       TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') Max,
       COUNT(1) count
FROM   cube_instance
WHERE  creation_date >= TO_DATE('2015-04-22 00:00', 'YYYY-MM-DD HH24:MI')
--AND    creation_date <= TO_DATE('2013-07-03 23:59', 'YYYY-MM-DD HH24:MI')
GROUP BY domain_name, component_name, state
ORDER BY 2, 3

Mediator avg/min/max performance

SELECT SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       source_action_name action,
       DECODE(component_state, '0', 'Completed', '16', 'Stale', '2', 'Faulted', '4', 'Recovery Needed', '8', 'Running') state,
       TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4))),'999990.000') Avg,
       TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4))),'999990.000') Min,
       TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4))),'999990.000') Max,
       COUNT(1) count
FROM   mediator_instance
-- Last three hours
WHERE  created_time >= sysdate - 0.125
--AND    COMPONENT_NAME LIKE '%%'
GROUP BY component_name, source_action_name, component_state
ORDER BY component_name, source_action_name, component_state












BPEL avg/min/max performance

SELECT domain_name,
       component_name,
       DECODE(state,'5','Complete','9','Stale','10','Faulted') State,
       TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') Avg,
       TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') Min,
       TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') Max,
       COUNT(1) count
FROM   cube_instance
-- Last 3 hours
WHERE  CREATION_DATE >= sysdate - 0.125
--AND    COMPONENT_NAME LIKE '%%'
--AND    COMPOSITE_NAME LIKE '%%'
GROUP BY domain_name, component_name, state
ORDER BY component_name, state



Composite state count

SELECT SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1) partition,
       SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1) component,
       DECODE(state,'1','Complete','0','Running','3','Faulted','32','Unknown','5','Recovery Needed','7','Recovery Needed','64','Stale') State,
       count(1) Count
FROM   composite_instance 
WHERE  composite_dn LIKE '%%'
GROUP BY SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1),SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1),state
ORDER BY SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1),SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1),state



Composite instances and state

SELECT id Instance,
       SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1) partition,
       SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1) component,
       DECODE(state,'1','Complete','0','Running','3','Faulted','32','Unknown','5','Recovery Needed','7','Recovery Needed','64','Stale') State
FROM   composite_instance 
WHERE  composite_dn LIKE '%%'
--AND    state = 0 -- 0 = Running 1 = Completed 3 = Faulted 5&7 = Recovery Needed 32 = Unknown 64 = Stale
ORDER BY SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1),SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1), state

Mediator state counts

SELECT n.partition,
       n.component,
       nvl(c.completed,0) Completed,
       nvl(f.failed,0) Failed,
       nvl(rn.Recovery_needed,0) Recovery_Needed,
       nvl(r.running,0) Running,
       nvl(s.stale,0) Stale,
       nvl(t.total,0) Total
FROM
(SELECT Unique SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component
 FROM mediator_instance) n,
(Select SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       count(1) Completed
       from mediator_instance
       WHERE component_state = 0
       AND created_time >= sysdate - 0.125
       GROUP BY SUBSTR(component_name, 1, INSTR(component_name,'/')-1), SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1)) c ,
(Select SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       count(1) Failed
       from mediator_instance
       WHERE component_state = 2
       AND created_time >= sysdate - 0.125
       GROUP BY SUBSTR(component_name, 1, INSTR(component_name,'/')-1), SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1)) f ,
(Select SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       count(1) Recovery_Needed
       from mediator_instance
       WHERE component_state = 4
       AND created_time >= sysdate - 0.125
       GROUP BY SUBSTR(component_name, 1, INSTR(component_name,'/')-1), SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1)) rn ,
(Select SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       count(1) Running
       from mediator_instance
       WHERE component_state = 8
       AND created_time >= sysdate - 0.125
       GROUP BY SUBSTR(component_name, 1, INSTR(component_name,'/')-1), SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1)) r ,
(Select SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       count(1) Stale
       from mediator_instance
       WHERE component_state = 16
       AND created_time >= sysdate - 0.125
       GROUP BY SUBSTR(component_name, 1, INSTR(component_name,'/')-1), SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1)) s ,
(Select SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       count(1) Total
       from mediator_instance
       WHERE created_time >= sysdate - 0.125
       GROUP BY SUBSTR(component_name, 1, INSTR(component_name,'/')-1), SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1)) t
where n.partition = c.partition(+)
AND   n.component = c.component(+)
AND   n.partition = f.partition(+)
AND   n.component = f.component(+)
AND   n.partition = rn.partition(+)
AND   n.component = rn.component(+)
AND   n.partition = r.partition(+)
AND   n.component = r.component(+)
AND   n.partition = s.partition(+)
AND   n.component = s.component(+)
AND   n.partition = t.partition(+)
AND   n.component = t.component(+)
Order by --failed desc,
         --completed desc,
         partition,
         component;

Faulted Mediator instances

SELECT composite_instance_id Instance_ID,
       SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1) partition,
       SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1) component,
       error_message
FROM COMPOSITE_INSTANCE_FAULT where composite_instance_id in
(SELECT composite_instance_id 
 FROM mediator_instance 
 WHERE component_state = 2
 AND created_time >= sysdate - 0.125)

Faulted BPEL instances

 SELECT composite_instance_id Instance_ID,
       SUBSTR(composite_dn, 1, INSTR(composite_dn,'/')-1) partition,
       SUBSTR(composite_dn, INSTR(composite_dn,'/')+1, INSTR(composite_dn,'!')-INSTR(composite_dn,'/')-1) component,
       error_message
FROM COMPOSITE_INSTANCE_FAULT where composite_instance_id in
(SELECT cikey
 FROM cube_instance
 WHERE state = 10
 AND   creation_date >= sysdate - 0.125)

Mediator end-to-end Flows that took over 60 seconds

SELECT SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       source_action_name action,
       DECODE(component_state, '0', 'Completed', '16', 'Stale', '2', 'Faulted', '4', 'Recovery Needed', '8', 'Running') state,
       COUNT(1) count,
       '60' seconds
FROM   mediator_instance
WHERE  TO_CHAR(composite_creation_date, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(sysdate-0.125, 'YYYY-MM-DD HH24:MI')
AND    TO_CHAR(composite_creation_date, 'YYYY-MM-DD HH24:MI') <= TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI')
AND    parent_id IS NULL
AND    TO_NUMBER(TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-composite_creation_date),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-composite_creation_date),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-composite_creation_date),18,4)),'999990.0')) > 60
GROUP BY component_name, source_action_name, component_state
ORDER BY component_name, source_action_name

Duration of time for Mediator reference calls

SELECT composite_instance_id,
       TO_CHAR(composite_creation_date, 'YYYY-MM-DD HH24:MI') creation_date,
       -- SUBSTR(component_name, 1, INSTR(component_name,'/')-1) partition,
       SUBSTR(component_name, INSTR(component_name,'/')+1, INSTR(component_name,'!')-INSTR(component_name,'/')-1) component,
       -- source_action_name action,
       DECODE(component_state, '0', 'Completed', '16', 'Stale', '2', 'Faulted', '4', 'Recovery Needed', '8', 'Running') state,
       TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-created_time),18,4)),'999990.000') component_duration,
       TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(updated_time-composite_creation_date),12,2))*60*60) + (TO_NUMBER(SUBSTR(TO_CHAR(updated_time-composite_creation_date),15,2))*60) + TO_NUMBER(SUBSTR(TO_CHAR(updated_time-composite_creation_date),18,4)),'999990.000') composite_duration
FROM   mediator_instance
WHERE  TO_CHAR(composite_creation_date, 'YYYY-MM-DD HH24:MI') >= '2015-04-22 09:00'
--AND    parent_id IS NULL
AND    component_name LIKE '%%'
AND    source_action_name = '%%'
ORDER BY creation_date, component_name, source_action_name, component_state

Applicable Versions:

• Oracle SOA Suite 11g