accept startTime prompt 'Enter start time (YYYY-MM-DD HH24:MI:SS)'
accept endTime prompt 'Enter end time (YYYY-MM-DD HH24:MI:SS)'
Prompt "==== Rejected Messages ====";
REM 2012-10-24 21:00:00
REM 2012-10-24 21:59:59
select count(*), composite_dn from rejected_message
where created_time >= to_timestamp('&&StartTime','YYYY-MM-DD HH24:MI:SS')
and created_time <= to_timestamp('&&EndTime','YYYY-MM-DD HH24:MI:SS')
group by composite_dn;
Prompt " ";
Prompt "==== Throughput of one-way/asynchronous messages ====";
select state, count(*) Count, composite_name composite from dlv_message
where receive_date >= to_timestamp('&StartTime','YYYY-MM-DD HH24:MI:SS')
and receive_date <= to_timestamp('&EndTime','YYYY-MM-DD HH24:MI:SS')
group by composite_name, state
order by Count;
Prompt " ";
Prompt "==== Throughput and latency of BPEL process instances ===="
select state,
count(*) Count,
trunc(Max(extract(day from (modify_date-creation_date))*24*60*60 +
extract(hour from (modify_date-creation_date))*60*60 +
extract(minute from (modify_date-creation_date))*60 +
extract(second from (modify_date-creation_date))),4) MaxTime,
trunc(Min(extract(day from (modify_date-creation_date))*24*60*60 +
extract(hour from (modify_date-creation_date))*60*60 +
extract(minute from (modify_date-creation_date))*60 +
extract(second from (modify_date-creation_date))),4) MinTime,
trunc(AVG(extract(day from (modify_date-creation_date))*24*60*60 +
extract(hour from (modify_date-creation_date))*60*60 +
extract(minute from (modify_date-creation_date))*60 +
extract(second from (modify_date-creation_date))),4) AvgTime,
composite_name Composite, component_name Process, componenttype
from cube_instance
where creation_date >= to_timestamp('&StartTime','YYYY-MM-DD HH24:MI:SS')
and creation_date <= to_timestamp('&EndTime','YYYY-MM-DD HH24:MI:SS')
group by composite_name, component_name, componenttype, state
order by count(*) desc;
