Database Reference
In-Depth Information
4.
Clicking on the service engine name takes you to the service engine home
dashboard, allowing you to drill down to statistics, instances, faults, deployed
composites, and recovery.
Monitoring using SQL queries
The standard information obtained from Oracle Enterprise Manager Fusion Mid-
dleware Control might not be sufficient and adequate for fine grained monit-
oring. 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 suc-
cess/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_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,
