O'Reilly logo

Oracle SQL Tuning Pocket Reference by Mark Gurry

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The SQL Optimizers

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. Oracle supports two optimizers: the rule-base optimizer (which was the original), and the cost-based optimizer.

To figure out the optimal execution path for a statement, the optimizers consider the following:

  • The syntax you've specified for the statement

  • Any conditions that the data must satisfy (the WHERE clauses)

  • The database tables your statement will need to access

  • All possible indexes that can be used in retrieving data from the table

  • The Oracle RDBMS version

  • The current optimizer mode

  • SQL statement hints

  • All available object statistics (generated via the ANALYZE command)

  • The physical table location (distributed SQL)

  • INIT.ORA settings (parallel query, async I/O, etc.)

Oracle gives you a choice of two optimizing alternatives: the predictable rule-based optimizer and the more intelligent cost-based optimizer.

Understanding the Rule-Based Optimizer

The rule-based optimizer (RBO) uses a predefined set of precedence rules to figure out which path it will use to access the database. The RDBMS kernel defaults to the rule-based optimizer under a number of conditions, including:

  • OPTIMIZER_MODE = RULE is specified in your INIT.ORA file

  • OPTIMIZER_MODE = CHOOSE is specified in your INIT.ORA file, andno statistics exist for any table involved in the statement

  • An ALTER SESSION SET OPTIMIZER_MODE = RULE command has been issued ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required