[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.如果大家做最佳化加入提示有什麼好方法,歡迎加入討論.

[20210120]提示加入註解.txt

相關文章