Oracle SOA Suite: Want performance? Don't log so much and clean up your database!

The Oracle SOA Suite infrastructure, especially composites, use the database intensively. Not only are the process definitions stored in the database, also a lot of audit information gets written there. The SOA infrastructure database, if not well managed, will grow and will eventually have detrimental effects on performance. In this blog post I will give some quick suggestions that will help you increase performance of your SOA Suite infrastructure on the database side by executing some simple scripts. These are some suggestions I have seen work at different customers. Not only do they help managing the SOA Suite data in the database, they will also lead to better SOA Suite performance.

Oracle SOA Suite: Find that composite instance!

When executing BPM or BPEL processes, they are usually executed in the context of a specific entity. Sometimes you want to find instances involved with a specific entity. There are different ways to make this easy. You can for example use composite instance titles or sensors and set them to a unique identifier for your entity. If they have not been used, you can check the audit trail. However, manually checking the audit trail, especially if there are many instances, can be cumbersome. Also if different teams use different standards or standards have evolved over time, there might not be a single way to look for your entity identifier in composite instances. You want to automate this.

It is of course possible to write Java or WLST code and use the API to gather all relevant information. It would however require fetching large amounts of data from the SOAINFRA database to analyse. Fetching all that data into WLST or Java and combining it, would not be fast. I've created a database package / query which performs this feat directly on the 11g SOAINFRA database (and most likely with little alteration on 12c).

Analyzing instances in an Oracle SOA environment: linking composite instances via references

To analyse a running environment, it is useful to know which process calls which other process and how often it does so (which instance is initiated by which reference from which other process). This provides insight in how composites are linked and thus how process flows are implemented. In a previous post I've looked at Oracle Business Transaction Management (BTM) to achieve this insight (http://javaoraclesoa.blogspot.nl/2013/11/a-first-look-at-oracle-business.html). Oracle BTM can't analyse local invocations and requires quite some work to install/setup correctly. Also a license is required, for example SOA Management Pack Enterprise Edition, in order to use this product. To make monitoring composite instances and their relations possible without this product, the dehydration store, can be queried. In this post I'll describe a method on how this can be done. Mind that this method is not fully tested and certainly not supported by Oracle. Use with caution!