In the following post I will describe my quick procedure I always use to debug the database queries generated by an application that hit an Oracle database.
In a lot of cases your queries that hit the database are not written explicitly by a developer. That may be the case when intermediate layers as Hibernate are used.
Hibernate has the habit of generating weird queries that may end up being very expensive on the underlying database.
I encountered the following situations.
- A simple retrieve by id of an entity object that has EAGER relations with other objects may trigger Hibernate to generate a massively costly query with lets of JOIN statements that may span over several tables.
- Hibernate may generate weird queries if not enough hints are given on what you really looking for.
- Hibernate may generate queries for which indexes are missing so they will be very inefficient.
So the following is the simple procedure I use to detect all the above situations.
STEP 1: Generate some test data in your database
It is very important to have a loaded database when doing performance tests. Before starting the real tunning perform several batches of performance tests to load the database with meaningful data and allow it to generate execution plans and load indexes with data.
After the database is “loaded” gather some statistics.
$ sqlplus / as sysdba
Exec DBMS_STATS.GATHER_SCHEMA_STATS('MY_SCHEMA');STEP 2: Create initial database snapshot
Make sure you have access to the sysdba user of your database.
Then connect to your database as sysdba with the following command.
At the sqlplus command line create a snapshot of the database so you can generate an AWR latter.
$ sqlplus / as sysdba
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
STEP 3: Testing with monitoring
Start your application and perform some tests that are aimed to measure the performance of your application in a meaningful way.
STEP 3: Create final database snapshot
At the sqlplus command line create a second snapshot of the database.
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
STEP 4: Generate the AWR file
At the sqlplus command line generate the AWR file
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Make sure to choose html as the file format, then one day and enter as interval the last two snapshot IDs displayed in the options.
STEP 5: Investigate the AWR file
The most important section when trying to find bad queries is the “SQL ordered by Elapsed Time” section.
In this section you can find the top queries ordered by execution time. Go through the queries in order and start looking for the following:
- Does the query look strange ? Strange being one of the cases I mentioned earlier.
- Try to see if you do not have to add to Hibernate some extra hints. For example if you are using a second level query cache, maybe you need to add a hint so the query will be cached instead of hitting the database for each call. A hint that you need that is if number of “Executions” is very high and the data retuned by the query is “static” or “quasi-static”.
- Try to see if the query is missing an index. That is easy to see in case “Elapsed Time per Exec (s)” is vey high.
- Try to see if you cannot simplify the query. As I mentioned if you notice a very complicated query full of UNION and JOIN statements maybe your application code needs to be changed.
- Try to run your query through the query explain in SqlDeveloper. SqlDeveloper is a very nice tool that can give you very good hints on how to optimize your query execution.