opt_param

This hint is valid only for the following parameters: 
OPTIMIZER_DYNAMIC_SAMPLING,
OPTIMIZER_INDEX_CACHING,
OPTIMIZER_INDEX_COST_ADJ,
OPTIMIZER_SECURE_VIEW_MERGING,
STAR_TRANSFORMATION_ENABLED

举例如下

SQL> set autotrace trace
SQL> select empno from emp e, dept d where e.ename=d.dname ;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 3434568436

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     4 |    80 |     5	(20)| 00:00:01 |
|*  1 |  HASH JOIN	   |	       |     4 |    80 |     5	(20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | IDX_DEPT1 |     4 |    40 |     1	 (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP       |    14 |   140 |     3	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."ENAME"="D"."DNAME")


统计信息
----------------------------------------------------------
	 24  recursive calls
	  0  db block gets
	  7  consistent gets
	  0  physical reads
	  0  redo size
	286  bytes sent via SQL*Net to client
	405  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

加hint后

SQL>  select /*+ opt_param('hash_join_enabled','false') */ empno from emp e, dept d where e.ename=d.dname; 

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 2082400136

---------------------------------------------------------------------------------
| Id  | Operation	    | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |		|     4 |    80 |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN	    |		|     4 |    80 |     6  (34)| 00:00:01 |
|   2 |   SORT JOIN	    |		|     4 |    40 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | IDX_DEPT1 |     4 |    40 |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN	    |		|    14 |   140 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP	|    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."ENAME"="D"."DNAME")
       filter("E"."ENAME"="D"."DNAME")


统计信息
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  4  consistent gets
	  0  physical reads
	  0  redo size
	286  bytes sent via SQL*Net to client
	405  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
	  0  rows processed

备之后查