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.
SELECT process_id,count(*) as num_invocations, max(((EXTRACT(HOUR FROM MODIFY_DATE) - extract(hour from creation_date)) *3600 + (EXTRACT(MINUTE FROM MODIFY_DATE) - extract(MINUTE from creation_date)) *60 + (EXTRACT(SECOND FROM MODIFY_DATE) - extract(SECOND from creation_date))) *1000) as Max_Time , min(((EXTRACT(HOUR FROM MODIFY_DATE) - extract(hour from creation_date)) *3600 + (EXTRACT(MINUTE FROM MODIFY_DATE) - extract(MINUTE from creation_date)) *60 + (EXTRACT(SECOND FROM MODIFY_DATE) - extract(SECOND from creation_date))) *1000) as Min_Time , avg(((EXTRACT(HOUR FROM MODIFY_DATE) - extract(hour from creation_date)) *3600 + (EXTRACT(MINUTE FROM MODIFY_DATE) - extract(MINUTE from creation_date)) *60 + (EXTRACT(SECOND FROM MODIFY_DATE) - extract(SECOND from creation_date))) *1000) as Avg_Time FROM CUBE_INSTANCE WHERE state <> 9 and creation_date > '09-APR-08 09.00.00.000000000 AM' and creation_date < '09-APR-08 01.00.00.000000000 PM' 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.
select bpel_process_name as process_id, instance_key as cikey, trunc(creation_date), eval_time as exec_time from bpel_process_instances where state <> 9 and creation_date > '11-APR-08 09.00.00.000000000 AM' and creation_date < '11-APR-08 05.00.00.420000000 PM' 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.
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
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
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.