Access Path SQL Statement

Without a tool to show the execution plan for a SQL statement, the DBA or programmer cannot hope to tune the SQL statement. It is rarely obvious by looking at raw SQL to infer how the optimizer will execute the statement, and so the DBA must make frequent use of EXPLAIN PLAN to show how Oracle is actually servicing the query. This is especially a problem with SQL hints where many programmers add a hint with a syntax error, and re-run the statement without checking to see if the execution plan has changed. In short, you should always get in the habit of double-checking the execution plan for each and every SQL statement.

Cost Based Optimizer

When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. Once you have highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.