CBO基礎概念

標籤：

CBO基礎概念

CBO：評估 I/O,CPU,網路（DBLINK）等消耗的資源成本得出

一、cardinality

cardinality：集合中包含的記錄數。實際CBO評定目標SQL執行具體步驟的記錄數，cardinality和成本是相關的，cardinality越大，執行步驟中的成本就越大

二、Selectivity

Selectivity ：謂詞的過濾條件返回的結果的行數占未加謂詞過濾條件的行數

公式：=

範圍0-1，值越小，說明 選擇性越好 返回的cardinality 越小；值越大，選擇性越差，返回的cardinality 越大。

未加任何謂詞條件結果集： original cardinality，增加謂詞條件為：computed cardinality。

computed cardinality=original cardinality*selectivity

在列上沒有NULL或者長條圖的情況下，selectivity取值

SELECTIVITY=

NUM_DISTINCT ：表示為列中不同值的個數

三、transitivity（可傳遞性）

CBO會對現有的sql語句做等價轉換，謂詞條件、欄位串連

最佳化器基礎概念

一、最佳化類型：RBO和CBO

（一）、RBO

RBO基於規則的最佳化器access paths優先順序:

• RBO Path 1: Single Row by Rowid
• RBO Path 2: Single Row by Cluster Join
• RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
• RBO Path 4: Single Row by Unique or Primary Key
• RBO Path 5: Clustered Join
• RBO Path 6: Hash Cluster Key
• RBO Path 7: Indexed Cluster Key
• RBO Path 8: Composite Index
• RBO Path 9: Single-Column Indexes
• RBO Path 10: Bounded Range Search on Indexed Columns
• RBO Path 11: Unbounded Range Search on Indexed Columns
• RBO Path 12: Sort Merge Join
• RBO Path 13: MAX or MIN of Indexed Column
• RBO Path 14: ORDER BY on Indexed Column
• RBO Path 15: Full Table Scan

注意在不違反如上優先順序的前提下，若有2個最佳化級一樣的索引可用，則RBO會選擇晚建的那個索引， 解決方案是重建你想要讓RBO使用的那個索引，或者使用CBO……..

（二）、optimizer_mode :最佳化器的模式

1、RULE：使用rbo的規則

2、CHOOSE:9i中預設值，SQL語句全部的對象中無統計資訊，使用RBO，其他使用CBO

3、FIRST_ROWS_n（n=1,10,100,1000）:CBO會把哪些能夠最快響應速度返回的成本設定為很小的值

4、ALL_ROWS：oracle10g 以後的預設值，會使用CBO來解析SQL，計算各個執行路徑中的成本值最佳的輸送量（最小的I/O和CPU資源）

二、結果集

執行計畫中row，CBO評估的cardinality

三、訪問資料的方法

（一）、訪問表的方法：全表掃描和rowid訪問

全表掃描：從高水位線下全部資料區塊讀取，DELETE不會下降高水位線

ROWID訪問:通過實體儲存體地址直接存取，rowid是個偽列，但是他能快速的定位的資料檔案、塊、行

[email protected]> select empno,rowid ,dbms_rowid.rowid_relative_fno(rowid)||‘_‘||dbms_rowid.rowid_block_number(rowid)||‘_‘||dbms_rowid.rowid_row_number(rowid) location_row from emp;     EMPNO ROWID              LOCATION_ROW---------- ------------------ ------------------------------      7369 AAASZHAAEAAAACXAAA 4_151_0      7499 AAASZHAAEAAAACXAAB 4_151_1      7521 AAASZHAAEAAAACXAAC 4_151_2      7566 AAASZHAAEAAAACXAAD 4_151_3      7654 AAASZHAAEAAAACXAAE 4_151_4      7698 AAASZHAAEAAAACXAAF 4_151_5      7782 AAASZHAAEAAAACXAAG 4_151_6      7788 AAASZHAAEAAAACXAAH 4_151_7      7839 AAASZHAAEAAAACXAAI 4_151_8      7844 AAASZHAAEAAAACXAAJ 4_151_9      7876 AAASZHAAEAAAACXAAK 4_151_10      7900 AAASZHAAEAAAACXAAL 4_151_11      7902 AAASZHAAEAAAACXAAM 4_151_12      7934 AAASZHAAEAAAACXAAN 4_151_13

（二）、索引訪問

索引訪問的成本：一部分時訪問相關的B樹索引的成本，另一個成本是回表的成本（根據索引中的rowid）

1、索引唯一性掃描（index unique scan）：建立是唯一索引，能建立唯一索引的一定要建立唯一索引

2、索引範圍掃描（index range scan）：謂詞條件中>、<等

3、索引全掃描（index full scan）：掃描目標索引中所有的塊的所有索引行。從最左的葉子節點讀到底，因為葉子是一個雙向鏈表

• 索引全掃描的執行結果是有有序的，並且是按照該索引的索引索引值列來排序，這也意味走索引全掃描能夠既達到排序的效果，又同時避免了對該索引的索引索引值列的真正的排序操作。
• 索引全掃描時有序的就決定了不能夠並存執行，索引全掃描時單塊讀
• oracle中能做索引全掃描的前提條件是目標索引至少有一個索引索引值列的屬性是NOT NULL

4、索引快速全掃描（INDEX FAST FULL SCAN）

索引全掃描類似，讀取所有葉子塊的索引行

與全索引掃描不同點

• 索引快速全掃描只適用於CBO
• 索引快速全掃描可以使用多塊讀，也可以並存執行
• 索引快速全掃描的執行結果不一定是有序的

5、索引跳躍式掃描（INDEX SKIP SCAN）

適用複合B數索引，謂詞中的過濾條件不是以索引前置列。

只是對前置列做distinct：如create index ind_1 ON emp(job,empno)

Select * from emp where empno=7499

如果job有兩個CLERK,SALESMAN

等同的語句

Select * from emp where job=‘CLERK‘ AND empno=7499UNION ALLSelect * from emp where job=‘SALESMAN‘ AND empno=7499

oracle中的索引跳躍式掃描僅僅適用那些目標索引前置列的distinct值數量較少、後續非前置列的可選擇性又非常好的情形，因為索引跳躍式掃描的執行效率一定會隨著目標索引前置列的distinct值數量的遞增而遞減

四、表串連

（一）、表串連順序

不管目標SQL中有多少個表做表串連，ORACLE在實際執行都是先做兩兩做表串連，在依次執行這樣的兩兩表串連過程，直到目標SQL中所有表都已經串連完畢。

表串連很重要的是驅動表（outer table）和被驅動表（inner table）。

（二）、表串連方法

• 嵌套迴圈串連（nested loop）

     1、如果驅動表所對應的驅動結果集的記錄數較少，同時在被驅動表的串連列上又存在唯一性索引（或者在被驅動表的串連列上存在選擇性很好的非唯一性索引），那麼此時使用嵌套迴圈串連的執行效率非常高。

     2、大表也可以作為驅動表，關鍵看大表的謂詞條件是否可以把驅動表的結果集的資料量下降下來

• 雜湊串連（hash join）

     1、雜湊串連不一定會排序，或者說大多數情況下都不需要排序

     2、雜湊串連的驅動表所對應的串連列的可選擇性應儘可能好，因為這個可選擇性會影響對應hash bucket中的記錄數，而hash bucket中的記錄數又會直接影響從該hash bucket中查詢匹配記錄的記錄。如果一個hash bucket裡所包含的記錄數過多，則可能或嚴重降低所對應雜湊串連的執行效率，此時典型的表現就是該雜湊串連執行了很多時間都沒有結束，資料庫所在資料庫伺服器上的CPU佔用率很高，但目標SQL所消耗的邏輯讀卻很低，因為此時大部分時間都耗費在了遍曆上述Hash Bucket裡的所有記錄上，而遍曆Hash Bucket裡的記錄這個動作發生在PGA的工作區裡，所以不耗費邏輯讀。

     3、雜湊串連只適用於CBO，它也只能用於等值串連條件（即使是雜湊反串連，Oracle實際上也是講其轉換成了等價的等值串連）。

     4、雜湊串連很適合小表和大表之間做串連且串連結果集的記錄數較多的情形，特別是在小表的串連列的可選擇性非常好的情況下，這時候雜湊串連的執行時間就可以近似看作和全表掃描那個大表所耗費的時間相當。

     5、當兩個表做雜湊串連時，如果在施加目標SQL中指定的謂詞條件（如果有的話）後得到的資料量較小的那個結果集所對應的hash table、 能夠完全容納在記憶體中（PGA的工作區），則此時的雜湊串連的執行效率會非常高

• 排序合并串連（merge jion）

    1、通常情況下，排序合并串連的執行效率會遠不如雜湊串連，但前者的使用範圍更廣，因為雜湊串連通常只能用於等值串連條件，而排序合并串連還能用於其他串連條件（<,<=,>,>=）

    2、通常情況下，排序合并串連並不適合OLTP類型的系統，器本質原因是因為對於OLTP類型的系統而言，排序很昂貴的操作，當然，如果能避免排序操作，那麼即使是OLTP類型的系統，也還是可以使用排序合并串連的。

    3、排序合并串連並不存在驅動表的概念

• 笛卡爾積串連（Cartesian join）

     1、無任何串連條件，在執行計畫中有cartesian，通常來說，有笛卡爾積，sql語句是有問題的

（三）、表串連的類型

1、內串連（Inner JOIN）

--使用=select * from emp a join dept b on a.deptno=b.deptno--joinselect * from emp a join dept b on a.deptno=b.deptno

2、外串連（outer join）

左串連--使用（+）select * from emp a,dept b where a.deptno=b.deptno(+)--使用left joinselect * from emp a left outer join dept b on a.deptno=b.deptno右串連--使用（+）select * from emp a,dept b where a.deptno(+)=b.deptno--使用left joinselect * from emp a right outer join dept b on a.deptno=b.deptno

外串連的驅動表以（+）對面的表，如以上左串連，驅動表為a，右串連的驅動表為b

3、反串連（anti join）

串連中not in、not exists 、<> 

sql A：select * from emp a WHERE A.DEPTNO NOT  IN (SELECT DEPTNO FROM dept b where a.deptno=b.deptno)sql B：select * from emp a WHERE NOT EXISTS (SELECT 1 FROM dept b where a.deptno=b.deptno)sql C：select * from emp a WHERE A.DEPTNO <> all (SELECT DEPTNO FROM dept b where a.deptno=b.deptno)

4、半串連（semi join）

串連中用in，exists，any

select * from emp a WHERE A.DEPTNO   IN (SELECT DEPTNO FROM dept b where a.deptno=b.deptno)select * from emp a WHERE  EXISTS (SELECT 1 FROM dept b where a.deptno=b.deptno)select * from emp a WHERE A.DEPTNO =any(SELECT DEPTNO FROM dept b where a.deptno=b.deptno)

5、星形串連

在OLTP基本不會用到，在OLAP中用到

 

參考：崔華《基於ORACLE的SQL最佳化》

CBO基礎概念