[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才能作为驱动表.

--//最后一种情况是我经常犯的错误..

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2642961/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2642961/