No shortcuts for SQL tuning…

 

From time to time I get questions like,

“How can I make this SQL statement run faster?”

When I see these questions I’m filled with dread. I don’t want to say nothing, but at the same time there is often little I can do to help because SQL tuning requires some knowledge of what you’re trying to achieve. So this post will serve as my generic answer.

Identify poorly performing SQL statements using Statspack, AWR or ADDM.

Once you’ve identified a problem SQL statement, trace it using SQL Trace to get hold of the execution plan and wait states, which should give you some idea of why it is performing so badly. Use this information to help you decide if you should do one or more of the following:

  • Rewrite the SQL statement.
  • Add suitable indexes to the underlying tables.
  • Force appropriate index usage with optimizer hints. Remember, forcing full scans by avoiding indexes may be appropriate in some situations (suggested by Jeff).
  • Check the statistics used by the optimizer are up to date. Gathering histograms may help. (suggested by Don and Nuno)

During the rewrite process Explain Plan and DBMS_XPLAN will provide you with the adjusted execution plan, without having to rerun the statement.

If you’re using Oracle 10g you might want to consider using the SQL Tuning Advisor or the SQL Access Advisor. I’ve seen limited success with these, but you may find them more useful.

At some point you may realize that no amount of tweaking is going to solve your performance problems and a more radical approach is necessary. In these cases it may be necessary to use materialized views to “pre-fetch” the data for your problem query, or just redesign that section of your application to make it more efficient.

If there are any shortcuts in this process please pass them on, because I’ve not found them yet 🙂

Cheers

Tim…

PS. I’ve made a couple of modifications based on comments 🙂

Author: Tim...

DBA, Developer, Author, Trainer.

9 thoughts on “No shortcuts for SQL tuning…”

  1. Don’t forget, not all slow queries are sensible targets to tune! Users have been known to ask the wrong questions.
    Also it may be better to target the 1.2 second query that executes 2000 times a day than the 3 minute query that runs once a month.

  2. Yes, I couldn’t agree more. That’s why I use statspack/AWR to identify problem queries. As you know the information is displayed in several sections, each ordered by a different critera. That way you can look at the worst offenders ordered by elapsed time, executions, CPU, I/O etc.

    In addition it makes sense to know what you expect to get out of a tuning excercise. What result would you consider success?

    Of course sometimes the identification is easy. You have a screen that it slow, and making it useable is the goal 🙂

    Cheers

    Tim…

  3. One more to add to:

    * Rewrite the SQL statement.
    * Add suitable indexes to the underlying tables.
    * Force index usage with optimizer hints.

    * Force full scan where appropriate

  5. * Rewrite the SQL statement.
    * Add suitable indexes to the underlying tables.
    * Force index usage with optimizer hints.

    I’d add one more:
    Consider analysing stats and their effect on the statement.

    Quite often a little bit of tweaking of the stats themselves can produce miracles. But like anything else, use with caution and test first.

  7. Not quite ‘on-topic’ but if you have a materialized view / query rewrite environment then make sure that the session you run your tests with has the same settings for query_rewrite_enabled and query_rewrite_integrity as the user!

    And read the explain plan carefully to find the table you are really using!

    I say this because I’ve been caught out by this before.

Comments are closed.