What Statements Have the Worst Performance in my Oracle Database?

So you know that your database access overall is sluggish.  But where do you start?  Maybe you know certain actions in your application are slow, and you can figure out what SQL those actions run, and that is a good place to start.  But But maybe overall, everything seems slower than it should.  Or maybe you are taking over a new application.  For whatever reason, one of the really useful things that Oracle can tell us is what statements are taking the most time to execute and the most resources.

Whenever a statement is run in Oracle, Oracle keeps detailed statistics on the execution of that statement.  Better yet, Oracle makes all of this diagnostic information available to us.  This is one of the great things about Oracle.  It is a highly instrumented piece of software that gives us lots of information on what it is doing with our SQL statements and why.  And if we know where to look, we can analyze this information to understand why our statement ran the way it did and to know what statements we need to focus on for tuning.

The main view with this information is V$SQLSTATS.  Here is the query I use to get information about the most intensive statements in the databases I work with.

 SELECT * FROM  
 (  
   SELECT sql_id, sql_text, executions,   
     elapsed_time, cpu_time, buffer_gets, disk_reads,  
     elapsed_time / executions AS avg_elapsed_time,  
     cpu_time / executions AS avg_cpu_time,  
     buffer_gets / executions as avg_buffer_gets,  
     disk_reads / executions as avg_disk_reads  
   FROM v$sqlstats  
   WHERE executions > 0  
   ORDER BY elapsed_time / executions DESC  
 )  
 WHERE rownum <= 25;  

Basically, we are querying the V$SQLSTATS dynamic performance view for the execution statistics on all of the statements currently stored in our Shared SQL Area and then ordering them by elapsed time in this case.  Finally, the outside query will limit us to the top 25 rows, though you could change this to any number you wanted to.

In the results, you want to look for statements that use a high amount of CPU or perform a large number of logical reads (buffer gets) per statement execution.  These are statements that are using up a lot of resources each time they run, and statements that use up a lot of resource run slowly.  I also like to look at the number of times a statement has been executed.  Sometimes this is very revealing that a statement is running many more times than I expect, revealing a problem in the application.

One thing you want to know about this statement is that is is only looking at statements in the Shared SQL Area in Oracle.  Statements are cached out of this area using a least recently used algorithm, so if you run this query at noon, you may not see an expensive statement that ran as part of your batch process at midnight.  So it is a good idea to run this query at multiple times throughout the day and over several days.  In doing this, right away patterns will emerge about what statements are the most inefficient.  You can then concentrate your efforts on tuning these statements.

When looking at these inefficient statements, one of the things you will want to know is what execution plan the statement was using.  This way, you can tell if it wasn't using an index, was using an index that wasn't optimized to the statement or doing something else that was expensive.  You can retrieve this data from Oracle as well by using the following query and feeding in the SQL ID from the query above.

-- Displaying an execution plan
SELECT plan_table_output 
    FROM
    table(dbms_xplan.display_cursor(‘<<sql id>>',
        null,'typical'));

With these two queries, you will easily be able to walk up to any Oracle database and identify your top performance tuning targets to get to work on.