CBO_ORACLE

ORACLE_CBO

========================
Oracle的聚簇因子對於查詢IO的影響
Oracle中，對於同一個查詢語句，有時候會很快的完成，有時候卻很慢，但是表結構什麼的完全一致，表中的資料也完全一致，這個具體是什麼原因呢，就要從Index中的細節說起了。
在Oracle中的一個特殊的檢視user_indexes中有一個特殊的列，名字是clustering_factor，這個值的內容就是如果訪問表的整個表資料，會造成多少次資料庫IO。我們可以透過下面的SQL語句來檢視。

SELECT
A.INDEX_NAME,
B.NUM_ROWS,
B.BLOCKS,
A.CLUSTERING_FACTOR
FROM
USER_INDEXES A,
USER_TABLES B
WHERE
A.INDEX_NAME = ?
AND A.TABLE_NAME = B.TABLE_NAME

在這個SQL語句中，?代表的就是我們要檢索的Index的名稱。在表中資料有時候屬於無序狀態，這個時候的CLUSTERING_FACTOR比較接近NUM_ROWS，說明如果掃描整個表，每次都要根據Index來讀取相應行的RowID，這個時候的IO操作很多，自然檢索時間會比較長。如果資料有序的話，CLUSTERING_FACTOR比較接近BLOCKS，說明相鄰的資料在一個塊中，減少了IO運算元量，自然檢索時間會大大降低。

下面這一段是Oracle 手冊中關於CLUSTERING_FACTOR的說明：

If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.

========================
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

對其中因子的解釋是：

#SRDS：單資料塊讀的次數；
#MRDS：多資料塊讀的次數；
SREADTIM：一次單資料塊讀的時間；
MREADTIM：一次多資料塊讀的時間；
#CPUCYCLES：完成查詢所需要發出的CPU指令數
CPUSPEED：CPU的處理速度

#SRDS = BLEVEL + INDLEAFBLKS*INDSEL + TABSEL*CLUF (索引掃描)
#MRDS = TABBLKS/MBRC (全表掃描、快速索引全掃描)
SREADTIM = IOSEEKTIM + BLKSIZ/IOTFRSPEED
MREADTIM = IOSEEKTIM + MBRC * BLKSIZ/IOTFRSPEED

其中，
BLEVEL：索引高度；
INDLEAFBLKS：索引葉子資料塊數；
INDSEL：索引選擇性；
TABSEL：表選擇性；
TABBLKS：表在HWM下的資料塊數；
MBRC：多資料塊讀的一次讀取的資料塊數，Multi_Block_Read_Count；
BLKSIZ：資料塊大小；
IOSEEKTIM：IO定址時間，System Statistics中給出，預設為10ms；
IOTFRSPEED：IO傳輸速度，System Statistics中給出，預設為4096位元組/ms。

表部分
#Rows：表的記錄數；
#Blks：表的HWM以下的資料塊數；
AvgRowLen：表記錄的平均長度

索引部分
Col#：索引中欄位在表中的位置；
LVLS：索引高度，即BLevel；
#LB：索引葉子節點資料塊數；
LB/K：平均每個鍵值的葉子節點資料塊數；
DB/K：平均每個鍵值的表資料塊數；
CLUF：聚簇因子（Clustering Factor）

欄位部分
(#n)：欄位位置；
A(VARCHAR2)：欄位名稱和資料型別；
AvgLen：欄位平均長度；
NDV：欄位中唯一值數量；
Nulls：欄位中空值數量；
Density：欄位密度

規則1：CPUSPEED與#CPUCYCLES無關
規則2：IOTFRSPEED與#CPUCYCLES無關
規則3：IOSEEKTIM與#CPUCYCLES無關。

公式22：COST_CPU = TYPFAC*TABROWS + 0.32*TABBLKS*TABSIZ + 4500*TABBLKS

其中

TYPFAC = ROUND((130 + MAXFLTCOLPOS*20 + EXPTYPEFAC1+ EXPTYPEFAC2* EXPSEL1 + 20*EFFQRYCOLNUM*MAX(1,ROUND(EXPTYPEFAC *TABROWS,0))/TABROWS)*TABROWS,0)/TABROWS

其中，子句的計算是由它的上優先順序的子句計算得出，計算式如下：

AND子句：
EXPTYPEFAC = MIN((SUBEXPTYPFAC1+ SUBEXPTYPFAC2*SUBEXPSEL1), (SUBEXPTYPFAC2+ SUBEXPTYPFAC1*SUBEXPSEL2)

EXPSEL = SUBEXPSEL1*SUBEXPSEL2

OR子句：
EXPTYPEFAC = MIN((SUBEXPTYPFAC1+ SUBEXPTYPFAC2*(1-SUBEXPSEL1)), (SUBEXPTYPFAC2+ SUBEXPTYPFAC1*(1-SUBEXPSEL2))

EXPSEL = SUBEXPSEL1 + SUBEXPSEL2 - SUBEXPSEL1*SUBEXPSEL2

其中SUBEXPTYPFAC和SUBEXPSEL可以為子句或者欄位的TYPFAC和SELECTIVITY。

不同資料型別欄位的TYPFAC：

資料型別
COLTYPEFAC

CHAR、VARCHAR2
50

NUMBER
150

DATE
300

當匹配符為LIKE時，COLTYPEFAC_NEW = COLTYPEFAC + 50

當匹配符為IN、NOT IN時COLTYPFAC_NEW = COLTYPEFAC*(1-1/NDV)^0+COLTYPEFAC*(1-1/NDV)^1+…+COLTYPEFAC*(1-1/NDV)^(INNUM-1)

各種匹配符的選擇性計算如下：

匹配符
COLSEL

>、=
1/20

LIKE
1/20

=
1/NDV

<>
1-1/NDV

IN
NOTINNUM*1/NDV

NOT IN
(1-1/NDV)^NOTINNUM

當作用NOT時，選擇性變為 (1-原選擇性).

[@more@]