Monday, January 3, 2011

ORABPEL Spying-Part1

Requirement:
Want to get the information on currently running processes - Shortest and Longest running instances on server?

Solution:
Below query will give you the desired result:

SELECT * FROM (
SELECT
bpel_process_name AS "ProcessName",
TO_CHAR(MIN(creation_date),'YYYY-MM-DD HH:MI') AS "EarliestDate",
COUNT(*) AS "TotalRunningProcesses",
TO_NUMBER(SUBSTR(MIN(sysdate-creation_date), 1,
INSTR(MIN(sysdate-creation_date), ' '))) AS "ShortestRunning (Days)",
SUBSTR(MIN(sysdate-creation_date),
INSTR(min(sysdate-creation_date),' ')+1,8) AS "ShortestRunning (Hours)",
TO_NUMBER(SUBSTR(MAX(sysdate-creation_date), 1,
INSTR(MAX(sysdate-creation_date), ' '))) AS "LongestRunning (Days)",
SUBSTR(max(sysdate-creation_date),
INSTR(MAX(sysdate-creation_date),' ')+1,8) AS "LongestRunning (Hours)"
FROM ORABPEL.bpel_process_instances
WHERE state = 1
GROUP BY bpel_process_name
ORDER BY "EarliestDate" DESC
)

NOTE: Query for Oracle BPEL Process Manager 10g (10.1.3.x)

4 comments:

Kuldeep Singh said...

Dear Abhishek,
I was searching for queries to find out closed/open/cancelled instances within a time duration say 2 hours or 1 day, and still looking on web...but i come across your blog and found this article and run the query given by you to know if this can help me in anywhere..
i got the results but finding difficulties to understand it...can you please elaborate it column wise?
ProcessName - it is bpel process name
EarliestDate -?
TotalRunningProcesses -?
ShortestRunning (Days)-?
ShortestRunning (Hours)-?
LongestRunning (Days) -?
LongestRunning (Hours) -?

Thanks,
KD

mahakk01 said...

ORABPEL spying is described in this post. It includes different processes. The method and concept that ORABPEL uses is not very complex. You can understand it if you concentrate a bit on it. Have a look at this post. I am sure will find it useful.
sap upgrade issues

lola pons said...

Follow me! imagineyourself2.blogspot.com.es/

Mangun Blog said...

http://collyminati.blogspot.com/search/label/IDRPoker.com%20Agen%20Texas%20Poker%20Dan%20Domino%20Online%20Indonesia%20Terpecaya