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'
September 27, 2010
Subscribe to:
Post Comments (Atom)
https://www.quester.pk/
ReplyDelete