Il arrive souvent que l’on cherche à connaitre les temps d’exécution des composites Oracle bpel sur une période de temps donné et sur quel nœud du cluster s’est « instancié » le composite.

Il y a bien la console Oracle Enterprise Manager qui nous permet de trouver ces infos mais c’est très lent …une requête en base soa nous fourni un résultat plus exploitable (export sous format csv, html, xml)
 
 
La requête est :
select composite_name Composite, component_name Process, componenttype,CREATE_CLUSTER_NODE_ID, DECODE(cube_instance.STATE,
0, ‘STATE_INITIATED’,
1, ‘STATE_OPEN_RUNNING’,
2, ‘STATE_OPEN_SUSPENDED’,
3, ‘STATE_OPEN_FAULTED’,
4, ‘STATE_CLOSED_PENDING_CANCEL’,
5, ‘STATE_CLOSED_COMPLETED’,
6, ‘STATE_CLOSED_FAULTED’,
7, ‘STATE_CLOSED_CANCELLED’,
8, ‘STATE_CLOSED_ABORTED’,
9, ‘STATE_CLOSED_STALE’,
10,’STATE_CLOSED_ROLLED_BACK’,’unknown’) 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
from cube_instance
where creation_date >= to_timestamp(‘2013-07-08 00:00:00′,’YYYY-MM-DD HH24:MI:SS’)
and creation_date <= to_timestamp(‘2013-07-08 16:47:54′,’YYYY-MM-DD HH24:MI:SS’)
group by composite_name, component_name, componenttype,CREATE_CLUSTER_NODE_ID, state
order by count(*) desc;
Le résultat est: