Database Reference
In-Depth Information
--------------------------------------------------
-- MEDIATOR AVG/MIN/MAX --
--------------------------------------------------
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,
DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2', 'FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') 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 MEDIATOR_INSTANCE
WHERE CREATED_TIME >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
GROUP BY COMPONENT_NAME, COMPONENT_STATE
ORDER BY COMPONENT_NAME, COMPONENT_STATE
Although the average execution time, as shown in the following screenshot, of
the DeleteAddress Mediator component is 0.055 seconds, which is exception-
ally low, the maximum time is 20.200 seconds. This may require some investig-
ation if needed. You can further limit the query to a specific Mediator component,
but it must be within the LIKE clause.
Search WWH ::




Custom Search