[20200801]sql hint冲突.txt

--//昨天看崔华<基于oracle的sql优化>,发现自己以前对于这个问题没理解清楚.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.测试:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.

SCOTT@test01p> select /*+ use_hash(dept) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
EMPNO ENAME      DNAME
----- ---------- ----------
 7782 CLARK      ACCOUNTING
 7839 KING       ACCOUNTING
 7934 MILLER     ACCOUNTING
 7566 JONES      RESEARCH
 7902 FORD       RESEARCH
 7876 ADAMS      RESEARCH
 7369 SMITH      RESEARCH
 7788 SCOTT      RESEARCH
 7521 WARD       SALES
 7844 TURNER     SALES
 7499 ALLEN      SALES
 7900 JAMES      SALES
 7698 BLAKE      SALES
 7654 MARTIN     SALES
14 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fcvzctv91801v, child number 0
-------------------------------------
select /*+ use_hash(dept) */ emp.empno,emp.ename,dept.dname from
emp,dept where dept.deptno=emp.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      11 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |     14 |   364 |     6  (17)| 00:00:01 |     14 |00:00:00.01 |      11 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |    52 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      4 |     14 |   182 |     4  (25)| 00:00:01 |     14 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |   182 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / DEPT@SEL$1
   5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
32 rows selected.

--//可以发现sql hint无效,选择merge join.
--//做hash连接,表dept无法做被驱动表.因为一般做hash连接都是小结果集做驱动表,大结果集做被驱动表.
--//sql语句中emp的结果集大,而dept的结果集小.而只有加入leading提示固定.

3.继续:
SCOTT@test01p> select /*+ leading(emp dept) use_hash(dept) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
EMPNO ENAME      DNAME
----- ---------- --------------------
 7782 CLARK      ACCOUNTING
...
14 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2mgqptn3ym690, child number 0
-------------------------------------
select /*+ leading(emp dept) use_hash(dept) */
emp.empno,emp.ename,dept.dname from emp,dept where
dept.deptno=emp.deptno
Plan hash value: 1123238657
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |   364 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |      15 |  1650K|  1650K| 1048K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |   182 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    52 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
29 rows selected.
--//OK.

SCOTT@test01p> select /*+ use_hash(dept emp) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
...

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0vs9jmh173bu1, child number 0
-------------------------------------
select /*+ use_hash(dept emp) */ emp.empno,emp.ename,dept.dname from
emp,dept where dept.deptno=emp.deptno

Plan hash value: 615168685

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |   364 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |      15 |  1695K|  1695K| 1070K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    52 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |   182 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

SCOTT@test01p> select /*+ use_hash(emp dept) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  58rkm699up0gv, child number 0
-------------------------------------
select /*+ use_hash(emp dept) */ emp.empno,emp.ename,dept.dname from
emp,dept where dept.deptno=emp.deptno
Plan hash value: 615168685
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |   364 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |      15 |  1695K|  1695K| 1070K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    52 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |   182 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       8 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--//无论/*+ use_hash(dept emp) */ ,/*+ use_hash(emp dept) */ 提示,dept仅仅做驱动表.
--//如果看执行计划的Outline Data.
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DEPT"@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
      USE_HASH(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

--//提示use_NL,use_merge也存在类似的情况,里面的参数是被驱动表.优化时最好配合leading提示.
--//避免一些歧义性.同时也要注意避免提示冲突.比如:

SCOTT@test01p> select /*+ leading(emp dept) use_nl(emp) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
Plan hash value: 1123238657
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN         |      |     14 |   364 |     6   (0)| 00:00:01 |  1572K|  1572K| 1033K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |     14 |   182 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |      4 |    52 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

--//提示leading emp在前,dept在后,而use_nl指定emp作为被驱动表,存在冲突.执行计划并不走nested loop.