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 ,   
  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.






  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.

Comments:

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

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
About

Principal Product Manager

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today