[20200801]sql hint衝突.txt

[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.