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.
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.
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
Tuning and Timing
The Product User Profile
Customizing Your SQL*Plus Environment
Appendix A. SQL*Plus Command Reference
Appendix B. SQL*Plus Format Elements
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick
Similar book on Amazon