Oracle SOA Suite 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:
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:
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>>%';
No comments:
Post a Comment