[20160730]12c pdb與cdb 光标不共享.txt

[20160730]hint 沖突.txt

--昨天别人優化加提示無效,問我為什麼無效?我一般認為這種情況稱為hint 沖突.

--通過例子來說明,我測試會使用ordered,我一般不喜歡使用ordered提示,通過例子來說明.

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

set autot traceonly

select * from emp,dept where emp.deptno=dept.deptno;

--不加提示,預設執行計劃如下:

Execution Plan

----------------------------------------------------------

Plan hash value: 844388907

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT             |         |  3500 |   198K|     5   (0)| 00:00:01 |

|   1 |  MERGE JOIN                  |         |  3500 |   198K|     5   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |         |    14 |   532 |     3   (0)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

2.測試1:

--如果你使用use_nl提示,裡面僅僅包含1個表,按照文檔介紹,作為被驅動表:

select /*+ use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;

--------------------------

select /*+  use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;

Plan hash value: 1123238657

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT   |      |  3500 |   198K|     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN         |      |  3500 |   198K|     6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 |

--可以看出如果use_nl()裡面包含1個表的時候,如果你仔細看2個執行計劃都沒有走nested loop.第1個使用MERGE JOIN,

--而第2個使用HASH JOIN,明顯不對.提示無效.

3.測試2:

select /*+ use_nl(dept emp) */ * from emp,dept where emp.deptno=dept.deptno;

select /*+ use_nl(emp dept) */ * from emp,dept where emp.deptno=dept.deptno;

---------------------------

Plan hash value: 3625962092

|   0 | SELECT STATEMENT             |         |  3500 |   198K|    17   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |       |       |            |          |

|   2 |   NESTED LOOPS               |         |  3500 |   198K|    17   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |   250 |  5000 |     1   (0)| 00:00:01 |

--2者執行計劃一樣?可以發現這樣寫确實走nested loop.感覺這樣寫,内部有規則控制那個做驅動與被驅動表.

4.測試3:

select /*+ ordered use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;

--這樣才是正确的,而且使用了2次.實際上你看文檔:use_nl(dept) 裡面的表作為被驅動表.

--再看看如下執行計劃:

select /*+ ordered use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;

select /*+ use_nl(emp) ordered */ * from emp,dept where emp.deptno=dept.deptno;

--可以發現ordered放在前面後面,執行計劃都一樣.但是執行計劃是hash join而不是nested loop.

5.我一般不喜歡使用ordered,而是喜歡leading.

select /*+ leading(dept,emp) use_nl(emp) */ * from emp,dept where emp.deptno=dept.deptno;

Plan hash value: 4192419542

|   0 | SELECT STATEMENT   |      |  3500 |   198K|  1360   (1)| 00:00:01 |

|   1 |  NESTED LOOPS      |      |  3500 |   198K|  1360   (1)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |  1000 | 20000 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     1   (0)| 00:00:01 |

--你可以發現這個cost=1360太高了,這個也許是前面使用/*+ use_nl(dept emp) */,/*+ use_nl(emp dept) */不選擇的原因.

select /*+ leading(dept,emp) use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;

--這個提示是錯誤,或者存在沖突的,use_nl() 裡面的表作為被驅動表.可以發現執行計劃走的MERGE JOIN.

select /*+ leading(emp dept) use_nl(dept) */ * from emp,dept where emp.deptno=dept.deptno;

--這樣寫正确.

--總之要控制執行計劃,最好使用leading,use_nl()裡面的表作為被驅動表.

--最後做一個例子:

select /*+ leading(dept emp) use_merge(emp) index(dept pk_dept) */ * from emp,dept where emp.deptno=dept.deptno;

select /*+ leading(dept emp) use_merge(dept) index(dept pk_dept) */ * from emp,dept where emp.deptno=dept.deptno;

Plan hash value: 4260967074

------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT                     |         |  3500 |   198K|     5   (0)| 00:00:01 |

|*  1 |  HASH JOIN                           |         |  3500 |   198K|     5   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT    |  1000 | 20000 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN                   | PK_DEPT |  1000 |       |     1   (0)| 00:00:01 |

|   4 |   TABLE ACCESS FULL                  | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

--//其中的細節還是自己體會,感覺提示ordered,leading作為提示有優先級.要自己多做練習才行.