X
Try Oracle Cloud Platform For Free

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.









view plaincopy to clipboardprint?




  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 ,   

  6.   

  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 ,   

  11.   

  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   

  16.   

  17.   FROM CUBE_INSTANCE   

  18.     WHERE  

  19.       state <> 9   

  20.         and  

  21.       creation_date > '09-APR-08 09.00.00.000000000 AM'  

  22.         and  

  23.       creation_date < '09-APR-08 01.00.00.000000000 PM'  

  24.   

  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.









view plaincopy to clipboardprint?




  1.   

  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 09.00.00.000000000 AM' and  

  9.       creation_date < '11-APR-08 05.00.00.420000000 PM'  

  10. order by process_id asc, exec_time desc  

  11.     


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,
    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 01.08.08.834000 PM 25-MAR-08 12.25.00.825000 PM 1,298,588,009
    09-APR-08 01.08.41.182000 PM 25-MAR-08 12.25.01.637000 PM 1,298,619,545
    26-MAR-08 12.11.00.375000 PM 25-MAR-08 12.29.09.023000 PM 85,311,352
    26-MAR-08 12.11.59.063000 PM 25-MAR-08 01.01.40.481000 PM 83,418,582
    26-MAR-08 12.13.18.759000 PM 26-MAR-08 11.27.04.712000 AM 2,774,047
    27-MAR-08 02.08.47.667000 PM 26-MAR-08 12.49.34.851000 PM 91,152,816
    11-APR-08 02.50.28.458000 PM 26-MAR-08 02.36.33.619000 PM 1,383,234,839
    09-APR-08 03.47.39.267000 PM 28-MAR-08 11.58.10.918000 AM 1,050,568,349
    09-APR-08 02.45.43.858000 PM 28-MAR-08 03.29.44.625000 PM 1,034,159,233
    31-MAR-08 11.51.45.320000 AM 31-MAR-08 11.41.23.182000 AM 622,138
    10-APR-08 03.25.27.702000 PM 31-MAR-08 11.42.23.622000 AM 877,384,080
    10-APR-08 03.13.17.446000 PM 31-MAR-08 11.42.24.818000 AM 876,652,628
    01-APR-08 09.57.40.731000 AM 31-MAR-08 11.42.25.877000 AM 80,114,854
    01-APR-08 09.57.37.028000 AM 31-MAR-08 11.42.25.067000 AM 80,111,961
    01-APR-08 06.26.41.314000 AM 01-APR-08 06.26.33.668000 AM 7,646
    01-APR-08 06.26.40.863000 AM 01-APR-08 06.26.34.598000 AM 6,265
    09-APR-08 12.19.02.921000 PM 01-APR-08 12.48.27.536000 PM 689,435,385
    08-APR-08 03.01.11.974000 PM 01-APR-08 02.01.49.054000 PM 608,362,920
    02-APR-08 06.32.38.971000 AM 02-APR-08 06.32.33.827000 AM 5,144
    I trust that this will be readable on your blog!
    Bye,
    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.

Recent Content