Oracle最佳化器和執行計畫

標籤：style   c   int   a   資料   使用   

1. 最佳化器(Optimizer)是sql分析和執行的最佳化工具，它負責制定sql的執行計畫，負責保證sql執行效率最高，比如決定oracle以什麼方式訪問資料，全表掃描(full table scan)還是索引範圍(index range scan)掃描，還是全索引快速掃描(index fast full scan, INDEX_FFS),對於表關聯查詢，是用什麼方式關聯。有2種最佳化器，RBO和CBO，從oracle 10g開始，RBO已經被棄用，但是仍可以通過hint的方式使用。

2. RBO執行機制：在最佳化器裡嵌入若干種規則，執行的sql符合什麼規則，則按照規則制定執行計畫，這些規則是按照優先順序排列。 select /*+ rule */ * from t where id=1; 使用hint方式強制使用RBO最佳化器執行。

3. CBO執行機制：擷取所有執行計畫相關資訊，通過對這些資訊的分析，最後得出一個代價最小的執行計畫作為最終的執行計畫。

4. 即使在表，索引沒有被分析的時候，oracle仍會使用CBO(從oracle 10g開始，RBO已經被棄用), 此時oracle會使用動態採樣，在分析sql的時候，動態收集表，索引上的一些資料區塊，使用這些資料區塊的資訊及字典中關於這些對象的資訊來計算出執行計畫的代價，進而挑選出最優的執行計畫。動態採樣只有在sql執行的第一次，即硬分析階段使用，後續的軟分析不再使用動態採樣，直接使用第一次sql硬分析出的執行計畫。

5. CBO最佳化器有2種可選的運行模式：

• FIRST_ROWS(n): oracle在執行sql時，優先考慮將結果集中的前n條記錄以最快的速度反饋回來，其他的結果不需要同時返回。這種需求在搜尋或分頁經常看到。

select /*+ first_rows(10) */ b.x, b.y

from (select /*+ first_rows(10) */ a.*, rownum rnum

from (select /*+ first_rows(10) */ * from t order by x) a where rownum<=20)

b where rnum>=10;

• ALL_ROWS:將sql執行完畢將結果集全部返回。

select /*+ all_rows */ *

from (select /*+ all_rows */ a.*, rownum r

from (select /*+ all_rows */ owner, object_name, created from t where owner=‘sys‘ order by object_name) a where rownum<=20)

where r>=10;

6. 執行計畫中的基數(Cardinality): 執行計畫每一步操作中，Card的值表示CBO預期從一個行源row source返回的記錄數，這個行源可能是一個表，一個索引，也可能是一個子查詢。

執行計畫中card就是Cardinality的縮寫，在10g以後，card被rows替換，表示CBO估算當前操作預期擷取的記錄數。Cardinality的值對應CBO做出正確的執行計畫至關重要。

/*+ dynamic_sampling(t 0) cardinality(t 10000) */ 在未分析資料的前提下，禁止動態採樣並告訴CBO從t表將擷取到10000條記錄。

7. 執行計畫可以使用如下方式得到：

• explain plan for 如：1.explain plan for select * from t; 2. select * from table(dbms_xplan.display);
• sqlplus命令 set autotrace on; set autotrace trace exp, stat;
• 第三方提供的工具，如Toad，PL/SQL developer

8. 執行計畫中的access表示這個謂詞條件的值將會影響資料的訪問路徑(表還是索引)，filter表示謂詞條件的值並不會影響資料的訪問路徑，只起到過濾的作用。

9. 如果表沒有做分析，那麼CBO可以通過動態採樣的方式獲得分析資料，也可以獲得準確的執行計畫；如果表分析過，但是分析資訊過舊，這時候CBO不會使用動態採樣，而是使用這些舊的分析資料，從而可能導致錯誤的執行計畫。