Oracle SOA Suite 11.1.1.6: Monitoring SOA Composite Behavior with SQL Queries

Oracle SOA Suite 11.1.1.6: Monitoring SOA Composite Behavior with SQL Queries


The standard information obtained from Oracle Enterprise Manager Fusion Middleware Control might not be sufficient and adequate for fine grained monitoring. By querying some core product tables in the [PREFIX]_SOAINFRA schema such as the COMPOSITE_INSTANCE, CUBE_INSTANCE, and MEDIATOR_INSTANCE tables, you can get detailed metrics that include success/failure counts, composite instance performance, and durations of invokes as well. Here, we provide two main
queries to obtain performance metrics on BPEL processes and Mediator services, specifically the duration of time that each component took. Though Oracle typically does not recommend querying the product tables directly (since the structure of the tables may change after a patch or upgrade), note that these queries below run fine on Oracle SOA Suite 11g PS3 (11.1.1.4), PS4 (11.1.1.5), and PS5 (11.1.1.6).


The following query outputs a list of all BPEL component instances, their state,
average, minimum, and maximum durations, as well as counts:

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_DATECREATION_
DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),18,4))),'999990.000') AVG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),18,4))),'999990.000') MIN,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),18,4))),'999990.000') MAX,
COUNT(1) COUNT
FROM CUBE_INSTANCE
GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE
ORDER BY COMPONENT_NAME, STATE


The following query displays a list of all Mediator component instances, their state,
average, minimum, and maximum durations, as well as counts:

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_TIMECREATED_
TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),18,4))),'999990.000') AVG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),18,4))),'999990.000') MIN,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),18,4))),'999990.000') MAX,
COUNT(1) COUNT
FROM MEDIATOR_INSTANCE
GROUP BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE
ORDER BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE