Controls the amount of effort made by the query optimizer to find an access plan for a SQL statement.
|PUBLIC role||For current user||For other users|
|Allowed to set permanently?||Yes, with SET ANY PUBLIC OPTION||Yes||Yes, with SET ANY PUBLIC OPTION|
|Allowed to set temporarily?||Yes, with SET ANY PUBLIC OPTION||Yes (current connection only)||No|
The optimization_level option controls the amount of effort that the optimizer spends on optimizing SQL data manipulation language (DML) statements. This option controls the maximum number of alternative join strategies that the optimizer considers for any SELECT block. The higher the setting of optimization_level, the greater the maximum number of join strategies that the optimizer considers.
If the option is set to 0, then the optimizer chooses the first access plan it considers for execution, in effect avoiding any cost-based comparison of alternative plans. In addition, with level 0 some semantic optimizations of nested queries are disabled. If this option is set to a value higher than 0, the optimizer evaluates alternative strategies and chooses the one with the lowest expected cost. If this option is set to a value greater than the default (9), the optimizer is more aggressive in its search for alternative strategies, possibly resulting in much higher elapsed time spent in the optimization phase.
In typical scenarios, this option is temporarily set to lower levels (0, 1, or 2) when the application desires faster OPEN times for a DML statement. It is known that although the statement may be complex, the query's execution time is very small, and the specific access plan chosen by the optimizer is less consequential. It is not recommended that the PUBLIC setting of optimization_level be changed from its default.
The effect of setting the optimization_level option is independent of the settings of the optimization_goal and optimization_workload options.
Simple DML statements (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row) are optimized heuristically and bypass the cost-based optimizer altogether. The optimization of simple DML statements is not affected by the setting of the optimization_level option. The count of the number of requests optimized through the optimizer bypass mechanism is available as the QueryBypassed connection property.
You can override any temporary or PUBLIC settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.