Pages

Wednesday, February 1, 2012

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


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:

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>>%';







No comments:

Post a Comment