Skip to content

Performance Tuning Basics 9 : Optimizer Mode

Published November 25, 2017 By Brijesh Gogia

Oracle’s Cost based optimizer OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach. Choosing the right mode setting can improve many times the average performance of your queries. Optimizer is the mind of the Oracle engine which takes into account many factors like indexes, statistics etc and decide the optimum path to fetch the data and return back to the user session. Oracle provides some parameters which if tweaked can make Optimizer mode to go alternate ways which can be beneficial to your SQL queries. optimizer_mode database parameter is one such useful parameters.

POSSIBLE VALUES FOR OPTIMIZER_MODE

Key values that we usually use for these parameters are mentioned below. There are other values like “rule” and “choose” but those are rarely or never used now a days so we will not discuss those here.

 

1. first_rows

The optimizer find a best plan for fast delivery of the first few rows. The goal here is to get the data as quickly as possible even if the overall query is running longer. This method usually uses index instead of full scan as index access will be faster. So for OLTP kind of system this mode may prove helpful many times where users just want to see the small results quickly.

 

2. first_rows_n

The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000). So this is just like using firs_rows method but additionally telling CBO to bring exact number of rows. Telling this additional information regarding number of rows may help further CBO in formulating right access path to get the data.

 

3. all_rows

The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput.The goal is to minimize resource usage for entire statement and not to provide partial results quickly. This method may favor parallel full table scan instead of index scan. So this mode can prove helpful for data warehousing kind of applications where real-time viewing is not the goal but to get the whole data quickly is the goal

Note that this is the default mode used by Oracle database.

HOW TO VERIFY WHAT OPTIMIZER MODE YOU ARE USING

Simple show parameter command will tell you what is your current optimizer mode.

or you can run below SQL also:

SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';

OVERRIDING DEFAULT OPTIMIZER MODE AT SPECIFIC SQL LEVEL 

For an individual SQL statement we can use hints to override the default optimizer mode.

Use ALL_ROWS hint  to optimize a statement block with a goal of best throughput (minimum total resource consumption).

Example:

SELECT /*+ ALL_ROWS */ EMP_NO, ENAME,....

 

Use FIRST_ROWS hint to optimize a statement block with a goal of best response time to return first few rows.

Example:

SELECT /*+ FIRST_ROWS */ EMP_NO, ENAME, ....

 

Use FIRST_ROWS(n) hint when you need only the specific number of records (1,10,100,1000) to fetch.

Example:

SELECT /*+ FIRST_ROWS (10) */ EMP_NO, ENAME,...

CHANGING OPTIMIZER MODE AT SESSION/SYSTEM LEVEL

For Session level change:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

For System level change:

ALTER SYSTEM SET OPTIMIZER_MODE = FIRST_ROWS SCOPE=SPFILE;

CHANGING OPTIMIZER MODE FOR SPECIFIC USERID ONLY

If your want that a specific user always use a specific optimizer mode then you can create a trigger for it:

create or replace trigger set_optimizer_mode
after logon
on database
begin
 if user like 'XXPO' then
 execute immediate 'alter session set optimizer_mode=all_rows';
 end if;
end;
/

 

Please note that Statistics are very important for optimizer mode to work correctly. Database objects statistics must not be stale for getting optimum execution plan.

 

This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:

 

Brijesh Gogia
Latest posts by Brijesh Gogia (see all)
Print 🖨
Post Views: 726

Related posts:

  1. Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
  2. Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
  3. Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
  4. Performance Tuning Basics 11 : Steps to analyze a performance problem
  5. Performance Tuning Basics 1 : Selectivity and Cardinality
  6. Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
  7. Performance Tuning Basics 2 : Parsing
  8. Performance Tuning Basics 10 : Histograms
  9. Performance Tuning Basics 12 : Dynamic Performance Views
  10. Performance Tuning Basics 3 : Parent and Child Cursors

Categories:DATABASE Database Basics Performance Tuning Series TUNING Tuning Basics

2 Comments

  1. jorge batista jorge batista

    good article.
    I have a query that takes more than 45 minutes+ to complete. SELECT * from (select * from (select a.*, rownum DefaultSortColumn from(v_reportadmingapsitesummary) a));
    and using /*+ ALL_ROWS */, takes less than 12 seconds, most of the info I found, said not to use /*+ ALL_ROWS */ in an OLTP.
    Do you see a big issue if I use /*+ ALL_ROWS */?

    September 30, 2020 Reply
  2. Vimal Rathinasamy Vimal Rathinasamy

    Very informative article..Thanks Sir

    December 8, 2023 Reply
Leave a Reply