Query to find BPEL execution time

I had a scenario where business event is captured by a mediator in one of the composites and routes it to several BPEL processes in a sequence to complete particular business flow. I had to know the time taken by each of these BPELs in my end-to-end flow. Instead of going through each of the BPEL instance in EM console, I came up with the following query to find out the time taken. I used the ECID to correlate all the BPEL instances generated during my process flow. Check if it’s of help to any.

SELECT INSTANCE_KEY, COMPOSITE_NAME, COMPONENT_NAME,
       CREATION_DATE, MODIFY_DATE, EVAL_TIME
FROM (SELECT INSTANCE_KEY, COMPOSITE_NAME, COMPONENT_NAME,
               CREATION_DATE, MODIFY_DATE, EVAL_TIME
       FROM BPEL_PROCESS_INSTANCES
        WHERE instance_key IN (SELECT cikey FROM CUBE_INSTANCE
                              WHERE ecid = <<ecid>>)
       UNION ALL
       SELECT 1 INSTANCE_KEY, ‘Comp’ COMPOSITE_NAME, COMPONENT_NAME,
             CREATED_TIME CREATION_DATE, UPDATED_TIME MODIFY_DATE,
             CASE
          WHEN UPDATED_TIME IS NOT NULL THEN
            (extract(DAY FROM (UPDATED_TIME – CREATED_TIME)) * 24 * 60 * 60 +
             extract(hour FROM (UPDATED_TIME – CREATED_TIME)) * 60 * 60 +
             extract(minute FROM (UPDATED_TIME – CREATED_TIME)) * 60 +
             extract(second FROM (UPDATED_TIME – CREATED_TIME))) * 1000
         ELSE NULL
             END AS eval_time
        FROM MEDIATOR_INSTANCE
    WHERE ecid = <<ecid>>
       )
ORDER BY INSTANCE_KEY;

In case of the multiple events, we can rewrite the query if the composites are using sensors. The following query has been used in our case to monitor and to get execution times, minimum and maximum time taken for each of the component in process flow.

/*SELECT ECID, INSTANCE_KEY, CID, COMPOSITE_NAME, COMPONENT_NAME, CREATION_DATE, MODIFY_DATE, EVAL_TIME */
–SELECT COMPONENT_NAME, min(EVAL_TIME), max(eval_time)
SELECT ECID, count(component_name) cnt
FROM (SELECT ECID, CIKEY INSTANCE_KEY, CIKEY CID, COMPOSITE_NAME, COMPONENT_NAME, CREATION_DATE, MODIFY_DATE,
        CASE
        WHEN MODIFY_DATE IS NOT NULL
        THEN (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))) * 1000
          ELSE NULL
              END AS EVAL_TIME
      FROM CUBE_INSTANCE
      WHERE ecid IN (SELECT b.ecid FROM COMPOSITE_SENSOR_VALUE a, MEDIATOR_INSTANCE b
                                               WHERE sensor_name = <<sensor name>>
                            AND a.composite_instance_id = b.composite_instance_id
                                        AND string_value IN (<<values>>)
                        )
    UNION ALL
    SELECT a.ECID, 1 INSTANCE_KEY, a.composite_instance_id CID,
               ‘Comp’ COMPOSITE_NAME, a.COMPONENT_NAME,
              a.CREATED_TIME CREATION_DATE, a.UPDATED_TIME MODIFY_DATE,
              CASE
        WHEN UPDATED_TIME IS NOT NULL
        THEN (extract(DAY FROM (UPDATED_TIME – CREATED_TIME)) * 24 * 60 * 60 +
              extract(hour FROM (UPDATED_TIME – CREATED_TIME)) * 60 * 60 +
              extract(minute FROM (UPDATED_TIME – CREATED_TIME)) * 60 +
              extract(second FROM (UPDATED_TIME – CREATED_TIME))) * 1000
          ELSE NULL
              END AS eval_time
      FROM MEDIATOR_INSTANCE a, COMPOSITE_SENSOR_VALUE b
      WHERE a.composite_instance_id = b.composite_instance_id
        AND b.sensor_name = <<sensor name>>
        AND b.string_value IN (<<values>>)
)
–GROUP BY COMPONENT_NAME
GROUP BY ECID order by cnt

–ORDER BY ecid, INSTANCE_KEY, CID;

Note: Based on the requirement, the above select and group by statements have to be commented/uncommented.

Siva's Blog