Oracle SOA 11g - Important SQL queries to get the details about composites from SOAINFRA schema

Query to find the execution time of the BPEL instances:- SELECT * FROM (SELECT COMPOSITE_NAME COMPOSITENAME,A.CMPST_ID COMPOSITE_INSTANCE_ID,creation_date BEGIN_TIME,modify_date END_TIME ,(extract(day from modify_date - creation_date)*86400+ extract(hour from modify_date - creation_date)*3600+ extract(minute from modify_date - creation_date)*60+ extract(second from modify_date - creation_date)) duration_in_second,A.* FROM cube_instance A where state = 5 and CREATION_date BETWEEN TO_DATE('19/01/2012 00:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('19/01/2012 23:59:59','dd/mm/yyyy HH24:MI:SS') AND COMPOSITE_NAME IN (<<Composite Name>>)) ORDER BY COMPOSITE_NAME,duration_in_second DESC Query to find the execution time of the Mediator instances:- SELECT * FROM (SELECT COMPONENT_NAME COMPONENTNAME,A.COMPOSITE_INSTANCE_ID INSTANCE_ID,created_time BEGIN_TIME,updated_time END_TIME ,(extract(day from updated_time - created_time)*86400+ extract(hour from updated_time - created_time)*3600+ extract(minute from updated_time - created_time)*60+ extract(second from updated_time - created_time)) duration_in_second,A.* FROM mediator_instance A where CREATED_TIME BETWEEN TO_DATE('30/01/2012 00:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('31/01/2012 23:59:59','dd/mm/yyyy HH24:MI:SS') AND A.COMPONENT_NAME like '%<<Meditor Composite Name>>%') ORDER BY COMPONENT_NAME,duration_in_second DESC Query to find the count of Composite instances created on particular time:- BPEL instances:- select * from (select count(COMPONENT_NAME) count,COMPONENT_NAME from CUBE_INSTANCE where CREATION_DATE BETWEEN TO_DATE('30/01/2012 12:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('31/01/2012 23:00:00','dd/mm/yyyy HH24:MI:SS') /*and component_state<5*/ group by COMPONENT_NAME ) order by count desc Mediator Instances:- select * from (select count(COMPONENT_NAME) count,COMPONENT_NAME from MEDIATOR_INSTANCE where CREATED_TIME BETWEEN TO_DATE('30/01/2012 12:58:00','dd/mm/yyyy HH24:MI:SS')AND TO_DATE('31/01/2012 23:00:00','dd/mm/yyyy HH24:MI:SS') group by COMPONENT_NAME ) order by count desc Getting the count of instances in different state for the BPEL Components:- select composite_name,count(*),DECODE(cube_instance.STATE, 0, 'STATE_INITIATED', 1, 'STATE_OPEN_RUNNING', 2, 'STATE_OPEN_SUSPENDED', 3, 'STATE_OPEN_FAULTED', 4, 'STATE_CLOSED_PENDING_CANCEL', 5, 'STATE_CLOSED_COMPLETED', 6, 'STATE_CLOSED_FAULTED', 7, 'STATE_CLOSED_CANCELLED', 8, 'STATE_CLOSED_ABORTED', 9, 'STATE_CLOSED_STALE', 10,'STATE_CLOSED_ROLLED_BACK','unknown') state from CUBE_INSTANCE group by state,composite_name order by composite_name; Getting the count of instances in different state for the Mediator Components:- select COMPONENT_NAME,count(*),DECODE(mediator_instance.COMPONENT_STATE, 0, 'STATE_CLOSED_COMPLETED', 2, 'STATE_OPEN_FAULTED', 4,'STATE_RECOVERY_REQUIRED', 8, 'STATE_OPEN_RUNNING', 16, 'STATE_CLOSED_STALE', 48, 'STATE_TERMINATED', 50, 'STATE_TERMINATED','unknown') STATE from mediator_instance group by component_state,COMPONENT_NAME order by COMPONENT_NAME; Querying the call back message status from dlv_message table:- Error to deliver the call back to the receive activity:- select count(*) from dlv_message where dlv_type = 2 and state=1 Callback arrived but did not get resolved:- select count(*) from dlv_message where dlv_type = 2 and state=0 Callback successfully delivered to receive:- select count(*) from dlv_message where dlv_type = 2 and state=2 Querying recoverable invoke messages :- select * from dlv_message where dlv_type = 1 and state = 0; To check for rejected messages for a specific composite: select count(*) from rejected_message where composite_dn like '%<<Composite Name>>%';