| Previous | Table of Contents | Next |
There are a number of Oracle initialization parameters that can be set to modify the behavior of the optimizer. These parameters can be used to configure the optimizer mode and the amount of time spent in the optimizer, and to set other optimizer defaults.
The optimization modes (cost-based or rule-based) can be chosen using either of the following methods:
When specifying the optimization method with either the initialization file or the ALTER SESSION command, you can specify the following options:
| Option | Description |
|---|---|
| CHOOSE | This option allows the Oracle optimizer to choose an optimization mode based on the availability of statistics on a particular table, cluster, or index. If statistics are available for any of the tables accessed in the SQL statement, the cost-based approach is used with the goal of best through-put. If none of the tables has statistics available, the rule-based approach is used; the rule-based approach is also the default if no optimization approach is specified. |
| RULE | This option causes the Oracle optimizer to always use the rule-based optimization approach, regardless of any statistics that may have been gathered for the tables being accessed. |
| ALL_ROWS | This option causes the optimizer to used the cost-based approach on all SQL statements, even if there are no statistics available for the tables being accessed. This approach has the goal of best throughput, with the least amount of system resources being used. |
| FIRST_ROWS | This option causes the optimizer to use the cost-based approach on all SQL statements, even if no statistics are available for the tables being accessed. This approach has the goal of best response time. |
Note:
If the cost-based optimization approach is used and no internal statistics are available for a table that is being accessed, other information such as the number of data blocks in the table is used to estimate the cost of various operations.
Other optimizer parameters that can be set include the following:
| Option | Description |
|---|---|
| OPTIMIZER_FEATURES_ENABLED | This parameter can be set to 8.0.0, 8.0.3, and 8.0.4. The effect of setting this parameter is to, in turn, set other initialization parameters. If set to 8.0.4, the following parameters are set to TRUE: PUSH_JOIN_PREDICATE, FAST_FULL_SCAN_ENABLED, COMPLEX_VIEW_MERGING, and B_TREE_BITMAP_PLANS. If this parameter is set to 8.0.0 or 8.0.3, those other parameters are set FALSE. They can, of course, be set independently. Note: This feature is new in Oracle8.0.4. |
| OPTIMIZER_PERCENT_PARALLEL | This parameter specifies the amount of parallelism that is used in the optimizer’s cost function. By default, this parameter is 0, which favors serialized transactions or index scans. Setting this parameter to 100 will allow the optimizer to take into account each table’s degree of parallelism, thus favoring table scans. |
| OPTIMIZER_SEARCH_LIMIT | Specifies the maximum number of tables in the FROM clause for which all possible join permutations are considered. By setting this low, the optimizer will spend less time considering different possibilities. In cases where there are a large number of tables in the FROM clause, the optimizer time can be significant. |
By configuring the optimizer for your particular needs, you will achieve better results.
| Previous | Table of Contents | Next |