使用hint改变SQL执行计划的方法

今天来做一个使用hint来改变SQL执行计划的测试

[root@ora10g ~]# su - oracle

[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 3月 30 23:10:33 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@ora10g> startup

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size    1220384 bytes

Variable Size  335544544 bytes

Database Buffers  184549376 bytes

Redo Buffers    2973696 bytes

Database mounted.

Database opened.

SCOTT@ora10g> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SYS@ora10g> conn scott/tiger

Connected.

--开启跟踪执行计划（仅计划）

SCOTT@ora10g> set autot traceonly explain

SCOTT@ora10g> set line 130 pages 130

--强制使用hash连接

SCOTT@ora10g> select /*+ use_hash(e d)*/ ename,loc from emp e ,dept d where e.deptno=d.deptno;

Execution Plan

----------------------------------------------------------

Plan hash value: 615168685

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |  |    14 |   280 |7  (15)| 00:00:01 |

|*  1 |  HASH JOIN   |  |    14 |   280 |7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |4 |    44 |3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")

SCOTT@ora10g> select /*+use_hash(d e)*/ ename,loc from emp e ,dept d where e.deptno=d.deptno;

Execution Plan

----------------------------------------------------------

Plan hash value: 615168685

---------------------------------------------------------------------------

| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |  |    14 |   280 |7  (15)| 00:00:01 |

|*  1 |  HASH JOIN   |  |    14 |   280 |7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |4 |    44 |3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")

注意：这里在use_hash中的表的顺序前后颠倒输入，并不影响执行计划的先后顺序

--强制使用merge连接

SCOTT@ora10g> select /*+ use_merge(e d)*/ ename,loc from emp e ,dept d where e.deptno=d.deptno;

Execution Plan

----------------------------------------------------------

Plan hash value: 844388907

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   280 |     6(17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   280 |     6(17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   126 |     4(25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   126 |     3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("E"."DEPTNO"="D"."DEPTNO")

       filter("E"."DEPTNO"="D"."DEPTNO")

--强制使用nested loops连接

SCOTT@ora10g> select /*+ use_nl(e d)*/ ename,loc from emp e ,dept d where e.deptno=d.deptno;

Execution Plan

----------------------------------------------------------

Plan hash value: 351108634

----------------------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    14 |   280 |     4 (0)| 00:00:01 |

|   1 |  NESTED LOOPS     |       |    14 |   280 |     4 (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL     | EMP     |    14 |   126 |     3 (0)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1 (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN     | PK_DEPT |     1 |       |     0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("E"."DEPTNO"="D"."DEPTNO")

--强制使用全表扫描

SCOTT@ora10g> select /*+ full(emp)*/ * from emp;

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

--强制使用主键而不使用全表扫描

SCOTT@ora10g> select /*+ index(emp pk_emp) */* from emp; 

Execution Plan

----------------------------------------------------------

Plan hash value: 4170700152

--------------------------------------------------------------------------------------

| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |     |  14 | 518 |   2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |  14 | 518 |   2   (0)| 00:00:01 |

|   2 |  INDEX FULL SCAN    | PK_EMP |  14 |     |   1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

--强制使用并行，提高全表扫描效率

SCOTT@ora10g> select /*+ full(emp) parallel(emp 4)*/* from emp; 

Execution Plan

----------------------------------------------------------

Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------

| Id  | Operation     | Name| Rows| Bytes | Cost (%CPU)| Time|    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     ||    14 |   518 |     2   (0)| 00:00:01 | ||     |

|   1 |  PX COORDINATOR      ||||     || | |     |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 |P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR ||    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |     |

|   4 |    TABLE ACCESS FULL| EMP|    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |     |

--------------------------------------------------------------------------------------------------------------

SCOTT@ora10g> select /*+ parallel(emp 4)*/* from emp; 

Execution Plan

----------------------------------------------------------

Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------

| Id  | Operation     | Name| Rows| Bytes | Cost (%CPU)| Time|    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     ||    14 |   518 |     2   (0)| 00:00:01 | ||     |

|   1 |  PX COORDINATOR      ||||     || | |     |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR ||    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |     |

|   4 |     TABLE ACCESS FULL| EMP|    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |     |

--------------------------------------------------------------------------------------------------------------

注意：即使没有指定full关键词对emp进行全表扫描，只要采用并行方式扫描，就会以全表扫描的方式进行

SCOTT@ora10g> select /*+ parallel(emp 2)*/* from emp; 

Execution Plan

----------------------------------------------------------

Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------

| Id  | Operation     | Name| Rows| Bytes | Cost (%CPU)| Time|    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     ||    14 |   518 |     2   (0)| 00:00:01 | ||     |

|   1 |  PX COORDINATOR      ||||     || | |     |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR ||    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |     |

|   4 |     TABLE ACCESS FULL| EMP|    14 |   518 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |     |

--------------------------------------------------------------------------------------------------------------

由于并行度和CPU有关，由于我的机器是双核CPU，因此即使设置并行度为4，仍然和并行度2的执行计划是一致的

SCOTT@ora10g> select /*+ parallel(emp 1)*/* from emp; 

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

再来看，使用并行度为1的情况，并行度为1即没有开启并行，因此执行计划中并没有体现出并行的效果

--不使用hint

SCOTT@ora10g> select * from emp; 

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

当不使用hint时，并且当访问的数据为所有记录时，优化器默认采用的访问方式就是全表扫描，可以看到，和上面的并行度为1的写法对应的执行计划是一致的

hint的用法并限于以上这些，还有很多其他的用法，本次测试主要和多表连接相关。