[20190430]注意sql hint写法.txt

[20190430]注意sql hint写法.txt --//链接:https://www.bobbydurrettdba.com/2019/04/16/check-your-hints-carefully/ SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> @ sqlhint cache old   1: select * from V$SQL_HINT where name like upper('%&1%') new   1: select * from V$SQL_HINT where name like upper('%cache%') NAME             SQL_FEATURE     CLASS        INVERSE         TARGET_LEVEL   PROPERTY VERSION  VERSION_OUTLINE ---------------- --------------- ------------ --------------- ------------ ---------- -------- ---------------- CACHE_CB         QKSFM_CBO       CACHE_CB     NOCACHE                    4        256 8.1.5 CACHE            QKSFM_EXECUTION CACHE        NOCACHE                    4        256 8.1.0 NOCACHE          QKSFM_EXECUTION CACHE        CACHE                      4        256 8.1.0 CACHE_TEMP_TABLE QKSFM_ALL       CACHE        NOCACHE                    4        256 8.1.5 RESULT_CACHE     QKSFM_EXECUTION RESULT_CACHE NO_RESULT_CACHE            2          0 11.1.0.6 NO_RESULT_CACHE  QKSFM_EXECUTION RESULT_CACHE RESULT_CACHE               2          0 11.1.0.6 6 rows selected. SCOTT@book> select /*+ result cache */ * from dept ;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON --//注意中间没有"_". SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  5sm6uuf1wtunm, child number 0 ------------------------------------- select /*+ result cache */ * from dept Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |   1 |  TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@SEL$1 SCOTT@book> select /*+ result_cache */ * from dept ;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dh09kah6tkdjy, child number 0 ------------------------------------- select /*+ result_cache */ * from dept Plan hash value: 3383998547 -------------------------------------------------------------------------------------------------- | Id  | Operation          | Name                       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | -------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |                            |        |       |     3 (100)|          | |   1 |  RESULT CACHE      | gsg6g7y8rvxaydjyjh2g2yr21r |        |       |            |          | |   2 |   TABLE ACCESS FULL| DEPT                       |      4 |    80 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / DEPT@SEL$1 --//链接:http://www.oaktable.net/content/avoid-compound-hints-better-hint-reporting-19c,也提到一种情况: --//我没有19c. SCOTT@book> select /*+ use_nl(emp dept) */ * from dept ,emp where dept.deptno=emp.deptno; ... SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  gk5d852xxj4b5, child number 0 ------------------------------------- select /*+ use_nl(emp dept) */ * from dept ,emp where dept.deptno=emp.deptno Plan hash value: 4192419542 ---------------------------------------------------------------------------- | Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |        |       |    10 (100)|          | |   1 |  NESTED LOOPS      |      |     14 |   812 |    10   (0)| 00:00:01 | |   2 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 | |*  3 |   TABLE ACCESS FULL| EMP  |      4 |   152 |     2   (0)| 00:00:01 | ---------------------------------------------------------------------------- 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): ---------------------------------------------------    3 - filter("DEPT"."DEPTNO"="EMP"."DEPTNO") --//注意看执行计划,实际上主驱动dept表.只有写成如下: SCOTT@book> select /*+ use_nl(emp ) */ * from dept ,emp 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 |   812 |     6   (0)| 00:00:01 |  1048K|  1048K|  662K (0)| |   2 |   TABLE ACCESS FULL| EMP  |     14 |   532 |     3   (0)| 00:00:01 |       |       |          | |   3 |   TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |       |       |          | ------------------------------------------------------------------------------------------------------- --//这样emp才能作为驱动表. --//最后一种情况是我经常犯的错误..