[20160730]hint 冲突.txt

--昨天别人优化加提示无效,问我为什么无效?我一般认为这种情况称为hint 冲突.

--通过例子来说明,我测试会使用ordered,我一般不喜欢使用ordered提示,通过例子来说明.

1.环境:

SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

set autot traceonly

select * from emp,dept where emp.deptno=dept.deptno;

--不加提示,缺省执行计划如下:

Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 | |   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 | |   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 | |*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 | |   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | ----------------------------------------------------------------------------------------

2.测试1:

--如果你使用use_nl提示,里面仅仅包含1个表,按照文档介绍,作为被驱动表: select /*+ use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno; Execution Plan -------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 | |   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 | |   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 | |*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 | |   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | ----------------------------------------------------------------------------------------

select /*+  use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan ---------------------------------------------------------- Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |  3500 |   198K|     6   (0)| 00:00:01 | |*  1 |  HASH JOIN         |      |  3500 |   198K|     6   (0)| 00:00:01 | |   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 | |   3 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------

--可以看出如果use_nl()里面包含1个表的时候,如果你仔细看2个执行计划都没有走nested loop.第1个使用MERGE JOIN,

--而第2个使用HASH JOIN,明显不对.提示无效.

3.测试2:

select /*+ use_nl(dept emp) */ * from emp,dept where emp.deptno=dept.deptno; select /*+ use_nl(emp dept) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan

--------------------------- Plan hash value: 3625962092 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 | |   1 |  NESTED LOOPS                |         |       |       |            |          | |   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 | |   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 | |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- --2者执行计划一样?可以发现这样写确实走nested loop.感觉这样写,内部有规则控制那个做驱动与被驱动表.

4.测试3:

select /*+ ordered use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno; Execution Plan ---------------------------------------------------------- Plan hash value: 3625962092 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 | |   1 |  NESTED LOOPS                |         |       |       |            |          | |   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 | |   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 | |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- --这样才是正确的,而且使用了2次.实际上你看文档:use_nl(dept) 里面的表作为被驱动表. --再看看如下执行计划:

select /*+ ordered use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;

select /*+ use_nl(emp) ordered */ * from emp,dept where emp.deptno=dept.deptno; Execution Plan ---------------------------------------------------------- Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |  3500 |   198K|     6   (0)| 00:00:01 | |*  1 |  HASH JOIN         |      |  3500 |   198K|     6   (0)| 00:00:01 | |   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 | |   3 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- --可以发现ordered放在前面后面,执行计划都一样.但是执行计划是hash join而不是nested loop.

5.我一般不喜欢使用ordered,而是喜欢leading.

select /*+ leading(dept,emp) use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno; Execution Plan ---------------------------------------------------------- Plan hash value: 4192419542 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |  3500 |   198K|  1360   (1)| 00:00:01 | |   1 |  NESTED LOOPS      |      |  3500 |   198K|  1360   (1)| 00:00:01 | |   2 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 | |*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------- --你可以发现这个cost=1360太高了,这个也许是前面使用/*+ use_nl(dept emp) */,/*+ use_nl(emp dept) */不选择的原因.

select /*+ leading(dept,emp) use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 | |   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 | |   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 | |*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 | |   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- --这个提示是错误,或者存在冲突的,use_nl() 里面的表作为被驱动表.可以发现执行计划走的MERGE JOIN.

select /*+ leading(emp dept) use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan --------------------------- Plan hash value: 3625962092 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 | |   1 |  NESTED LOOPS                |         |       |       |            |          | |   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 | |   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 | |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- --这样写正确.

--总之要控制执行计划,最好使用leading,use_nl()里面的表作为被驱动表.

--最后做一个例子:

select /*+ leading(dept emp) use_merge(emp) index(dept pk_dept) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 | |   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 | |   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 | |*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 | |   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 | ----------------------------------------------------------------------------------------

select /*+ leading(dept emp) use_merge(dept) index(dept pk_dept) */ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan ---------------------------------------------------------- Plan hash value: 4260967074 ------------------------------------------------------------------------------------------------ | Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                     |         |  3500 |   198K|     5   (0)| 00:00:01 | |*  1 |  HASH JOIN                           |         |  3500 |   198K|     5   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 | |   3 |    INDEX FULL SCAN                   | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 | |   4 |   TABLE ACCESS FULL                  | EMP     |    14 |   532 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------

--//其中的细节还是自己体会,感觉提示ordered,leading作为提示有优先级.要自己多做练习才行.