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.
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.
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.
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).
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.
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.
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:
[catlist name="Performance Tuning Series"]
[Post Views: 122]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018