Opt_param: a new optimizer hint for 10gR2

"OPT_PARAM" is a new optimizer hint introduced in 10gR2. This hint behaves the same way as setting a parameter (e.g, using alter session) but the effect is for the next statement only. Two IN params are the parameter and the parameter_value. If the parameter contains a numeric value, the parameter value has to be specified without quotes.

Syntax: opt_param(<parameter_name> [,] <parameter_value>).
For example:/*+ opt_param('hash_join_enabled','false') */


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 |
--------------------------------------------------------------------------

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 |  

浅谈查询优化器中的JOIN算法：【上一篇】
put your own progress information in V$SESSION_LONGOPS：【下一篇】