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

Ramkumar Menon
Director, Product Strategy

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.

Join the discussion

Comments ( 5 )
  • Thomas Gaines Tuesday, April 22, 2008
    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,
    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:
    ------------------------------ ------------------------------ -----------------------
    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!
    Tom Gaines
  • Thomas Gaines Tuesday, April 22, 2008
    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
  • Ramkumar Menon Tuesday, April 22, 2008
    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.
  • abusing lorazepam Thursday, November 18, 2010
    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.
  • John Burden Tuesday, April 26, 2011
    Hey i just visited your site for the first time and i really liked it, i bookmarked it and will be back :D
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.