René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

The oracle optimizer

The optimizer is influenced by its decision by the following factors:
  1. OPTIMIZER_MODE in the initialization file
  2. statistics in the data dictionary
  3. OPTIMIZER_GOAL parameter of the ALTER SESSION statement
    • CHOOSE
    • ALL_ROWS
    • FIRST_ROWS
    • RULE
  4. hints

Displaying the current optimizer mode

Use the following select statement if you want to query about the current optimizer mode in use:
select value from v$parameter where name='optimizer_mode'

Altering the optimizer mode for a session only

Use the following alter session command to change the optimizer mode to rule:
alter session set optimizer_mode='rule'