Database Reference
In-Depth Information
Poor execution times of BPEL, BPMN, and Mediator compon-
ents may not be due to slowness of the infrastructure or code,
but could be due to poor response times from the invocation
of external services or resources.
These queries come in very handy, as they are simple to use and quickly retrieve
good, aggregated information on your overall component instance performance.
Duration of single component instances
In other cases, the performance may be isolated to a single transaction, which
could be related to one or more composite and/or component. Regardless, you
will have to start somewhere and have some general idea of the composite that
is reportedly responding poorly.
The following SQL query lists all BPEL and BPMN instances within the last 24
hours (see
sysdate-1
in the
WHERE
clause) that have completed in over 10
seconds (see
10
in the
WHERE
clause):
--------------------------------------------------
-- BPEL/BPMN COMPONENT INSTANCE DURATION TIMES --
--------------------------------------------------
SELECT CMPST_ID,
TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,
COMPONENT_NAME,
COMPONENTTYPE,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,
TO_CHAR((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') DURATION
FROM CUBE_INSTANCE
WHERE TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(
SYSDATE-1
,'YYYY-MM-DD HH24:MI')
AND (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)) >
10
