Some initialization parameters that affect the optimizer

　　

Some initialization parameters that affect the optimizer

Optimizer_features_enable: Each version of the Oracle optimizer features are different, especially after the version upgrade must modify this parameter to use only the version of the optimizer features supported. It can be given values such as: 9.2.0, 9.0.2, 9.0.1, 8.1.7, 8.1.6, and so on.

Cursor_sharing: This parameter replaces the direct amount in the SQL statement with a variable, and an OLTP system that has a large number of direct amounts can consider enabling this parameter. Note, however, that binding variables can make a lot of SQL reuse and reduce analysis time, but the execution plan may not be ideal. OLTP systems are typically used for binding variables, and the OLTP system features SQL that runs frequently and is relatively short in time, with a large proportion of SQL analysis time. The DSS system does not recommend this parameter if, in the DSS system, the SQL runs for a long time and the analysis time is negligible compared to a good execution plan.

Hash_area_size: This is the storage area of the hash table, if the value of this parameter is not too small to use hash join, it will have a great effect on the hash connection performance. If it is 9i it is recommended to start the workspace auto-management and then set Pga_aggregate_target.

Sort_area_size: The size of the memory sort area, if the memory area is not enough to be written to the disk when sorting. 9i also recommends starting the workspace auto-management and then setting the Pga_aggregate_target.

Hash_join_enabled: Only if this parameter is enabled, the CBO will consider a hash connection when considering the connection mode.

Optimizer_index_caching: This parameter represents the percentage of cached index blocks, and the range of optional values is 0-100. This value affects nested loops, and if the value is set higher, the CBO will prefer to use nested loops.

Optimizer_index_cost_adj: The optimizer uses this parameter (a percentage) to convert the cost of the index scan to the equivalent full-table scan cost, which is then compared to the cost of the full table scan. The default value of 100 indicates that the index scan cost is equivalent to the full table scan cost. The range of selectable values is 0-10000.

Optimizer_max_permutations: This initial parameter is used to set the maximum number of connection sequences that the optimizer considers, and the optimizer constantly produces a connection arrangement of possible tables until the number of permutations reaches the parameter optimizer_max_permutations. Once the optimizer stops producing a new arrangement, it chooses the lowest-cost arrangement from there.

Db_file_multiblock_read_count: This parameter represents the number of contiguous blocks of I/O read (block# continuous, and one I/O cannot exceed extent) during a full table scan or an index fast full scan.

Optimizer_mode: Optimizer mode. Values are: RULE, CHOOSE, All_rows, First_rows_n, First_rows.

Partition_view_enabled: If set to true, the optimizer skips partitions that are not requested in the partitioned view, and this parameter can also change how the cost-based optimizer calculates partitioned view statistics from the underlying table statistics.

Query_rewrite_enable: If set to true, the optimizer will override SQL with the available materialized views.

Some initialization parameters that affect the optimizer