Oracle SQL: the Essential Reference

Chapter 8. SQL Statement Tuning

If you work with Oracle, it won't be long before you are confronted with the need to tune SQL statements. You will write a "simple" query that ends up with a projected run time of about a week. You wrote it, so you should be able to fix it. Or someone will write a query that takes too long to execute. You will be seen as the source of all Oracle knowledge (after all, you did get that week-long query to run in under a minute!), and, consequently, you will be handed the job of making that query run faster. Tuning SQL statements goes hand-in-hand with writing SQL statements.

You need to understand Oracle's approach to executing a SQL statement before you will be able to tune it effectively. Then you have to determine whether there is a better approach. Finally, assuming there is a better approach, you need to get Oracle to use it. To help you do these things, Oracle has built the following features into its software:

EXPLAIN PLAN statement

You can use this statement to determine Oracle's execution plan for a SQL statement.

SQL Trace facility

Not only gets you the execution plan, but also collects vital statistics related to a statement's execution.

SQL*Plus SET AUTOTRACE command

Causes SQL*Plus to automatically display the query plan and execution statistics for statements as you execute them.

SQL*Plus TIMING command

Allows you to measure elapsed execution time.

Optimizer hints

Allow you to tell Oracle how you want it to execute a particular query. ...