Oracle Optimizer: Moving to and working with CBO - Part 2
August 26, 2003
Part 2 of this series covers the Initialization parameters and Hidden or Internal Oracle parameters that influence the Optimizer in choosing execution plans. It is very important to setup these parameters properly.
6. Initialization parameters that affect the Optimizer
Apart from generating statistics, the setup of the below mentioned parameters play a very crucial role in dictating how your system will work. Setting these will depend a lot on what kind of environment you want to create, Online, Batch processing, Data warehousing or a combination of more than one. Please note that the optimizer considers these parameters to evaluate every execution plan it generates in CBO.
The parameter values that I have mentioned below in examples are settings that I have used in some OLTP setups on Oracle 18.104.22.168 with good results. Oracle gives you the liberty of deciding what kind of a setup you want to maintain, so do not just keep the default values and make sure that these parameters are set as per your requirements.
This will decide the mode in which the optimizer engine should run in. Valid values are RULE, CHOOSE, ALL_ROWS, FIRST_ROWS (_n). CBO options have been explained in Part-1 (3.2.2 - Available CBO Modes)
Optionally, the OPTIMIZER_MODE can be set to CHOOSE. This is kind of an intermediate option between RBO and CBO. In fact, it tries to run the query in either CBO or RBO depending on the availability or unavailability of statistics. Therefore, if the tables present in the query have statistics generated on them, CBO (ALL_ROWS only) is preferred or RBO is taken up.
e.g.: optimizer_mode = first_rows
This is set to a version number such as- 8.1.5, 8.1.7, 9.0.0. Since new features and functionality are being added to CBO in every release, its behavior may change and result in different execution plans. You can set this to a version number for which your application is tuned. Please note setting it to a lower version will prevent the use of new features that have come in later versions.
e.g.: optimizer_features_enable = 8.1.7
This parameter specifies the maximum number of permutations that should be considered for queries with joins, to choose an execution plan. This will influence the parse time of queries. This parameter should be set to a lower value. Make sure the other parameters mentioned in this section are set properly so that the optimizer finds an optimal execution plan within the specified limits. It defaults to 80000 in Oracle 8, which means no limits! In Oracle 9i it is defaulted to 2000.
e.g.: optimizer_max_permutations = 2000
Another parameter, OPTIMIZER_SEARCH_LIMIT overrides the effect of this parameter. OPTIMIZER_SEARCH_LIMIT specifies the maximum tables in a query that would be considered for join orders with Cartesian; it is obsolete in 8.1.6.
Optimizer_Index_Cost_Ad takes a value between 1 and 10000 (default 100). Setting this value makes the index more or less friendly to the optimizer. For example setting it to 50 tells the optimizer that an execution plan that uses index(es) is half as expensive as other execution plans that are not using them.
The lower the value (less than 100), the less full table scan executions will take place in the system.
Use of indexes in OLTP is a crucial factor for deciding the response time, but sometimes, full table scans provide better throughput. Nested loop joins are influenced by this parameter value. Set this parameter to a value that is optimal for your application.
e.g.: optimizer_index_cost_adj = 10
This tells optimizer to favor nested loops and IN-list iterators over sort-merge and hash joins. The default value is 100 and makes nested loops and IN-list operator look less expensive and more appealing to Optimizer. The value basically indicates the percentage of index blocks that the optimizer should assume are present in cache.
e.g.: optimizer_index_caching = 100
OPTIMIZER_PERCENT_PARALLEL takes a value between 0 and 100. A low value favors indexes and a higher value will favor full table scans. The optimizer uses this parameter in working out the cost of a full table scan. A value of 100 makes use of degree of parallelism set at object level. I prefer setting it to zero to favor use of indexes and prevent use of parallel query in computing the costing.
It is renamed to _OPTIMIZER_PERCENT_PARALLEL in Oracle 9i and its value
e.g.: optimizer_percent_parallel = 0
This parameter is used to provide backward compatibility with earlier releases. This may also restrict the use of some new features. CBO has undergone lot of changes in release 8. It is advisable to set this parameter to 8.1.0 or higher. Only three digits are required to be specified, however, you can specify more for record purposes.
e.g.: compatible = 8.1.7
This parameter determines the number of database blocks read in one input/output operation during a full table scan. The value set is used in computing the cost of full table scans. OLTP systems will not benefit by setting a high value for this parameter as the computed cost for full table scan execution plans would reduced. The maximum size is Operating system dependent.
e.g.: db_file_multiblock_read_count = 1 (may be set to a higher value)
This parameter defines the maximum memory space allocated per user process that requires sorting of data and for insert and updates to bitmap indexes. Optimizer uses the set value for determining the cost of sorting in a query. This defaults to 64K. Normally a size of 64K to 1M is appropriate for OLTP systems.
Sort-merge joins are influenced by this parameter value. The bigger the size the more appealing will be sort-merge joins over nested-loop and hash joins. The lower the size the more sorting will take place in temporary segments. So assign a value that has been evaluated for your setup.
Oracle recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from Oracle 9i.
e.g: sort_area_size = 1048576
This specifies the number of database blocks to be read each time a sort performs a read from a temporary segment. The default value is 2. General recommendation is to keep the default value. However, if you intend to hike up the SORT_AREA_SIZE considerably, you may evaluate the improvement by changing this parameter.
e.g.: sort_multiblock_read_count = 2