Skip to content

Performance Tuning Basics 9 : Optimizer Mode

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:

 

[Post Views: 122]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *

error: Alert: Content is protected !!