Oracle 11g統計資訊收集--多列統計資訊的收集

Oracle 11g統計資訊收集--多列統計資訊的收集

我們在寫SQL語句的時候，有的時候會碰到where子句後面有多個條件的情況，也就是根據多列的條件式篩選得到資料。預設情況下，Oracle會把多列的選擇性（selectivity）相乘從而得到where語句的選擇性，這樣有可能會讓Oracle的選擇性變的不夠準確，從而導致最佳化器做出錯誤的判斷。比如對於汽車廠商和汽車型號，實際上是有關聯關係的，一旦你知道了汽車的型號，就能判斷出是哪一個廠商的汽車。再比如說酒店星級和酒店價格等級也有類似的對應關係。為了能夠讓最佳化器做出準確的判斷，從而產生準確的執行計畫，oracle在11g資料庫中引入了多列統計資訊的概念。

選擇性：在本例中是 1/唯一值

我們有一張表BOOKS，兩個列hotel_id,rate_category,我們來看一下這兩列的資料分布：
SQL>  select hotel_id,rate_category,count(1) from books
2  group by  hotel_id,rate_category
3  order by hotel_id;

HOTEL_ID RATE_CATEGORY  COUNT(1)
---------- ------------- ----------
10  11  19943
10  12  39385
10  13  20036
20  21  5106
20  22  10041
20  23  5039

6 rows selected.

仔細檢查資料：hotel_id 10 的 rate_category 列僅包含 11、12 和 13，而 hotel_id 20 的該列僅包含 21、22 和 23（11、12 和 13 一個都不包含)。為什

嗎？原因可能與酒店的星級有關。酒店 20 是一家定價較高的酒店，而租金等級 11、12 和 13 是較低的等級，因此它們不適用於一家高收費的酒店。同樣地，

21、22 和 23 是較高的租金等級，因此它們不適用於酒店 10 這樣的經濟型酒店。而且，酒店 10 的房間預定數量多於酒店 20。

在表books的兩個列上建立索引，並收集表的統計資訊。
SQL> create index book_idx1 on books(hotel_id);
Index created.

SQL> create index book_idx2 on books(rate_category);
Index created.

SQL> analyze table books compute statistics;
Table analyzed.

如果我們要找到表中滿足條件20號酒店價格等級是21的記錄，執行計畫會是什麼樣子呢？
SQL> set autotrace trace exp
SQL> select hotel_id,rate_category from books where hotel_id=20 and rate_category=21;

Execution Plan
----------------------------------------------------------
Plan hash value: 2688610195

---------------------------------------------------------------------------
| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |  |  8296 | 33184 |  47  (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOOKS |  8296 | 33184 |  47  (3)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("RATE_CATEGORY"=21 AND "HOTEL_ID"=20)

SQL> set autotrace off

SQL> select count(1) from books;

COUNT(1)
----------
99550

SQL> select 99550/8296 from dual;

99550/8296
----------
11.9997589

從上例中可以看到，oracle選擇了走全表掃描，判定的記錄條數是8296條，而我麼表中真實的資料是5106條，對於整張表99550條記錄來說，應當可以使用到索引的。但是oracle沒有，因為oracle會把兩個列分別考慮，而計算出來的選擇性是hotel_id 1/2，rate_category 1/6,從而得到了語句的選擇性是1/12,這也就

是我們在執行計畫中看到8296（99550*1/12）條記錄的原因。

為了能夠讓oracle得到準確的執行記錄，我們可以採取兩個方法
1.使用程式包 dbms_stats 中的新函數 create_extended_stats 建立一個虛擬列，然後對錶收集統計資訊。
大致如下：
dbms_stats.create_extended_stats('SCOTT', 'BOOKS','(HOTEL_ID, RATE_CATEGORY)')
下次再收集表的統計資訊時，將會自動收集您的列組的多列統計資訊。

2.直接在程式包 dbms_stats 指定method_opt，收集統計資訊時，把列組合作為單獨列使用

在這裡我們使用第二種方法
SQL> begin
2  dbms_stats.gather_table_stats (
3  ownname  => 'SCOTT',
4  tabname  => 'BOOKS',
5  estimate_percent=> 100,
6  method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS  (HOTEL_ID,RATE_CATEGORY)',
7  cascade  => TRUE
8  );
9  end;
10  /

PL/SQL procedure successfully completed.

收集完列組統計資訊後，再來看一下語句的執行計畫
SQL> set autotrace trace exp
SQL> select hotel_id,rate_category from books where hotel_id=20 and rate_category=21;

Execution Plan
----------------------------------------------------------
Plan hash value: 1484887743

-----------------------------------------------------------------------------------------
| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |  |  5106 | 30636 |  19  (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| BOOKS  |  5106 | 30636 |  19  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN  | BOOK_IDX2 |  5106 |  |  11  (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

1 - filter("HOTEL_ID"=20)
2 - access("RATE_CATEGORY"=21)

該輸出清晰地顯示索引 BOOK_IDX2 已使用。為什麼現在使用了索引？注意“Rows”列下方的值 (5106)。最佳化程式正確地確定了值組合的行數的估計值，而非分開的各個值的行數的估計值。

當然了，對於其他的條件，oracle也可以做出準確的判斷

SQL> set autotrace trace exp
SQL> select hotel_id,rate_category from books where hotel_id=10 and rate_category=12;

Execution Plan
----------------------------------------------------------
Plan hash value: 2688610195

---------------------------------------------------------------------------
| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |  | 39385 |  230K|  47  (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOOKS | 39385 |  230K|  47  (3)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("RATE_CATEGORY"=12 AND "HOTEL_ID"=10)

在視圖 USER_STAT_EXTENSIONS 中，您可以看到在資料庫中定義的擴充統計資訊：
SQL> select extension_name, extension
2  from user_stat_extensions
3  where table_name='BOOKS';

EXTENSION_NAME  EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUW3MXAI1XLZHCHDYKJ9E4K90 ("HOTEL_ID","RATE_CATEGORY")

本文永久更新連結地址：