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