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 🙂