Useful queries for Oracle BPEL Process Manager 10g

Here are some several SQL queries that may be of use (queries the ORABPEL schema), that queries for relevant instance level information for Oracle BPEL Process Manager 10g (10.1.3.x).

Substitute anything in red with your own values.


-- ----------------------------------------
--
-- Currently running processes
--   o Includes the shortest running instance
--   o Includes the longest running instance
--
-- ----------------------------------------

SELECT * FROM (
  SELECT   bpel_process_name AS "Process Name",
           TO_CHAR(MIN(creation_date),'YYYY-MM-DD HH:MI') AS "Earliest Date",
           COUNT(*) AS "Total Running Processes",
           TO_NUMBER(SUBSTR(MIN(sysdate-creation_date), 1, INSTR(MIN(sysdate-creation_date), ' '))) AS "Shortest Running (Days)",
           SUBSTR(MIN(sysdate-creation_date),INSTR(min(sysdate-creation_date),' ')+1,8) AS "Shortest Running (Hours)",
           TO_NUMBER(SUBSTR(MAX(sysdate-creation_date), 1, INSTR(MAX(sysdate-creation_date), ' '))) AS "Longest Running (Days)",
           SUBSTR(max(sysdate-creation_date),INSTR(MAX(sysdate-creation_date),' ')+1,8) AS "Longest Running (Hours)"
  FROM     orabpel.bpel_process_instances
  WHERE    state = 1
  GROUP BY bpel_process_name
  ORDER BY "Earliest Date" DESC
)


-- ----------------------------------------
--
-- Longest average time for top 40 processes
--
-- ----------------------------------------

SELECT * FROM (
  SELECT   bpel_process_name AS "Process Name",
           COUNT(*) AS "Completed Processes",
           TO_NUMBER(TO_CHAR(MAX(eval_time / 1000/60),'99999990.00')) AS "Max Time (Mins)",
           TO_NUMBER(TO_CHAR(MIN(eval_time / 1000)/60,'99999990.00')) AS "Min Time (Mins)",
           TO_NUMBER(LTRIM(RTRIM(TO_CHAR(AVG(eval_time/ 1000/60), '99999990.00')))) AS "Avg Time (Mins)"
  FROM     orabpel.bpel_process_instances
  WHERE    state = 5
  AND      creation_date > TO_DATE(sysdate) - 30
  GROUP BY bpel_process_name
  ORDER BY TO_NUMBER(LTRIM(RTRIM(TO_CHAR(AVG(eval_time/ 1000/60), '99999990.00')))) DESC
)
WHERE ROWNUM < 40


-- ----------------------------------------
--
-- Get all BPEL instances and their duration that run longer than 60 seconds
--
-- ----------------------------------------

SELECT process_id, creation_date, SUBSTR(modify_date-creation_date,12) duration, SUBSTR(REGEXP_SUBSTR(title, '[^ ]+', 1, 2), 2) instid
FROM   cube_instance
WHERE  TO_CHAR(creation_date, 'YYYY-MM-DD HH24') >= '2009-06-03 17'
AND    TO_CHAR(creation_date, 'YYYY-MM-DD HH24') <= '2009-06-03 20'
AND    (modify_date-creation_date) > '0 0:0:60.0'
AND    process_id IN ('HelloWorldBPEL')
ORDER BY modify_date DESC


-- ----------------------------------------
--
-- Get total count of related BPEL instances per minute during a specific timeframe
--
-- ----------------------------------------

SELECT process_id, TO_CHAR(modify_date,'DD-MM-YYYY HH24:MI') mdate, COUNT(1)
FROM   cube_instance
WHERE  TO_CHAR(creation_date, 'YYYY-MM-DD HH24') >= '2009-06-03 17'
AND    TO_CHAR(creation_date, 'YYYY-MM-DD HH24') <= '2009-06-03 20'
AND    process_id IN ('HelloWorldBPEL')
GROUP BY process_id, TO_CHAR(modify_date,'DD-MM-YYYY HH24:MI')
ORDER BY process_id, mdate


-- ----------------------------------------
--
-- Get total BPEL instances that were run during a specific timeframe
--   o Includes start time of first instance during that timeframe
--   o Includes end time of last instance during that timeframe
--
-- ----------------------------------------

SELECT process_id, TO_CHAR(state) state, MIN(creation_date) start_test, MAX(creation_date) end_test, COUNT(1)
FROM   cube_instance
WHERE  TO_CHAR(creation_date, 'YYYY-MM-DD HH24') >= '2009-06-03 17'
AND    TO_CHAR(creation_date, 'YYYY-MM-DD HH24') <= '2009-06-03 20'