Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Where to Find More Tuning Information

Tuning Oracle SQL is a complex subject. The information in this chapter provides only a brief overview of some of Oracle's features to help you during the tuning process.

To start with, read the manual. The Oracle Database Performance Tuning manual contains much tuning information although some of it is more oriented toward tuning the database server rather than tuning individual SQL statements. You'll find detailed information and examples on Oracle hints, the use of EXPLAIN PLAN, and the use of the TKPROF utility. TKPROF is difficult to use but can provide a great deal more information about the cost of a query than you will get from EXPLAIN PLAN.

I can recommend two good O'Reilly books on tuning.

Optimizing Oracle Performance

By Cary Millsap and Jeff Holt. Cary and Jeff's book gives you the tools you need to find and diagnose performance problems in your database. Often, those performance problems are related to poorly performing SQL statements, and that's where the next book comes into play.

SQL Tuning

By Dan Tow. This is the only book I've seen that presents a reliable and methodical approach that you can use to determine the optimal (or near-optimal) execution plan for a query. This book will help you determine which hints to apply, if any, to improve the performance of a SQL statement.