[20210120]提示加入注解.txt
--//经常做sql语句优化,需要手工加入各种提示,但是有时候要取消很麻烦,我个人喜欢直接加入一些11,12之类的字符在提示前.
--//看崔华<基于Oracle的SQL优化>,看到许多例子自己根据工作需要自己测试看看.
--//通过例子说明:
1.环境:
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
2.测试:
select
/*+
gather_plan_statistics
use_hash(emp)
*/
* from dept,emp 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 | 12 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 812 | 6 (0)| 00:00:01 | 14 |00:00:00.01 | 12 | 1321K| 1321K| 1041K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
select
/*+
comment gather_plan_statistics
use_hash(emp)
*/
* from dept,emp where dept.deptno=emp.deptno;
--//执行计划如下:
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
--//加入comment,这个是oracle的关键字,而后面的use_hash(emp)也无效了.我感觉有点奇怪.也就是这样的方式取消整个提示.
--//这个倒是不错取消整个提示的好方式.
--//也可以简单地使用,替换comment.
select
/*+
, gather_plan_statistics
use_hash(emp)
*/
* from dept,emp where dept.deptno=emp.deptno;
--//执行计划如下:
Plan hash value: 844388907
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
--//如果想取消某个提示,可以直接在提示前加入--,修改如下:
select
/*+
--gather_plan_statistics
use_hash(emp)
*/
* from dept,emp where dept.deptno=emp.deptno;
--//执行计划如下:
-------------------------------------------------------------------------------------------------------
| 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 | 1321K| 1321K| 1074K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------
select
/*+
gather_plan_statistics
--use_hash(emp)
*/
* from dept,emp 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 | 8 | | | |
| 1 | MERGE JOIN | | 1 | 14 | 812 | 6 (17)| 00:00:01 | 14 |00:00:00.01 | 8 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 80 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 1 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 532 | 4 (25)| 00:00:01 | 14 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
总结:
1.取消整个提示,在最前面加入, 或者comment.
2.另外我个人主张一个提示写一行,特别在测试与优化时.
3.取消单个提示,可以在前面加入--.
4.如果大家做优化加入提示有什么好方法,欢迎加入讨论.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2751251/，如需转载，请注明出处，否则将追究法律责任。
