cbo心得(選擇率,基數,直方圖)(三)

cbo心得(選擇率,基數,直方圖)(三)[@more@]存在表和索引上的統計資訊，沒有直方圖這是最一般的情況，定期對錶和索引進行分析，但是並不分析直方圖資訊。大部分時候，這種統計資訊收集策略都會很好的工作。我們這的大部分指得是，列上資料分佈很平均的情況下。為了更好的演示，我需要建立一個存在傾斜列的表

SQL> drop table sunwg purge;

表已刪除。

SQL> create table sunwg (id number);

表已建立。

SQL> create index ind_sunwg on sunwg(id);

索引已建立。

SQL> insert into sunwg select 1 from dba_objects where rownum<2001;

已建立2000行。

SQL> insert into sunwg select 1+ rownum from dba_objects where rownum<2001;

已建立2000行。

SQL> commit;

提交完成。

SQL> analyze table sunwg compute statistics for table for all indexes for columns id size 1;

表已分析。

這樣我們就有了一個有傾斜列的表SUNWG，在表中id = 1的記錄有2000條，佔了全部記錄的50%。查詢下表和索引的統計資訊，看看我們都告訴了CBO什麼資訊。

SQL> select NUM_ROWS,

2 BLOCKS,

3 EMPTY_BLOCKS,

4 AVG_SPACE,

5 CHAIN_CNT,

6 AVG_ROW_LEN

7 from user_tables

8 where table_name = 'SUNWG';

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

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

4000 13 3 4687 0 6

SQL> select BLEVEL,

2 LEAF_BLOCKS,

3 DISTINCT_KEYS,

4 AVG_LEAF_BLOCKS_PER_KEY,

5 AVG_DATA_BLOCKS_PER_KEY,

6 CLUSTERING_FACTOR

7 from user_indexes

8 where index_name = 'IND_SUNWG';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY

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

AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

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

1 8 2001 1

1 7

SQL> select NUM_DISTINCT,

2 LOW_VALUE,

3 HIGH_VALUE,

4 DENSITY,

5 NUM_NULLS,

6 NUM_BUCKETS,

7 HISTOGRAM

8 from user_tab_columns

9 where table_name ='SUNWG'

10 and column_name = 'ID';

NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM

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

2001 C102 C21502 .00049975 0 1 NONE

關於LOW_VALUE和HIGH_VALUE要特別說明下，在user_tab_columns裡面的這兩個值是採用RAW資料型別來儲存的。我們想要得到number型別的值需要呼叫dbms_stats中的過程convert_raw_value。下面是個寫好函式raw_to_number。

function raw_to_number (in_raw raw)

return number

as

ft FLOAT(126);

begin

dbms_stats.convert_raw_value(in_raw,ft);

return ft;

end;

SQL> select * from sunwg where id = 50;

執行計劃

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

Plan hash value: 3109917279

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IND_SUNWG | 2 | 6 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("ID"=50)

統計資訊

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

402 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

上面這條SQL，CBO選擇的透過索引範圍掃描來執行。CBO為什麼會做出這樣的決定呢？我們看一下計劃裡面有一列是Rows，這列代表著ORACLE估計的結果集合的大小。前面這個例子中ORACLE估計ID = 50的結果集大概是2條。實際上呢，ID = 50的記錄只有一條。先不管這點差異，在總量是4000條記錄的表中查詢幾條記錄用索引掃描會更加的高效。因此，CBO選擇了索引掃描而不採用全表掃描。

接下來在說這個Rows = 2是怎麼產生的。在列的統計資訊中一個欄位是NUM_DISTINCT，它說明該列不同值的個數。由於不存在直方圖資訊，ORACLE無法知道列上資料的真實分佈情況，所以ORACLE假設資料在列ID上是平均分配的。所以ID = 50的記錄數就應該是：

4000 * （1/2001） = 1.99

SQL> select * from sunwg where id = 1;

已選擇2000行。

執行計劃

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

Plan hash value: 3109917279

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IND_SUNWG | 2 | 6 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------Predicate Information (identified by operation id):

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

1 - access("ID"=1)

統計資訊

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

1 recursive calls

0 db block gets

138 consistent gets

0 physical reads

0 redo size

27319 bytes sent via SQL*Net to client

1848 bytes received via SQL*Net from client

135 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2000 rows processed

上面的這個例子中ORACLE關於結果集的估計是差得比較多了，實際上ID = 1的記錄有2000條，可ORACLE的估計值只有2條。差距這麼大的就可能導致CBO選擇錯誤的執行計劃，用低效的索引掃描來代替更加高效的全表掃描。直方圖資訊就是用來解決這種問題的，在資料傾斜的情況下給ORACLE更多的資訊，使ORACLE可以意識到傾斜的存在。

我們上邊僅僅對ID = 1這樣的最簡單的情況進行了測試，平時常用的謂詞還有>,>=,<等等，下表列出了常用的一些謂詞的情況

Where 條件	Oracle估計記錄數	表中實際記錄數	Oracle估算公式（猜想）
ID = 1	2	2000	4000 * （1/2001）
ID > 1	3998	2000	4000 * （2001 – 1）/2001
ID >= 1	4000	4000	4000*（2001 – 1/2001）+2
ID = 110	2	0	1<= X<= 4000 * （1/2001）
ID + 1 > 1	200	4000	4000* 5%
ID + 1 >= 1	200	4000	4000* 5%
ID + 1 > 1 AND ID + 1 > 1	200	4000	4000* 5%
ID > 1 AND ID < 50	96	48	4000 *( 5%* 5%) - 2 - 2
ID >= 1 AND ID <= 50	100	2049	4000 *( 5%* 5%)