SOA INFRA SQL Queries

All of us working in SOA are familiar with Enterprise Manager(EM) console where we can view the composites deployed, check the instances of execution and also flow of instances. This console helps us to analyze any issues that occur in SOA.

But sometimes, when there is huge load leading to multiple instances, it becomes very difficult to check in the EM console. This is when we can make use of the SOA Infra tables where the data shown in EM console is actually saved. You can also query these tables to find the performance of a flow like the counts, duration etc.

Here are few queries which were quite helpful for me and hope they would be helpful for you too.
Most of these queries are based on instance number, composite name, time and composite title as this is the handy information most of us would have.

Query to find the count of the instances of particular Composites created on particular time   

SELECT * FROM (SELECT COUNT(COMPOSITE_NAME) COUNT,COMPOSITE_NAME FROM CUBE_INSTANCE WHERE CREATION_DATE BETWEEN TO_DATE('09/20/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2016 23:00:00','MM/DD/YYYY HH24:MI:SS')  AND COMPOSITE_NAME IN (‘<<CompositeName1>>’, ‘<<CompositeName2>>’) GROUP BY COMPOSITE_NAME ) ORDER BY COUNT DESC

Above query will give the count of both success and failed instances. In order to filter successful vs failed, add STATE clause as follows.

SELECT * FROM (SELECT COUNT(COMPOSITE_NAME) COUNT,COMPOSITE_NAME FROM CUBE_INSTANCE WHERE CREATION_DATE BETWEEN TO_DATE('09/20/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2016 23:00:00','MM/DD/YYYY HH24:MI:SS') AND COMPOSITE_NAME IN (‘<<CompositeName1>>’, ‘<<CompositeName2>>’) AND STATE=5 GROUP BY COMPOSITE_NAME ) ORDER BY COUNT DESC

State = 5 is successful instance

https://blogs.oracle.com/ateamsoab2b/entry/list_of_all_states_from

Query to find the execution time of BPEL instances based on composite name   

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('09/20/2016 00:58:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2016 23:59:59','MM/DD/YYYY HH24:MI:SS') AND COMPOSITE_NAME IN ('<<CompositeName>>')) ORDER BY COMPOSITE_NAME, DURATION_IN_SECOND DESC

Query to find mediator instances based on instance number or title 

SELECT * FROM MEDIATOR_INSTANCE A, COMPOSITE_INSTANCE B WHERE A.COMPOSITE_INSTANCE_ID = B.ID AND (A.COMPOSITE_INSTANCE_ID = '<<InstanceId>>' OR B.TITLE LIKE '<<Title>>')

Query to find/track receive or callback activities on instance number 

SELECT * FROM DLV_MESSAGE WHERE CIKEY IN (SELECT CIKEY FROM CUBE_INSTANCE WHERE CMPST_ID='<<InstanceNumber>>')

Query to find payload of a composite instance based on instance number

SELECT A.DOCUMENT  FROM XML_DOCUMENT A,INSTANCE_PAYLOAD B,COMPOSITE_INSTANCE C WHERE A.DOCUMENT_ID = B.PAYLOAD_KEY AND B.INSTANCE_ID = C.ID AND B.INSTANCE_TYPE='COMPOSITE' AND A.DOCUMENT_TYPE = 2 AND B.INSTANCE_ID = <<InstanceNumber>>;