Oracle優化07-分析及動态采樣-直方圖

思維導圖

07系列文章

Oracle優化07-分析及動态采樣-直方圖

Oracle優化07-分析及動态采樣-DBMS_STATS 包

Oracle優化07-分析及動态采樣-動态采樣

概述

擷取準确的段對象（表、表分區、索引等）的分析資料，是CBO存在的基石。是以資料段的分析對于CBO來講非常的重要。

在本篇博文中我們重新梳理一下，從頭開始，再一次走進CBO的世界。

我們知道CBO的機制是手機盡可能多的對象資訊和系統資訊，通過對這些資訊進行計算、分析、評估，最終得出一個成本最低的執行花來，這就是CBO的全部。 為了讓CBO總是能做出最正确的SQL執行計劃，就需要給CBO提供盡可能多的資訊。

舉個簡單的例子：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as xxx@xgj

 ##通過 1=2 不成立的條件，建立一個空表（後續建索引會快一點兒）
SQL> create table t as select object_id ,object_name from dba_objects where 1=2;

Table created
 ##建立索引
SQL> create index ind_t on t(object_id);

Index created
 ##插入資料
SQL> insert into t select object_id ,object_name from dba_objects;

35260 rows inserted

SQL> commit;

Commit complete
 ## 沒有對表進行分析，可以通過下面兩個視圖來确認
SQL> select a.NUM_ROWS,a.AVG_ROW_LEN ,a.BLOCKS,a.LAST_ANALYZED from user_tables  a where a.TABLE_NAME='T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------

SQL> select  a.blevel ,a.leaf_blocks ,a.distinct_keys,a.last_analyzed from user_indexes a where a.table_name='T';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
         0           0             0 2017-01-08 11

SQL>            

複制

表的行數、行長、占用的資料塊數以及最後分析時間都是空的，索引的相關資訊也都為0，說明這個表和索引都沒有被分析過。

如果此時又一條SQL對表做查詢，CBO由于無法擷取這些資訊，很可能生成錯誤的執行計劃。如下所示：

檢視SQL的執行計劃：

執行SQL
SQL> select /*+ dynamic_sampling(t 0) */  * from t where t.object_id>30;
...省略輸出
SQL> select a.SQL_ID,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like 'select /*+ dynamic_sampling(t 0) */  * from t where t.object_id>30%';

SQL_ID CHILD_NUMBER
------------- ------------
f5q92sydyqc4z            0

##檢視執行計劃
SQL> select * from table(dbms_xplan.display_cursor('f5q92sydyqc4z',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  f5q92sydyqc4z, child number 0
-------------------------------------
select /*+ dynamic_sampling(t 0) */  * from t where t.object_id>30
Plan hash value: 4013845416
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   316 |     0   (0)|
|*  2 |   INDEX RANGE SCAN          | IND_T |     1 |       |     0   (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."OBJECT_ID">30)           

複制

10g以後，如果一個表沒有做過分析，ORACLE會自動對它做動态采用分析， 我們使用/+ dynamic_sampling(t 0) /這種Hint 将動态采樣的級别設定為0，即不使用動态采樣.

CBO估算出滿足條件的記錄為4條，是以選擇了索引。 實際情況呢？ 我們先對表做個分析操作。

9i開始，Oracle推薦使用DBMS_STATS包對表進行分析操作。

為CBO收集資訊

SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed

SQL> select a.NUM_ROWS,a.AVG_ROW_LEN ,a.BLOCKS,a.LAST_ANALYZED from user_tables  a where a.TABLE_NAME='T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
     35260          24        244 2017-01-08 14

SQL> select  a.blevel ,a.leaf_blocks ,a.distinct_keys,a.last_analyzed from user_indexes a where a.table_name='T';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
         1          98         35257 2017-01-08 14

SQL>            

複制

dbms_stats.gather_table_stats(user,’t’)預設對表和索引都進行了分析.

清掉shared_pool 重新看下執行計劃。

## 清掉 shared_pol
SQL> alter system flush shared_pool;

System altered

SQL> select * from t where t.object_id>30; 
省略輸出...

SQL> select a.SQL_ID,a.CHILD_NUMBER from v$sql a where a.SQL_TEXT like 'select * from t where t.object_id>30%';

SQL_ID CHILD_NUMBER
------------- ------------
fcvjyr3skfj5b            0


SQL> select * from table(dbms_xplan.display_cursor('fcvjyr3skfj5b',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fcvjyr3skfj5b, child number 0
-------------------------------------
select * from t where t.object_id>30
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    68 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 35234 |   825K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."OBJECT_ID">30)


SQL>            

複制

我們看到T表在做完分析後，CBO估算出的結果集為35234 ，和實際情況相差很小。

是以使用全表掃描更優。 因為這種情況下，如果先通路索引，然後根據索引的鍵值去尋找表的記錄，勢必會導緻讀取更多的資料塊，走全表反而會更快一些。

直方圖 Histogram

直方圖 (Histogram), 是資料分析分析當中的一個内容，但它對CBO的影響非常大。

DBMS_STATS 包對段表的分析有三個層次：

1. 表自身的分析： 包括表中的行數，資料塊數，行長等資訊。
2. 列的分析：包括列值的重複數，列上的空值，資料在列上的分布情況。
3. 索引的分析： 包括索引葉塊的數量，索引的深度，索引的聚合因子等。

我們現在說的直方圖，單指第二項的最後一種 列分析中 資料在列上的分布情況。

當 Oracle 做直方圖分析時，會将要分析的列上的資料分成很多數量相同的部分，每一部分稱為一個 bucket,這樣 CBO 就可以非常容易地知道這個列上的數的分布情況，這種資料的分布将作為一個非常重要的因素納入到執行計劃成本的計算當中。

對于資料分部非常傾斜的表，做直方圖分析是非常有用的。

來看下面兩個例子：

圖一的資料分布非常均勻的直方圖模式，每一個數值範圍（bucket）内的資料記錄都基本上一樣。

圖二，資料分部嚴重傾斜，數值小于20的記錄占到了總記錄的70%。

直方圖有時候對于CBO非常的重要，特别是對于字段資料非常傾斜的表，做直方圖分析尤為重要。

舉例說明：

SQL> create table t as select 1 id ,object_name from dba_objects;

Table created

SQL> update t set t.id=99 where rownum=1;

1 row updated

SQL> commit;

Commit complete

SQL> create index ind_t on t(id);

Index created

 ##檢視資料分布情況
SQL> select id ,count(1) from t group by id;

        ID   COUNT(1)
---------- ----------
         1      35256
        99          1
## 表和索引分析 
SQL>  exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed

SQL>            

複制

檢視分析後的情況：

SQL> select a.num_rows,a.avg_row_len ,a.blocks,a.last_analyzed from user_tables  a where a.table_name='T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------
     35257          22        144 2017-01-08 18
SQL> select  a.blevel ,a.leaf_blocks ,a.distinct_keys,a.last_analyzed from user_indexes a where a.table_name='T';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- -------------
         1          69             2 2017-01-08 18

SQL>            

複制

DISTINCT_KEYS ： 2

我們建立了一張表，id字段傾斜非常嚴重，除了一條id=99的資料之外，其餘的id全部為1。

預設情況下，dbms_stats包會對所有的列做直方圖分析。

檢視直方圖的資訊視圖 user_histogram

SQL> select *  from user_histograms a where a.TABLE_NAME = 'T' ;           

複制

檢視如下SQL的執行計劃

select * from t where id=1;           

複制

select * from t where id=99;           

複制

現在我們将直方圖資訊删除，但是保留表和索引的分析資訊

SQL>  exec dbms_stats.delete_column_stats(user,'t',colname => 'id');

PL/SQL procedure successfully completed           

複制

再此檢視T表直方圖的視圖資訊

我們可以看到ID字段的資訊已經被删除掉了。

檢視表和索引的資訊

我們可知索引和表的資訊依然存在，并且索引中甚至可以找到distinct_keys=2。但是CBO卻無法得到這兩個數值的分布情況，是以依然沒法選出一個正确的執行計劃。

下面看執行個體：

select * from t where id=1;

select * from t where id=99;

CBO在id =1 時，估算傳回的結果是353行，比較全部表的記錄35257（這個資訊可以從表的分析資料中得到user_tables.NUM_ROWS字段）， CBO認為選擇索引是合适的，但是我們知道實際上id=1的記錄數基本上等于表的全部記錄。在這種情況下CBO沒法得到資料的具體分布情況，是以做出了錯誤的執行計劃

同樣的CBO在id=99的情況下，CBO估算出傳回值是6條。

是以我們可以斷定，如果一個裂傷的資料有比較嚴重的傾斜，對這個列做直方圖是有必要的。

那是不是每個表的每個列都應該做直方圖分析呢？

其實是一個沒有定論的話題。因為首先要知道，Oracle對資料分析是需要消耗資源的，特别是對于一些很大的段對象，分析的時間尤其長。 是以權衡一下，既要避免分析導緻系統性能下降而對業務産生影響，同時又要保證CBO擷取足夠的資訊來産生正确的執行計劃。