基於RULE的最佳化器（學習筆記），rule學習筆記

基於RULE的最佳化器（學習筆記），rule學習筆記
崔華《基於Oracle的sql最佳化學習筆記》
1.1 基於RULE的最佳化器

(1) CBO

(2)RBO

和CBO相比，RBO是有其明顯許可權的。在使用RBO的情況下，執行計畫一旦出了問題，很難對其做調整。另外，如果使用了RBO則目標SQL的寫法，甚至是目標SQL中所涉及的各個對象在該SQL文本中出現的先後順序都可能影響RBO執行計畫的選擇我，更糟糕的是，Oracle資料庫中很好的特性、功能不能再RBO中使用因為他們不能被RBO鎖支援。

 

只要出現如下情形之一，那麼即便修改了最佳化器模式或者使用了RULE Hnint，Oracle依然不會使用RBO（而是強制使用CBO）。

（1）  目標SQL中設計的對此昂有IOT（indexOrganized Table）。

（2）  目標SQL中設計的對象有分區表。

（3）  使用了平行查詢或者並行DML。

（4）  使用了星形串連

（5）  使用了hash串連

（6）  使用了索引快速全掃描

（7）  使用了函數索引

（8）  …...

這種情況下我們是很難對RBO選擇的執行計畫做調整的，其中一個十分關鍵的原因是不能使用hint。因為如果在目標SQL中使用了Hint，就意味著自動啟動了CBO，即Oracle會以CBO來解析Hint的目標SQL。這裡僅有兩個例外，就是RULE Hint和SRIVING_SITE Hint，他可以在RBO下使用並且不自動啟用CBO。

 

那麼是不是在使用RBO的情況下就沒有辦法對執行計畫進行調整了呢？？

當然不是這樣，只是這種情況下我們的調整手段非常有限。其中的一種可行的方法就是等價改寫目標SQL，比如在目標SQL的where條件中對NUMBER或者DATE類型的列上加上0（如果是varchar2或者char類型，可以加上一個Null 字元，例如||’’）,這樣就可以讓原本可以走的索引現在走不了。對於包含多表串連的目標sql而言，這種改變甚至可以影響表串連的順序，進而就可以實現在使用RBO的情況下對該目標SQL的執行計畫作出調整的目的。

 

 

但是如果出現了兩條或者兩條以上的等級值相同的執行路徑的情況，那麼RBO此時該如何選擇呢？很簡單，此時RBO會依據目標SQL中所涉及的相關對象在資料字典緩衝（Data Dictionary cache）中的緩衝順序和目標SQL中所涉及的各個對象在目標SQL文本中出現的先後順序來綜合判斷。這也就意味著我們還可以通過調整相關對象在資料字典中的緩衝順序，改變目標SQL中所涉及的各個對象在該SQL文本中出現的先後順序來調整其執行計畫。

 

實驗：使用RBO的情況下，對目標SQL的執行計畫調整。

 

create table emp_temp as select * from  emp; create index idx_mgr_temp on emp_temp(mgr); create index idx_deptno_temp on emp_temp(deptno);  select * from emp_temp where  mgr>100 and deptno>100;

 

###在當前session中配置最佳化器模式為RULE

 

alter session set optimizer_mode='RULE';

 

SET AUTOT TRACE EXP

 

 

假如我們發現走索引IDX_DEPTNO_TEMP不如走索引IDX_MGR_TEMP的執行效率高，或者我們想讓RBO走索引IDX__MGR_TEMP，那麼我們該如何讓做？？

 

可以加一個0不讓它走索引：

 

Select * from  emp_temp where mgr>100 anddeptno+0>100; 08:28:11 scott@felix SQL>Select * from  emp_temp where mgr>100 anddeptno+0>100; no rows selected  Execution Plan----------------------------------------------------------Plan hash value: 2973289657 ----------------------------------------------------| Id  |Operation                   | Name         |----------------------------------------------------|   0 |SELECT STATEMENT            |              ||*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     ||*  2 |   INDEX RANGE SCAN          | IDX_MGR_TEMP |---------------------------------------------------- Predicate Information (identified by operationid):---------------------------------------------------    1 -filter("DEPTNO"+0>100)   2 -access("MGR">100) Note-----   - rulebased optimizer used (consider using cbo)  Statistics----------------------------------------------------------         1  recursive calls         0  db block gets         2  consistent gets         0  physical reads         0  redo size       799  bytes sent via SQL*Net toclient       512  bytes received via SQL*Netfrom client         1  SQL*Net roundtrips to/fromclient         0  sorts (memory)         0  sorts (disk)         0  rows processed 09:58:53 scott@felix SQL> 

我們可以看到已經改變了執行計畫。

 

 

剛才我先建立索引IDX_MGR_TEMP，再建立索引IDX_DEPTNO_TEMP，所以IDX_MGR_TEMP先緩衝，在緩衝IDX_DEPTNO_TEMP這種情形下RBO選擇的是走對索引IDX_DEPTNO_TEMP的索引範圍掃，如果反過來呢？？

先刪除索引IDDX_MGR_TEMP

 

09:58:53 scott@felix SQL>DROP INDEXIDX_MGR_TEMP; Index dropped.再建立該索引： 10:15:20 scott@felix SQL>create indexidx_mgr_temp on emp_temp(mgr); Index created.  10:16:05 scott@felix SQL>Select * from  emp_temp where mgr>100 and deptno>100; no rows selected  Execution Plan----------------------------------------------------------Plan hash value: 2973289657 ----------------------------------------------------| Id  |Operation                   | Name         |----------------------------------------------------|   0 |SELECT STATEMENT            |              ||*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP     ||*  2 |   INDEX RANGE SCAN          | IDX_MGR_TEMP |---------------------------------------------------- Predicate Information (identified by operationid):---------------------------------------------------    1 -filter("DEPTNO">100)   2 -access("MGR">100) Note-----   - rulebased optimizer used (consider using cbo)  Statistics----------------------------------------------------------         3  recursive calls         0  db block gets         6  consistent gets         0  physical reads          0 redo size       799  bytes sent via SQL*Net toclient       512  bytes received via SQL*Netfrom client         1  SQL*Net roundtrips to/fromclient         0  sorts (memory)         0  sorts (disk)         0  rows processed 10:17:48 scott@felix SQL>

 

如上實驗可知，當目標SQL有兩條或者兩條以上的執行路徑的登記相同時，我們確實可以通過調整相關對象在資料字典緩衝中的緩衝順序來影響RBO對於執行計畫的選擇。

 

 

Create table emp_temp1 as select * from emp; 10:25:30 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t1,emp_temp1 t210:26:27  2  Where t1.empno=t2.empno; 14 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1323777565 -----------------------------------------| Id  |Operation           | Name      |-----------------------------------------|   0 |SELECT STATEMENT    |           ||   1 |  MERGE JOIN         |           ||   2 |   SORT JOIN         |           ||   3 |    TABLE ACCESS FULL| EMP_TEMP1 ||*  4 |   SORT JOIN         |           ||   5 |    TABLE ACCESS FULL| EMP_TEMP  |----------------------------------------- Predicate Information (identified by operationid):---------------------------------------------------    4 -access("T1"."EMPNO"="T2"."EMPNO")      filter("T1"."EMPNO"="T2"."EMPNO") Note-----   - rulebased optimizer used (consider using cbo)  Statistics----------------------------------------------------------         1  recursive calls         0  db block gets         6  consistent gets         4  physical reads         0  redo size       778  bytes sent via SQL*Net toclient       524  bytes received via SQL*Netfrom client         2  SQL*Net roundtrips to/fromclient         2  sorts (memory)         0  sorts (disk)        14  rows processed 10:31:02 scott@felix SQL>

我們發現emp_tem1p作為驅動表，

注意：排序合并串連，從嚴格意義上來講，並沒有驅動表和被驅動表的概念，這裡只是為了方便闡述而人為的給排序合并串連添加了概念。

 

 

那麼改變該SQL的SQL文本中的位置會怎麼樣呢？？

 Select t1.mgr,t2.deptno from emp_temp1  t2,emp_temp  t1 Where t1.empno=t2.empno; 10:31:02 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp1 t2,emp_temp  t110:36:15  2   Where t1.empno=t2.empno;       MGR     DEPTNO---------- ----------     7902         20     7698         30     7698         30     7839         20     7698         30     7839         30     7839         10     7566         20                   10     7698         30     7788         20     7698         30     7566         20     7782         10 14 rows selected.  Execution Plan----------------------------------------------------------Plan hash value: 2135683657 -----------------------------------------| Id  |Operation           | Name      |-----------------------------------------|   0 |SELECT STATEMENT    |           ||   1 |  MERGE JOIN         |           ||   2 |  SORT JOIN         |           ||   3 |   TABLE ACCESS FULL| EMP_TEMP  ||*  4 |  SORT JOIN         |           ||   5 |   TABLE ACCESS FULL| EMP_TEMP1 |----------------------------------------- Predicate Information (identified by operationid):---------------------------------------------------    4 -access("T1"."EMPNO"="T2"."EMPNO")      filter("T1"."EMPNO"="T2"."EMPNO") Note-----   - rulebased optimizer used (consider using cbo)  Statistics----------------------------------------------------------         1  recursive calls          0 db block gets         6  consistent gets         0  physical reads         0  redo size       778  bytes sent via SQL*Net toclient       524  bytes received via SQL*Netfrom client         2  SQL*Net roundtrips to/fromclient          2 sorts (memory)         0  sorts (disk)        14  rows processed 10:36:15 scott@felix SQL>

 

我們發現，驅動表變為emp_temp了。

 

說明，當目標SQL有兩條或者兩條以上的執行路徑的等級值相同時，我們確定可以通過改變目標SQL中所涉及的各個對象在該SQL文本中出現的先後順序來影響RBO對其執行計畫的選擇。

 

 

注意：以上幾個實驗都是就RBO模式。RBO不支援HASH join。

 

 

Emp有主鍵，emp_temp上empno列沒有索引。

10:43:50 scott@felix SQL>Select t1.mgr,t2.deptno from emp  t1,emp_temp t210:44:00   2   Where t1.empno=t2.empno;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 367190759-------------------------------------------------| Id  | Operation                    | Name     |-------------------------------------------------|   0 | SELECT STATEMENT             |          ||   1 |  NESTED LOOPS                |          ||   2 |   NESTED LOOPS               |          ||   3 |    TABLE ACCESS FULL         | EMP_TEMP ||*  4 |    INDEX UNIQUE SCAN         | PK_EMP   ||   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |-------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("T1"."EMPNO"="T2"."EMPNO")Note-----   - rule based optimizer used (consider using cbo)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets         22  consistent gets          0  physical reads          0  redo size        778  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         14  rows processed10:44:00 scott@felix SQL>

 

修改該SQL文本的表順序：

Select t1.mgr,t2.deptno from emp_temp t2,emp  t1 Where t1.empno=t2.empno; 10:44:00 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t2, emp t110:49:57  2    Where t1.empno=t2.empno; 14 rows selected.  Execution Plan----------------------------------------------------------Plan hash value: 367190759 -------------------------------------------------| Id  |Operation                    | Name     |-------------------------------------------------|   0 |SELECT STATEMENT             |          ||   1 |  NESTED LOOPS                |          ||   2 |   NESTED LOOPS               |         ||   3 |   TABLE ACCESS FULL         |EMP_TEMP ||*  4 |    INDEX UNIQUE SCAN         | PK_EMP   ||   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |------------------------------------------------- Predicate Information (identified by operationid):---------------------------------------------------    4 -access("T1"."EMPNO"="T2"."EMPNO") Note-----   - rulebased optimizer used (consider using cbo)  Statistics----------------------------------------------------------         1  recursive calls         0  db block gets        22  consistent gets         0  physical reads         0  redo size       778  bytes sent via SQL*Net toclient       524  bytes received via SQL*Netfrom client         2  SQL*Net roundtrips to/fromclient         0  sorts (memory)         0  sorts (disk)        14  rows processed 10:49:57 scott@felix SQL>

 

可以看出執行計畫走的是嵌套迴圈，且驅動表依然是表EMP_TEMP，這就證明：如果RBO僅憑目標SQL各條執行路徑等級值的大小就可以選擇出執行計畫。那麼無論怎麼調整相關對象在該SQL的SQL文本中的位置，對於該SQL最終的執行計畫都不會有任何影響。