讀書筆記-《基于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, '_', '');

NAME

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

_optimizer_max_permutations                 

DESCRIPTION

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

optimizer

maximum join permutations per query block

VALUE

        ISDEFAULT ISMODIFIED ISADJ

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

2000

           TRUE         FALSE           FALSE