讀書筆記-《基於Oracle的SQL優化》-第一章-2

CBO優化器的基本概念：

可傳遞性：

1、簡單謂詞傳遞

t1.c1=t2.c1 and t1.c1=10，Oracle會自動將t2.c1=10的條件新增。

2、連線謂詞傳遞

t1.c1=t2.c1 and t2.c1=t3.c1，Oracle會自動將t1.c1=t3.c1的條件新增。

3、外連線謂詞傳遞

t1.c1=t2.c1(+) and t1.c1=10，Oracle會自動將t2.c1(+)=10的條件新增。

CBO的侷限性：

1、CBO會預設目標SQL語句where條件中出現的各個列之間是獨立的，沒有關聯關係。

2、CBO會假設所有的目標SQL都是單獨執行的，並且互不干擾。

不考慮SQL執行已經快取到Buffer Cache，下次執行不需要訪問物理IO到磁碟讀索引葉子塊、資料塊等，高估用索引的成本。

3、CBO對直方圖統計資訊有諸多限制。

      Oracle 12c之前，Frequency型別的直方圖對應的Bucket數量不能超過254，如果目標列的distinct值的數量超過254，Oracle就會使用Height Balanced型別的直方圖。對於Height Balanced型別的直方圖，因為Oracle不會記錄所有的nopopular value的值，所以CBO選錯執行計劃的概率會比Frequency型別的情形高。

      如果針對文字型別的欄位收集直方圖統計資訊，則Oracle只會將該文字型別欄位的文字值頭32個位元組取出來(實際只取頭15個位元組)，並將其轉換成一個浮點數，然後將這個浮點數作為上述文字型欄位的直方圖統計資訊儲存於資料字典中。對於那些超過32個位元組的文字型欄位，只要對應記錄的文字值的頭32個位元組相同，Oracle收集直方圖統計資訊時，就會認為這些記錄文字值相同，但實際是不同的。進而選擇錯誤的執行計劃。

4、CBO在解析多表關聯的目標SQL時，可能會漏選正確的執行計劃。

SQL各表之間可能的連線順序總數是n！，10個表連線三百多萬，15個表連線一百多億。

CBO至多隻會考慮其中根據_OPTIMIZER_MAX_PERMUTATIONS計算出來的有限種可能。

只要目標SQL正確的執行計劃不在上述有限可能之中，則CBO一定會漏選正確的執行計劃。

SELECT i.ksppinm name, CV.ksppstvl VALUE, CV.ksppstdf isdefault, 
DECODE (BITAND (CV.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismodified,
DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx
AND i.ksppinm LIKE '%_optimizer_max_%'
AND i.ksppinm LIKE '/_%' ESCAPE '/'  ORDER BY   REPLACE (i.ksppinm, '_', '');

DESCRIPTION

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

optimizer maximum join permutations per query block

VALUE         ISDEFAULT ISMODIFIED ISADJ

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

2000            TRUE         FALSE           FALSE