ITPub博客

首页 > 数据库 > Oracle > [20210120]提示加入注解.txt

[20210120]提示加入注解.txt

原创 Oracle 作者:lfree 时间:2021-01-20 09:18:48 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643924