Count Number of BPEL processes Instantiated in 15 minutes intervals

Why count number of instance that too in 15 minutes interval?

Count Number of BPEL processes instantiated in 15 minutes intervals for what is a good questions.

Well, post GO LIVE to identify what is the rate of arrival of BPEL processes at 15 minutes interval is some thing that upper management is always interested in.
Also can be used for performance stats collection.

Query

Well here I have only used the "Hello World BPEL" process for listing. Add process names to identify the rate of arrival of messages in BPEL system.

=================Query START ====================
SELECT processname sourcesystem, time_interval timeinterval,
COUNT (1) numberoftransactions
FROM (
SELECT process_id processname, creation_date,
CASE
WHEN (EXTRACT (MINUTE FROM (creation_date)) / 15 ) <>
TO_CHAR (creation_date, 'DD-MM-YYYY HH24')
'Hrs 1-(First 15 Minutes)'
WHEN (EXTRACT (MINUTE FROM (creation_date)) / 15) <>
TO_CHAR (creation_date, 'DD-MM-YYYY HH24')
'Hrs 2-(15 to 30 Minutes)'
WHEN (EXTRACT (MINUTE FROM (creation_date)) / 15) <>
TO_CHAR (creation_date, 'DD-MM-YYYY HH24')
'Hrs 3-(30 to 45 Minutes)'
ELSE TO_CHAR (creation_date, 'DD-MM-YYYY HH24')
'Hrs 4-(Last 15 Minutes)'
END AS time_interval
FROM orabpel.cube_instance
WHERE process_id IN ('Hello World BPEL')
AND creation_date > TO_DATE (:starttime, 'DD-MON-YYYY HH24:MI:SS')
AND creation_date <>
)
GROUP BY processname, time_interval
ORDER BY time_interval DESC;
=================Query END ====================