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.

What SQL Statements are Currently Running in my Oracle Database

We are often times faced with the situation where we need to know what statements are running right now in our Oracle database.  Maybe we are having some sort of issue, and applications are not responding or responding very slowly, and you need to get some information fast about what is happening in your system.  One of the first things I always take a look at is what is happening in the database.  This may just be from experience, but on (too) many occasions, I've found the issue to be a long running, inefficient query that is consuming way too many resources.  In any case, one of the things I always want to do is narrow down what tier of the system the problem might be in.  So by scanning through the results of this query, I can quickly determine if the problem is in Oracle or I need to look somewhere else.

So here is the query:

 SELECT  
     s.sid, s.username, s.osuser,   
     s.machine, s.process, s.program, s.module,   
     q.sql_text, q.optimizer_cost,   
     s.blocking_session, bs.username as blocking_user,   
     bs.machine as blocking_machine, bs.module as blocking_module,  
     bq.sql_text AS blocking_sql, s.event AS wait_event,  
     q.sql_fulltext  
   FROM v$session s  
   INNER JOIN v$sql q  
     ON s.sql_id = q.sql_id  
   LEFT OUTER JOIN v$session bs -- blocking sessions  
     ON s.blocking_session = bs.sid  
   LEFT OUTER JOIN v$sql bq -- blocking queries  
     ON bs.sql_id = bq.sql_id  
   WHERE s.type = 'USER';  

What do I look for when I run this query?  First, statements with a high optimizer cost.  The cost is usually high because the statement is expensive.  So I check those out first.  I also look to see if there is a blocking session for a statement.  I have seen cases where someone created a lock and then everyone piled up behind them bringing an entire environment to a halt (fortunately this was is DEV, but still).  And finally, I'll run this statement a couple of times, and see if there are any statements that are still the same on the second or even third running of the query.  Most statements that are run (in an OLTP environment anyway) execute in sub-second time, so if a statement is sticking around over 10, 15 or even 30 seconds, I know it is trouble.

What I also get from the statement is enough information to find out who is running the statement.  Session id, Oracle user, OS user and machine are all there.  I've seen cases where someone ran a statement in production but forgot a join condition or didn't realize they weren't using an index.  From the data in the above statement, I usually am able to track down who it is so I can give them a call and get them to cancel the statement.

I usually keep a cheat sheet around of about 10 statements like this that I commonly use to diagnose what is going on with an Oracle database, and this is always on of the more frequently used statements in that list.  So put this in your own cheat sheet or bookmark this page so that the next time someone asks "Does anyone know what is going on in the Oracle database right now?" you will be prepared to find the answers.

Finding Unused Indexes in Oracle

One area I think SQL Server really does a better job than Oracle is in the DMV's around indexes.  SQL Server makes it very easy to see what indexes it thinks are missing and also gives you the DMV dm_db_index_usage_stats which reports how often an index has been used and how often it has been updated.  Of course the point here is to find indexes that are not used and drop them and evaluate indexes that are only infrequently used and see if there is really a justification for those indexes to exist.

Oracle does make a lot more information available via AWR, but AWR requires you to have a diagnostics pack and tuning pack license.  So not everyone is going to have those.

We can use regular V$ views though to recover some of this functionality.  The following query uses the V$sql_plan table to find all the usages of an index, and then joins that information to the all_indexes view.  So we can see what indexes are getting used, but what statements and what indexes aren't getting used with this query.


 WITH index_usage AS

(

    SELECT pl.sql_id, pl.object_owner, pl.object_name, pl.operation,

            pl.options, count(1) as use_count

        FROM v$sql_plan pl

        WHERE pl.operation = 'INDEX'

        GROUP BY pl.sql_id, pl.object_owner, pl.object_name, pl.operation, pl.options

)

SELECT

        ix.table_owner, ix.table_name, ix.index_name, iu.operation,

        iu.options, ss.sql_text, ss.executions

    FROM all_indexes ix

    LEFT OUTER JOIN index_usage iu

             ON ix.owner = iu.object_owner

             AND ix.index_name = iu.object_name  

    LEFT OUTER JOIN v$sqlstats ss

        ON iu.sql_id = ss.sql_id

    WHERE ix.owner = '<<owner name>>'

    ORDER BY ix.table_name, ix.index_name;

Now this query is not perfect.  Since it is using v$sql_plan, you are limited to what statements are in the shared sql area right now.  But it does give you a start.  And you could always schedule this query to run on an hourly basis and dump those results to a table.  After a few days, you would have a pretty good idea of your index usage patterns.