Oracle Optimizer: Migrating to using the cost-based optimizer-----Series 2.1

oracle| optimization
Oracle Optimizer: Migrating to using the cost-based optimizer-----Series 2.1



Series bis contains initialization parameters and Oracle internal hidden parameters that affect the optimizer's choice of execution plan, and it is important for the optimizer to properly set these parameters.



6. Impact Optimizer Initialization parameters

In addition to generating statistics, the parameter settings mentioned below play a very important role in your system's normal functioning. These settings will mostly depend on what type of environment you want to create. Online, batch processing, data warehouse or more than one combination. Note that the optimizer considers these parameters to evaluate each execution plan generated in the CBO.

The value of the parameter mentioned in the following example is this (it has done well with some online transaction processing in Oracle8.1.7.4), and Oracle gives you the freedom to decide what settings you want to maintain. Therefore, do not leave the default values alone and be sure to set these parameters for each requirement.



6.1) Optimizer_mode



This determines the mode in which the optimizer engine runs. Valid values: Rule, CHOOSE, All_rows, First_rows (_n). The CBO option is explained in the first section 3.2.2.

You can set Optimizer_mode to choose. This intermediate type of option between the Rbo and the CBO,

In fact, it tries to run the query in the CBO or RBO mode if the statistics are available. Therefore, if the table that appears in the query has generated statistics, Oracle will take precedence over the CBO (All_rows only)

Example: Optimizer_mode = first_rows



6.2) Optimizer_features_enable



This item is used to set the version number of 8.1.5, 8.1.7, 9.0.0. Because the CBO adds new functionality in each release, it can change and lead to different execution plans. You can adjust the set version number for your program. Note that setting a lower version will prevent the use of new features in future versions.

Example: optimizer_features_enable = 8.1.7



6.3) Optimizer_max_permutations



This parameter specifies the maximum number of permutations for a query connection to select an execution plan that affects the resolution time of the query and should set a lower value. Make sure that the other parameter mentioned in this section is properly set so that the optimizer finds an optimal execution plan within the specified upper bound. The default value in Oracle8 is 80000, which means there is no limit. The default value in Oracle9 is 2000.

Example: Optimizer_max_permutations = 2000

Another parameter, Optimizer_search_limit, overrides the effect of this parameter, which specifies the largest number of tables in a query, and you can consider ordering a Cartesian connection. has been discarded in the Oracle8.1.6.