You will need SOA Suite installed and running, and have loaded it with representative load or live data.

This recipe requires that you are able to log in to the SOA Suite database as an administrator and are able to execute the necessary SQL statements.

Oracle SOA Suite makes very heavy use of the database, being able to identify the queries that take the longest time, which allows these to be optimized. The v$sql table in Oracle is a virtual table that contains the statistics about the recently executed SQL statements, so it can be used to identify the statements that take the longest to execute.

There are a number of ways of discovering slow-running database queries, but we have selected the preceding method because it is simple and easy to understand. More complex statistics can be used to identify exactly what it is that is causing queries to run slowly.

Once you have identified slow-running database queries, the steps you need to take to resolve them depends upon what the queries are. If they are application-related queries, then you can focus on changing the application code, but if they relate to Oracle SOA Suite itself, then you will have to look at things like reducing the amount of logging and persistence in your application, or rearchitecting it to behave differently.