Obtaining the Execution Times of BPEL Processes over a period of time

Here is a simple BPEL Query that will get you the statistics of execution times of all BPEL Process instances over a period of time. The stats include the number of invocations, minimum, maximum and average time of execution.

  1. SELECT process_id,count(*) as num_invocations,   
  2.   max(((EXTRACT(HOUR FROM MODIFY_DATE) - extract(hour from creation_date)) *3600 +   
  3.       (EXTRACT(MINUTE FROM MODIFY_DATE) - extract(MINUTE from creation_date)) *60 +   
  4.       (EXTRACT(SECOND FROM MODIFY_DATE) - extract(SECOND from creation_date))) *1000)   
  5.   as Max_Time ,   
  7.   min(((EXTRACT(HOUR FROM MODIFY_DATE) - extract(hour from creation_date)) *3600 +   
  8.      (EXTRACT(MINUTE FROM MODIFY_DATE) - extract(MINUTE from creation_date)) *60 +   
  9.      (EXTRACT(SECOND FROM MODIFY_DATE) - extract(SECOND from creation_date))) *1000)   
  10.   as Min_Time ,   
  12.   avg(((EXTRACT(HOUR FROM MODIFY_DATE) - extract(hour from creation_date)) *3600 +   
  13.      (EXTRACT(MINUTE FROM MODIFY_DATE) - extract(MINUTE from creation_date)) *60 +   
  14.      (EXTRACT(SECOND FROM MODIFY_DATE) - extract(SECOND from creation_date))) *1000)   
  15.   as Avg_Time   
  18.     WHERE  
  19.       state <> 9   
  20.         and  
  21.       creation_date > '09-APR-08 AM'  
  22.         and  
  23.       creation_date < '09-APR-08 PM'  
  25.       group by process_id order by process_id asc  

In this query, you need to replace the sample date and time values with the values that you wish to use and report. Here is another query that gives the execution times of all BPEL instances over the given time period.

  2. select bpel_process_name as process_id,   
  3.        instance_key as cikey,   
  4.        trunc(creation_date),   
  5.        eval_time as exec_time   
  6. from bpel_process_instances   
  7. where state <> 9 and  
  8.       creation_date > '11-APR-08 AM' and  
  9.       creation_date < '11-APR-08 PM'  
  10. order by process_id asc, exec_time desc  

In this query, you need to replace the sample date and time values with the values that you wish to use and report. Note that the above query shall give you the execution times for a particular day. Take a look at the comments on this blog entry to see a modified example that uses the "day" as well to obtain stats that span across multiple days.


Ram - Thanks very much for the posting. I'm slowly studying the various statistics available in the BPEL repository in hopes of contributing somewhat to what my developers are doing. I found your title of your post rather interesting. "Most expensive" you say? Is that really true, since you're examining only the execution times of a single BPEL instance? Is the net time spent on an execution a true reflection of its cost? Hmmm. Anyway, a couple of things caught my eye. First, you're ignoring the day component of the elapsed time. In my company, there are several human tasks that won't necessarily be addressed for several days. So with that in mind, I've cleaned up your query a bit. Any thoughts? column modify_date format a30 column creation_date format a30 column time_diff_milli_seconds format 999,999,999,999 select modify_date, creation_date, extract(day from (modify_date - creation_date)) * 24 * 60 * 60 * 1000 + extract(hour from (modify_date - creation_date)) * 60 * 60 * 1000 + extract(minute from (modify_date - creation_date)) * 60 * 1000 + extract(second from (modify_date - creation_date)) * 1000 time_diff_milli_seconds from orabpel.cube_instance where state != 9 and rownum < 20; And the output: MODIFY_DATE CREATION_DATE TIME_DIFF_MILLI_SECONDS ------------------------------ ------------------------------ ----------------------- 09-APR-08 PM 25-MAR-08 PM 1,298,588,009 09-APR-08 PM 25-MAR-08 PM 1,298,619,545 26-MAR-08 PM 25-MAR-08 PM 85,311,352 26-MAR-08 PM 25-MAR-08 PM 83,418,582 26-MAR-08 PM 26-MAR-08 AM 2,774,047 27-MAR-08 PM 26-MAR-08 PM 91,152,816 11-APR-08 PM 26-MAR-08 PM 1,383,234,839 09-APR-08 PM 28-MAR-08 AM 1,050,568,349 09-APR-08 PM 28-MAR-08 PM 1,034,159,233 31-MAR-08 AM 31-MAR-08 AM 622,138 10-APR-08 PM 31-MAR-08 AM 877,384,080 10-APR-08 PM 31-MAR-08 AM 876,652,628 01-APR-08 AM 31-MAR-08 AM 80,114,854 01-APR-08 AM 31-MAR-08 AM 80,111,961 01-APR-08 AM 01-APR-08 AM 7,646 01-APR-08 AM 01-APR-08 AM 6,265 09-APR-08 PM 01-APR-08 PM 689,435,385 08-APR-08 PM 01-APR-08 PM 608,362,920 02-APR-08 AM 02-APR-08 AM 5,144 I trust that this will be readable on your blog! Bye, Tom Gaines

Posted by Thomas Gaines on April 22, 2008 at 09:32 AM PDT #

Ram - Sorry to be a pest and all, but do you have any suggestions on how to post code to your blog and have it appear nicely formatted? Both the query and results were quite beautiful when I grabbed them from a temporary text file I was using. However, I see that all of the text is jammed together on your blog. The linefeed characters have been removed entirely! Any suggestions? Tom Gaines

Posted by Thomas Gaines on April 22, 2008 at 10:36 AM PDT #

You are absolutely correct - I have overlooked the title - the execution times may not be always reflective of the cost - I shall edit the title accordingly. I shall also make a note mentioning that the stats reflect daily execution times. BTW the stats are not just for a particular BPEL process instance - it shows the stats for all BPEL process instances in a day. Thank you very much for your feedback. On your second comment, I think comments cannot be in html format - thats why its tough to format the same. But I shall ensure that the message is conveyed clearly.

Posted by Ramkumar Menon on April 22, 2008 at 10:58 AM PDT #

The consensus among most legal scholars is that incorporation of the Second Amendment is likely. True, the Supreme Court has said in some past cases that the Second Amendment applies only to the federal government. But a footnote in Heller cast doubt on those decisions. For now, lower courts probably have to follow the older decisions until the Supreme Court says otherwise.

Posted by abusing lorazepam on November 18, 2010 at 04:02 AM PST #

Hey i just visited your site for the first time and i really liked it, i bookmarked it and will be back :D

Posted by John Burden on April 26, 2011 at 03:24 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

Principal Product Manager


« February 2016