Oracle Optimizer: Migrate to the cost-based optimizer ---- Series 2.1
The second series contains the initialization parameters of the optimizer to select the execution plan and the internal hidden parameters of Oracle, and these parameters are quite important for optimizer.
6. Influence the initialization parameters of the optimizer
In addition to generating statistics, the parameters mentioned below plays an important role in your system's normal work. These settings will mostly depend on what type of environment you want to create. A combination of online, batch, data warehouse or more than one. Note that the optimizer considers these parameters to evaluate each execution plan generated in CBO.
The parameter value mentioned in the following example is this (it has been in Oracle8.1.7.4, some online transactions are running well), and Oracle gives you the freedom to maintain what setup for maintenance. So do not only maintain the default and Confident to set these parameters for each requirement.
6.1) Optimizer_Mode
This decision does the optimizer engine runs in what mode. Valid value: rule, choose, all_rows, first_rows (_n). The CBO option is explained in the first part 3.2.2.
You can set Optimizer_Mode to Choose. This intermediate type of option is between RBO and CBO,
In fact, it tries to run the query in CBO or RBO mode if the statistics are available. Therefore, if there is a statistics that appear in the query, Oracle will prefer CBO (all_rows only)
Example: Optimizer_Mode = first_ROWS
6.2) Optimizer_features_enable
This item is used to set the version number such as 8.1.5, 8.1.7, 9.0.0. Because CBOs in each version add new features, it changes and leads to different execution plans. You can adjust the settings version number for your program. Note that the lower version will prevent the use of new features later.
Example: Optimizer_features_enable = 8.1.7
6.3) Optimizer_max_permutations
This parameter specifies the maximum number of queues to the connection to select an execution plan, which affects the resolution time of the query, and should set a lower value. Make sure that the normal settings of another parameter mentioned in this section look for an optimal execution plan within the specified upper limit. The default value in Oracle8 is 80000, which means there is no limit. The default value of Oracle9 is 2000.
Example: Optimizer_Max_Permutations = 2000
Another parameter OPTIMIZER_SEARCH_LIMIT covers the effect of this parameter, which specifies the maximum number of tables in a query, and can consider sorting the Carteskle connection. It has been discarded in Oracle8.1.6.
