Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.3 – Release: 10.2 to 10.2
Information in this document applies to any platform.
Goal
This article is explains the new optimizer hint “OPT_PARAM” introduced in 10g R2.
Solution
“OPT_PARAM” is a new optimizer hint introduced in 10g Release 2. This hint behaves the same way as
setting a parameter (e.g, using alter session) except that the effect is for the statement only. The hint only works for optimizer parameters. Global parameters such as optimizer_features_enable are not covered but optimizer_features_enable specifically has its own hint:
/*+ optimizer_features_enable(‘9.2.0’) */
@For an INTERNAL list of usable parameters see: Note:986618.1 Parameters useable by OPT_PARAM hint
Hint Syntax
The syntax is:
/*+ opt_param(
parameter_name is the name of a parameter
parameter_value is its value.
If the parameter contains a numeric value, the parameter value has to be specified without quotes.
The hint can be used to set multiple parameters by repeating the hint, i.e.
/*+ opt_param(
opt_param(
*/
Basic Usage Example
For example, the following hint sets <> to ‘false’ when added to a statement:
/*+ opt_param(‘hash_join_enabled’,’false’) */
e.g.:
Without the hint:
SQL> select empno from emp e, dept d where e.ename=d.dname --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 160 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 8 | 160 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 28 | 280 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
With the hint the hash join is disabled choosing a different plan:
SQL> select /*+ opt_param('hash_join_enabled','false') */ empno from emp e, dept d where e.ename=d.dname; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 160 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN | | 8 | 160 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 4 | 40 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 28 | 280 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| EMP | 28 | 280 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Multiple Parameter Settings example The OPT_PARAM hint can be specified more than once Time to adjust more than one parameter at once as follows: /*+ OPT_PARAM('_always_semi_join' 'off') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('query_rewrite_enabled' 'false') OPT_PARAM('_new_initial_join_orders' 'false') OPT_PARAM('optimizer_dynamic_sampling' 1) OPT_PARAM('optimizer_index_cost_adj' 1) */
Leave a Reply