How optimizer mode can be change?
Wednesday, 23 December 2009 15:49 | 
Written by Administrator | 
The optimizer mode can be set by using initialization Parameter (OPTIMIZER_MODE) the following mode can be set.
|
RULE |
The rule-based optimizer is used |
|
CHOOSE |
Optimizer use CBO with available statistics. RBO will be use instead if no statistics are available |
|
ALL_ROWS |
Optimizer uses CBO approach regardless of statistics with a goal of best throughput. Appropriate for data warehousing this is the default value of the parameter |
|
FIRST_ROWS_n |
Optimizer uses CBO approach, regardless statistics, with a goal of best response time to return the first n number of rows; (n = 1, 10, 100, or 1000). Appropriate for OLTP type queries |
|
FIRST_ROWS |
The optimizer uses a mix of cost and heuristics(learning themselves) to find a best plan for fast delivery of the first few rows. (for backward compatibility use FIRST_ROWS_n instead) |
- If optimizer uses CBO approach with no statistics, then optimizer uses internal information (No of DB blocks allocated for these tables)
- It is recommended that always use the cost-based optimizer because it can recognize materialized views while RBO does not recognize them.
1 2 |
ALTER SESSION SET optimizer_mode = first_rows_1; --current session ALTER SYSTEM SET optimizer_mode = first_rows_1; --instance level |
Last Updated (Wednesday, 23 December 2009 18:28)