Identifying slow-running database queries

SOA Suite applications make heavy use of the database. Identifying the database queries that are running slowly can allow you to identify areas where performance improvements can be made by tuning or reducing the amount of database access.

Getting ready

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.

How to do it…

1. Log in to the SQL database as an administrator.

2. Execute the following SQL query:

   SELECT * FROM
   (SELECT
   sql_fulltext,
   sql_id,
   child_number,
   disk_reads,
       executions,
   first_load_time,
   last_load_time
   FROM    v$sql
   ORDER BY elapsed_time DESC)
   WHERE ROWNUM < 10
   /

   This will return the top 10 recent slowest SQL statements, although statements eventually get removed from the v$sql table. So, this will contain statements that have been recently executed.

How it works…

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.

See also

• Chapter 7, Data Sources and JMS