• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » Optimizer Mode

Optimizer Mode

May 1, 2016 by techgoeasy Leave a Comment

Table of Contents

  • What is Optimizer Mode
  • Different Optimizer Nodes
  • How to change the Optimizer Mode
  • How to specify Optimizer Mode in the sql query

What is Optimizer Mode

Optimizer is the mind of the Oracle engine and it decides the execution plan of the sql queries to be executed. Optimizer has different mode and Optimizer Mode in the parameter which is used to specify the different modes

  • Set in the Init.Ora
  • Can be changed via ALTER SESSION
  • CHOOSE, FIRST_ROWS, ALL_ROWS = cost based optimization
  • RULE = rule based optimization
SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';
 or 
 show parameter optimizer_mode

Different Optimizer Nodes

Optimizer_Mode = Rule

  • Original optimizer developed by prior to 7
  • Applications prior to 11i must run as RULE to be supported
  • Documentation has warned of its removal
  • uses strict rules for determining the execution path
  • Sensitive to position of oracle table in FROM clause. Driving “starting” table should be listed last

Cost Based Optimization

  • Introduced in 7
  • favored optimizer approach
  • sensitive to data volume
  • Chooses the plan with the least cost where cost is a relative index of resources needed to execute operation

FIRST_ROWS Optimizer Mode

  • If an index scan is available, optimizer may choose it over a full table scan
  • If an index scan is available, optimizer may choose a nested loop join over a sort-merge join whenever the associated table is the potential inner table of the nested loop join.
  • If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation

ALL_ROWS Optimizer Mode

  • goal is to choose the minimum resource cost to return entire result set

CHOOSE Optimizer Mode

  • IF statistics have NOT been gathered on any table in the SQL statement then the optimizer reverts to RULE based optimization
  • IF statistics HAVE been gathered on at least ONE table in the SQL statement then the optimizer uses the ALL_ROWS cost based optimization approach

How to change the Optimizer Mode

Optimizer mode can be changed at the session level or system level

For Session level change
 ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
 For System level change:
 ALTER SYSTEM SET OPTIMIZER_MODE = FIRST_ROWS SCOPE=BOTH;

How to specify Optimizer Mode in the sql query

We can specify the optimizer mode while executing the queries using sql hints also

SELECT /*+ ALL_ROWS */  ID,ENAME
 SELECT /*+ FIRST_ROWS */ ID,ENAME
 SELECT /*+ RULE */ ID,ENAME

Also Read
Oracle Performance Tuning: Nice articles on Performance tuning
Oracle Performance Tuning Glossary : Glossary of All the oracle performance tuning terms

Filed Under: Oracle, Oracle Database Tagged With: Optimizer Mode

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us