Query para Monitoração do Ambiente SOA-SUITE

In a previous article I wrote a query to help verify the instance of the states of a given composite BPEL

In this new Query I am providing will be helping to monitor our SOA–SUITE environment providing COMPOSITE information, PROCESS, COMPONENT TYPE, STATE, QUANTITY (Requests), MAXIMUM TIME (in seconds), MINIMUM TIME (in seconds) and MEAN (in Seconds)

I am below highlighting the name in red to be your defined OWNER installation of your RCU

SELECT   COMPOSITE_NAME COMPOSITE,
         COMPONENT_NAME PROCESSO,
         COMPONENTTYPE AS "TIPO DE COMPONENTE",
         DECODE (STATE, 0,'STATE_INITIATED',
                 1,'STATE_OPEN_RUNNING',
                 2,'STATE_OPEN_SUSPENDED',
                 3,'STATE_OPEN_FAULTED',
                 4,'STATE_CLOSED_PENDING_CANCEL',
                 5,'STATE_CLOSED_COMPLETED',
                 6,'STATE_CLOSED_FAULTED',
                 7,'STATE_CLOSED_CANCELLED',
                 8,'STATE_CLOSED_ABORTED',
                 9,'STATE_CLOSED_STALE',
                 10,'STATE_CLOSED_ROLLED_BACK') AS "ESTADO",
        COUNT ( * ) AS "QUANTIDADE",
        TRUNC (
           MAX(  EXTRACT (DAY FROM (MODIFY_DATE - CREATION_DATE)) * 24 * 60 * 60
               + EXTRACT (HOUR FROM (MODIFY_DATE - CREATION_DATE)) * 60 * 60
               + EXTRACT (MINUTE FROM (MODIFY_DATE - CREATION_DATE)) * 60
               + EXTRACT (SECOND FROM (MODIFY_DATE - CREATION_DATE))), 4) AS "TEMPO MAXIMO (SEG)",
        TRUNC (
           MIN(  EXTRACT (DAY FROM (MODIFY_DATE - CREATION_DATE)) * 24 * 60 * 60
               + EXTRACT (HOUR FROM (MODIFY_DATE - CREATION_DATE)) * 60 * 60
               + EXTRACT (MINUTE FROM (MODIFY_DATE - CREATION_DATE)) * 60
               + EXTRACT (SECOND FROM (MODIFY_DATE - CREATION_DATE))),4 ) AS "TEMPO MINIMMO (SEG)",
        TRUNC ( AVG(  EXTRACT (DAY FROM (MODIFY_DATE - CREATION_DATE)) * 24 * 60 * 60
               + EXTRACT (HOUR FROM (MODIFY_DATE - CREATION_DATE)) * 60 * 60
               + EXTRACT (MINUTE FROM (MODIFY_DATE - CREATION_DATE)) * 60
               + EXTRACT (SECOND FROM (MODIFY_DATE - CREATION_DATE))), 4) AS "MEDIA (SEG)"
FROM   OWNER_SOAINFRA.CUBE_INSTANCE
WHERE   TRUNC (CREATION_DATE) = TRUNC (SYSDATE)
GROUP BY   COMPOSITE_NAME, COMPONENT_NAME, COMPONENTTYPE, STATE 
ORDER BY 1, 2 DESC;

Select this was provided by my good friend Helton Hideharu Higute

Hugs and see you next

/:-D