[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作为提示有优先级.要自己多做练习才行.
