Diagnose SOA composite performance using sql queries

Even though the Enterprise manager console along with the weblogic diagnostic framework provides different ways of monitoring the Oracle SOA suite the below queris can come in handy for analyzing the performance of SOA composite applications and start looking into the composite design issues.

The following query gets the details from CUBE_INSTANCE table for the BPEL instances and provide informations like instances, their state,average, minimum & maximum durations

in seconds and the total count after the previous purge or deployment.

SELECT DOMAIN_NAME PARTITION,COMPONENT_NAME,

DECODE(STATE,'1','RUNNING','5','COMPLETED','6',

'FAULTED','9','STALE') 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 <DB_PREFIX>.CUBE_INSTANCE where COMPONENT_NAME like 'XX%'

GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE

ORDER BY COMPONENT_NAME, STATE

The following query gets the details from MEDIATOR_INSTANCE table for the mediator instances and provide informations like instances, their state,average, minimum & maximum

durations in seconds and the total count.

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','2',

'FAULTED','3','ABORTED','4','RECOVERY

NEEDED','8','RUNNING','16','STALE') 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 <DB_PREFIX>._SOAINFRA.MEDIATOR_INSTANCE WHERE COMPONENT_NAME like 'XYZ%'

GROUP BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

ORDER BY STATE ,COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

Below sql statement can be run to query the virtual table v$sql in Oracle to find the recently executed top slowest  queries and needs tuning.

SELECT * FROM (SELECT sql_fulltext, sql_id,child_number,disk_reads,executions,first_load_time,last_load_time FROM v$sql ORDER BY elapsed_time DESC)
WHERE ROWNUM < 15

The above queries are seen working fine upto PS6(11.1.1.7).