X

Best Practices from Oracle Development's A‑Team

Retrieve Performance Data from SOA Infrastructure Database

Andy Knight
Principal Solution Architect

Introduction

My earlier blog posting shows how to enable, retrieve and interpret BPEL engine performance statistics to aid performance troubleshooting. The strength of BPEL engine statistics in EM is its break down per request. But there are some limitations with the BPEL performance statistics mentioned in that blog posting:

  • The statistics are stored in memory instead of being persisted. To avoid memory overflow, the data are stored to a buffer with limited size. When the statistic entries exceed the buffer size, old data will be flushed out to give way for new statistics. Therefore we can only keep the last X entries of stats data. The statistics from 5 hours ago may no longer be available.
  • The BPEL engine performance statistics only include latencies - information on throughput is not stored.

Main Article

Fortunately, Oracle SOA Suite runs with the SOA Infrastructure database and a great deal of performance data is persisted there. It's more coarse-grained in nature than the in-memory BPEL statistics, but it does have its own strengths as it is persisted.

In this article I would like offer examples of some basic SQL queries you can run against the infrastructure database of Oracle SOA Suite 11G to acquire the performance statistics for a given period of time. You can run these queries immediately after you modify the date range to match your actual system.

1. Asynchronous/one-way messages incoming rates

The following query will show the number of messages sent to one-way/async BPEL processes during a given time period, organized by process names and states

select composite_name composite, state, count(*) Count from dlv_message        where receive_date >= to_timestamp('2012-10-24 21:00:00','YYYY-MM-DD HH24:MI:SS')          and receive_date <= to_timestamp('2012-10-24 21:59:59','YYYY-MM-DD HH24:MI:SS')       group by composite_name, state       order by Count;

2. Throughput of BPEL process instances

The following query shows the number of synchronous and asynchronous process instances created during a given time period. It list instances of all states, including the unfinished and faulted ones. The results will include all composites cross all SOA partitions

select state, count(*) Count, composite_name composite, component_name,componenttype from cube_instance        where creation_date >= to_timestamp('2012-10-24 21:00:00','YYYY-MM-DD HH24:MI:SS')           and creation_date <= to_timestamp('2012-10-24 21:59:59','YYYY-MM-DD HH24:MI:SS')         group by composite_name, component_name, componenttype           order by count(*) desc;

3. Throughput and latencies of BPEL process instances

This query builds on the previous one, providing more comprehensive information. It gives not only throughput but also the maximum, minimum and average elapsed time BPEL process instances.

select composite_name Composite, component_name Process, componenttype, 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('2012-10-24 21:00:00','YYYY-MM-DD HH24:MI:SS')           and creation_date <= to_timestamp('2012-10-24 21:59:59','YYYY-MM-DD HH24:MI:SS')         group by composite_name, component_name, componenttype, state           order by count(*) desc;

 

4. Combine all together

Now let's combine all of these 3 queries together, and parameterize the start and end time stamps to make the script a bit more robust. The following script will prompt for the start and end time before querying against the database:

 

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;

I hope you find these queries useful.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content