OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance. Values: rule The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics. choose The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available. If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach. first_rows_n The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000). first_rows The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows. all_rows The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
Parameter type String
Syntax OPTIMIZER_MODE = {first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule}
Default value choose
Parameter class Dynamic: ALTER SESSION
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance. Values: rule The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics. choose The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available. If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach. first_rows_n The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000). first_rows The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows. all_rows The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).