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. ...
Get Oracle SQL: the Essential Reference now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.