Reading Oracle Explain Plans, Part 1: The Basics

This article, Part 1 of a two-part series, will take a close look at exactly how Oracle arrives at an execution plan for both the Oracle rule-based optimizer (RBO) and the cost-based optimizer (CBO).

Oracle first determines if the SQL being submitted is already in the library cache. If it is not, Oracle:

• Parses the SQL
  • Syntax checking
  • Tracking available indexes
  • Proper permissions to all the objects
  • Reads from the bottom up
• Builds an Explain Plan
  • Builds an execution plan using either the RBO or the CBO
  • Oracle 9 & 10 peeks at bind variables
  • Oracle11 has Adaptive Cursor Sharing which tracks various explain plans with different bind variables
• Executes the SQL
  • Runs the execution plan

Oracle reads the SQL from the end to the beginning. While processing the various items, Oracle keeps track of the available indexes.

NOTE

This article discusses mostly regular queries, or those queries that do not access partitioned objects.

ALL queries begin with a table access, applying where clause predicates (columns from the table that appear in the where clause of the query). A result set is produced and the execution plan directs Oracle to which tables it thinks will produce the most efficient way of performing the requested task.

In regular queries with more than one table, Oracle will perform a join operation from these result sets producing a result set based on additional where clause predicates. Oracle only joins two tables at a time. Oracle only processes each WHERE clause predicate once.

Partitioned queries typically access the partitioned objects first then join up the result sets of these scans.

Oracle usually rewrites queries internally; doing what it thinks is the best or fastest method to return the rows. Oracle will convert views to join conditions (and visa versa) , rewrite to use inline views, rewrite sub queries as joins, etc.

NOTE

There is no way to see the SQL that Oracle has actually processed.

Sometimes Oracle needs to access a table again to satisfy an unused or remaining where clause item.

Part 2 of this article will take a closer look at the RBO and CBO.