SQL tuning in Oracle

Question

Is there any article/link available where I can find examples of SQL tuning(Oracle). It would great if it is explain with example. I need something like existing query, statstics, plan and then suggested query/recommendation and new plan.

I found couple of really good links on google:

1. http://www.dba-oracle.com/art_sql_tune.htm
2. http://www.orafaq.com/tuningguide/

However I m looking for real time examples with above mentioned details, which are missing in these articles.

I hope I m not asking for too much. :-)

Answer 1

http://philip.greenspun.com/sql/tuning.html

Sql tuning is not easy because it depends on many factors. There is no universal method. Sometimes, just add an index or hint, sometimes need to change the structure.

Answer 2

There are few shortcuts - for best results:

1. Purchase a copy of "Cost-Based Oracle Fundamentals" by Jonathan Lewis
2. Set up a local Oracle database over which you have full control
3. Work through his examples

This book explains how the optimizer makes its decisions.

I'm not sure of the licensing implications for personal use, but also learn how to use the dbms_sqltune package - in cases where it can find an improved plan over the normal optimizer path it will generate a report showing the differences. You can pick up a lot by looking at this information.

Answer 3

Writing better SQL: Dan Tow's "Tuning SQL" by O'Reilly's a very good cross-platform SQL tuning guide. It's not a short read, but it does cover some pretty hairy examples and shows you how to tune your queries. There's no FAST=TRUE option in Oracle; if there were, it'd be hidden and you wouldn't be allowed to change it. His web site's at http://singingsql.com.

Tuning by Response Time: Cary Millsap's focused heavily on using the Oracle wait interface and queuing theory to tune SQL and systems over the last 20 years or thereabouts; both his current company ( http://method-r.com ) and his previous one ( http://hotsos.com ) have a number of useful resources.

Tuning via Optimizer Statistics: Wolfgang Breitling at http://centrexcc.com has done some excellent work in demonstrating tuning by cardinality feedback to demonstrate that if the SQL's good and there's too much load, the problem can be that you're lying to Oracle (or Oracle's making unsafe deductions) about the nature of the data. He then shows you how to fix it.

All of these are valuable. All of these alone are incomplete.

And I second the recommendation of Jonathan Lewis' fine work; he's got a pretty fair tuning blog over at http://jonathanlewis.wordpress.com

Answer 4

You can learn a lot just by browsing Ask Tom. It is a Q & A about Oracle, oriented mainly to SQL developers. Tom uses a lot of examples, and explains both simple stuff and advanced topics.