概述
[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/
最后
以上就是认真太阳为你收集整理的[20190430]注意sql hint写法.txt的全部内容,希望文章能够帮你解决[20190430]注意sql hint写法.txt所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错,欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复