Useful SQLs for Querying SOA Suite Dehydration Tables

As you all know Oracle SOA Suite uses dehydration tables to store the state of instances. We can query these tables to troubleshoot issues. Here are few sqls I find useful:

1) Count of bpel instances created between two timestamps. Count doesn’t include mediator instances:

select count(1)
from cube_instance 
where creation_date between to_timestamp('2018-04-23 09:00', 'YYYY-MM-DD HH24:MI') and to_timestamp('2018-04-24 17:00', 'YYYY-MM-DD HH24:MI')

2) Count of bpel instances created between two timestamps group by hour:

select to_char(creation_date, 'YYYY-MM-DD HH24'), count(1)
from cube_instance 
where creation_date between to_timestamp('2018-04-23 09:00', 'YYYY-MM-DD HH24:MI') and to_timestamp('2018-04-24 17:00', 'YYYY-MM-DD HH24:MI')
group by to_char(creation_date, 'YYYY-MM-DD HH24')
order by to_char(creation_date, 'YYYY-MM-DD HH24');

3) Average and max response times:

select TO_CHAR(created_time, 'YYYY-MM-DD HH24'),
avg(((TO_NUMBER(SUBSTR(TO_CHAR(created_time-updated_time),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(created_time-updated_time),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(created_time-updated_time),18,4)))) as avg,
max(((TO_NUMBER(SUBSTR(TO_CHAR(created_time-updated_time),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(created_time-updated_time),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(created_time-updated_time),18,4)))) as max, count(1) as Count

from SCA_FLOW_INSTANCE 

where created_time  >= to_date('01-11-18 23:30','DD-MM-YY HH24:MI')
--and created_time  >= to_date('06-11-18 23:30','DD-MM-YY HH24:MI')
AND created_time   <= to_date('17-11-18 18:00','DD-MM-YY HH24:MI')
AND extract( hour from created_time) in (18,19,20,21,22,23,00,01,02,03)
and extract( hour from created_time) = extract( hour from updated_time)
group by TO_CHAR(created_time, 'YYYY-MM-DD HH24')
order by TO_CHAR(created_time, 'YYYY-MM-DD HH24') asc; 

4)   Throughput hourwise in 11g:  

SELECT TRUNC(creation_date)
  ||'        ',
  lpad(TO_CHAR(extract(hour FROM creation_date)),2,0)
  || '00 hrs - '
  || lpad(TO_CHAR(extract(hour FROM creation_date) +1),2,0)
  ||'00 hrs' Slot,
  state_text,
  COUNT(*)
FROM PRD_SOAINFRA.bpel_process_instances
WHERE composite_name = 'SyncPersonUCMJMSProducer'
AND creation_date   >= to_date('13-JUN-18 21:00','DD-MON-YY HH24:MI')
--AND creation_date   <= to_date('05-FEB-18 11:00','DD-MON-YY HH24:MI')
GROUP BY extract(hour FROM creation_date),
  TRUNC(creation_date),
  state_text
ORDER BY 1,2;

5)  Replog response time group by response range in 11g. It puts responses into 10 buckets 1 to 10 seconds:

SELECT CASE 
         WHEN eval_time <= 1000 THEN '1'
         WHEN eval_time <= 2000 THEN '2'
         WHEN eval_time <= 3000 THEN '3'
         WHEN eval_time <= 4000 THEN '4'
         WHEN eval_time <= 5000 THEN '5'
         WHEN eval_time <= 6000 THEN '6'
         WHEN eval_time <= 7000 THEN '7'
         WHEN eval_time <= 8000 THEN '8'
         WHEN eval_time <= 9000 THEN '9'
         else '10'
         end as time, count(1)
FROM PRD_SOAINFRA.bpel_process_instances  a
WHERE a.composite_name = 'SyncCustomerSoc7R2ReqABCSImpl'
AND a.creation_date   >= to_date('2019-02-12 18:00','YYYY-MM-DD HH24:MI')
AND a.creation_date   <= to_date('2019-02-12 19:00','YYYY-MM-DD HH24:MI')
group by CASE
WHEN eval_time <= 1000 THEN '1'
         WHEN eval_time <= 2000 THEN '2'
         WHEN eval_time <= 3000 THEN '3'
         WHEN eval_time <= 4000 THEN '4'
         WHEN eval_time <= 5000 THEN '5'
         WHEN eval_time <= 6000 THEN '6'
         WHEN eval_time <= 7000 THEN '7'
         WHEN eval_time <= 8000 THEN '8'
         WHEN eval_time <= 9000 THEN '9'
         else '10' END;

Oracle Managed File Transfer (MFT) sqls:

1) Get size of files transferred:

select CREATE_TS, payload_Size
from MFT_DATA_STORAGE
where create_ts >= to_date('05-JAN-19 00:00','DD-MON-YY HH24:MI')
and create_ts <= to_date('05-JAN-19 23:59','DD-MON-YY HH24:MI')
order by create_ts asc;

2) Get all active file transfers:

select * from MFT_SOURCE_MESSAGE 

where Status='ACTIVE' and SOURCE_NAME='xyz';

3)  Get failed transfers:

select * from MFT_SOURCE_MESSAGE 
where SOURCE_NAME ='xyz' and STATUS='FAILED';