Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]Information in this document applies to any platform.
Goal
This article is explains the new optimizer hint "OPT_PARAM" introduced in 10g R2.
Fix
"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:
Hint Syntax
The syntax is:
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(
*/
Basic Usage Example
For example, the following hint sets
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 multiple times in the same hint in order 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) */来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24756186/viewspace-755313/ ，如需转载，请注明出处，否则将追究法律责任。
