When server-wide performance problems occur, they impact most or all of the transactions currently being executed on your infrastructure. For example, if the dehydration store database is down, this will definitely have an impact on all the running instances.

Consider asking yourself a few questions to determine whether there is an overarching infrastructure problem:

These are usually indications that there may be a server-wide performance problem. Performance issues impacting the entire server are usually related to either one or a few specific issues. In this case, take a look at the following:

Hardware may not be able to keep up with all the transactions, but by monitoring the various hardware, operating system, and infrastructure metrics, over time you should be able to determine whether you have maxed out your current hardware capacity or not.

Is your environment always slow or does it only demonstrate poor performance under heavy loads? Monitoring the performance of your environment is not a one-time activity. You should have monitoring tools in place to help you pinpoint unusual or high utilization and compare them to average usage times. In Chapter 6, Monitoring Oracle SOA Suite 12c, we proposed using Oracle Enterprise Manager Cloud Control, which provides end-to-end monitoring and alerting (and even some administration) capabilities for both your SOA Infrastructure and operating system. By adding the SOA Management Pack, you would also be able to get composite-specific information of all your environments through a single console.

When we refer to composite instance performance, we mean to say that a particular SOA composite is behaving poorly. In these cases, performance issues are often isolated to one (or a few) composites, but not all. For example, this could be due to the response time from external systems, a badly designed process, due to a suddenly poor performing database, or a queue that the SOA composite is using to read/write data.

Consider a scenario where your composite begins processing an instance by consuming messages from a JMS queue. During peak load, if sufficient threads are not available to the polling adapter, the overall performance of the composite is degraded. There may be no problem with your infrastructure and it may be capable of handling extra load by allocating more threads. The easiest way to overcome this bottleneck is to increase the value of the adapter.jms.receive.threads property that controls the number of threads from its default value of 1. This is a binding property that can be set in an individual composite:

<property name="adapter.jms.receive.threads" type="xs:string" many="false">4</property>

We cannot address all scenarios that may cause a specific composite to behave poorly, as they are unlimited. However, we can provide the means and direction to identify poorly behaving composites and help retrieve the specific composite instance ID of the offending instance so that we may navigate to its details and find out more about what's going on.

Average, minimum, and maximum duration of BPEL/BPMN components

The queries in this section will help you get an idea of the performance of runtime components. The majority of the commands performed provide an easy-to-read format and may initially look complex. However, the queries themselves are relatively basic.

This first query, when run against the [PREFIX]_SOAINFRA schema, returns the name of the BPEL or BPMN component, the partition it is deployed to, its state, as well as average, minimum, and maximum execution time along with the total count. The following query is filtered to retrieve statistics for the last 24 hours via the SYSDATE-1 clause. If preferred, you can further limit the query to a specific composite (COMPOSITE_NAME LIKE '%<composite_name>%') or component (COMPONENT_NAME LIKE '%<component_name>%'):

--------------------------------------------------
-- BPEL/BPMN AVG/MIN/MAX                        --
--------------------------------------------------
SELECT domain_name,
       component_name,
       DECODE(state,'5','COMPLETE','9','STALE','10','FAULTED') AS state,
       TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date),12,2)) * 60 * 60) + (TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 15, 2)) * 60) + TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 18, 4))), '999990.000') AS avg,
       TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date),12,2)) * 60 * 60) + (TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 15, 2)) * 60) + TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 18, 4))), '999990.000') AS min,
       TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date),12,2)) * 60 * 60) + (TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 15, 2)) * 60) + TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 18, 4))), '999990.000') AS max,
       COUNT(1) AS count
FROM   cube_instance
WHERE  creation_date >= SYSDATE - 1
--AND    component_name LIKE '%%'
--AND    composite_name LIKE '%%'
GROUP BY domain_name, 
         component_name,
         state
ORDER BY component_name, 
         state;

The output of the preceding query is shown in Figure 9.5. Here, we can see that the ChangeAddress BPEL component had 112 executions within the last 24 hours with an average execution time of 0.279 seconds and a maximum execution time of 7.3 seconds:

Figure 9.5: Output of the SQL query returning aggregated statistics of the BPEL/BPMN component performance

Note

Poor execution times of BPEL, BPMN, and Mediator components may not be due to the slowness of the infrastructure or code, but could be due to poor response time from the invocation of external services or resources.

This query comes in very handy as it is simple to use and quickly retrieves good, aggregated information of your overall component instance performance.

The duration of a single BPEL/BPMN component instance

In other cases, the performance may be isolated to a single transaction, which could be related to one or more composite and/or component. Regardless, you will have to start somewhere and have a general idea of the composite that is reportedly responding poorly.

This following SQL query lists all BPEL and BPMN instances within the last 24 hours (refer to SYSDATE - 1 in the WHERE clause) that have been completed in over 10 seconds (refer to 10 in the WHERE clause):

--------------------------------------------------
-- BPEL/BPMN COMPONENT INSTANCE DURATION TIMES  --
--------------------------------------------------
SELECT cmpst_id,
       TO_CHAR(creation_date, 'YYYY-MM-DD HH24:MI') AS creation_date,
       component_name,
       componenttype,
       DECODE(state, '5', 'COMPLETE', '9', 'STALE', '10', 'FAULTED') AS state,
       TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 12, 2)) * 60 * 60) + (TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 15, 2)) * 60) + TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 18, 4)),'999990.000') AS duration  
FROM   cube_instance
WHERE  TO_CHAR(creation_date, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE - 1,'YYYY-MM-DD HH24:MI')  
AND    (TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 12, 2)) * 60 * 60) + (TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 15, 2)) * 60) + TO_NUMBER(SUBSTR(TO_CHAR(modify_date - creation_date), 18, 4)) > 10
--AND    component_name LIKE '%%'
--AND    composite_name LIKE '%%'
ORDER BY component_name,
         creation_date;

You can further limit the query to a specific composite or component as needed.

Figure 9.6 shows the output of this query. Now, we can see that we have 7 instances that have taken longer than 10 seconds, and one in particular took 26.7 seconds. The good thing is that the query outputs the composite instance ID! Now we can log in to the console and navigate to the Flow Trace of that particular instance to find out exactly what activity took so long:

Figure 9.6: Output of the SQL query returning individual BPEL/BPMN component instance performance