Oracle直方圖統計信息的應用

Oracle直方圖統計信息說明了表中數據的分布情況，用于在表中數據分布十分不均衡的情況下，指導CBO優化器選擇最優的執行計劃。以下例子說明了這一應用。

創建表

create table scott.t(id number);

創建索引

create index scott.idx_t_id on scott.t(id) compute statistics parallel;

插入數據

begin

    for i in 1 .. 29990 loop

        insert into scott.t values (1);

    end loop;

    commit;

end;

/

begin

    for i in 29991 .. 30000 loop

        insert into scott.t values (mod(i, 7));

    end loop;

    commit;

end;

/

查看數據分布

select id,

       count(*) cardinality,

       sum(count(*)) over(order by id range unbounded preceding) sum_cardinality

  from scott.t

 group by id;

        ID CARDINALITY SUM_CARDINALITY

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

         0           1               1

         1       29991           29992

         2           1           29993

         3           2           29995

         4           2           29997

         5           2           29999

         6           1           30000

可以看到表中數據的分布嚴重不均衡，ID為0、2和6的記錄各只有1條，而ID為1的記錄有29991條，ID為3、4、5的記錄也各只有1條。

在這種情況下執行查詢，看執行計劃，可以看到，由于謂詞ID=1選擇性差，導致采用全表掃描

set autot trace exp

select * from scott.t where id=1;

執行計劃

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 29991 | 89973 |    15   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    | 29991 | 89973 |    15   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=1)

下面查詢唯一性高的條件，由于謂詞ID=0選擇性好，因此采用了索引掃描

select * from scott.t where id=0;

執行計劃

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

Plan hash value: 371777749

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

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

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

|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T_ID |     1 |     3 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("ID"=0)

創建與distinct_keys=7相等數量的寬度均衡的直方圖

begin

    dbms_stats.gather_table_stats(ownname          => 'SCOTT',

                                  tabname          => 'T',

                                  estimate_percent => 100,

                                  method_opt       => 'FOR COLUMNS SIZE 7 ID',

                                  degree           => 4,

                                  cascade          => true);

end;

/

查詢直方圖buckets數據分布信息

col owner for a10

col table_name for a20

col column_name for a20

col endpoint_number for a20

col endpoint_value for a20

select h.owner,

       h.table_name,

       h.column_name,

       to_char(h.endpoint_number) endpoint_number,

       to_char(h.endpoint_value) endpoint_value

  from dba_histograms h

 where h.owner = 'SCOTT'

   and h.table_name = 'T';

OWNER      TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER      ENDPOINT_VALUE

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

SCOTT      T                    ID                   1                    0

SCOTT      T                    ID                   29992                1

SCOTT      T                    ID                   29993                2

SCOTT      T                    ID                   29995                3

SCOTT      T                    ID                   29997                4

SCOTT      T                    ID                   29999                5

SCOTT      T                    ID                   30000                6

在直方圖統計信息的基礎上如果不使用綁定變量，查詢選擇性低的謂詞也是不會走索引的

select * from scott.t where id=1;

執行計劃

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 29991 | 89973 |    15   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    | 29991 | 89973 |    15   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=1)

但如果使用綁定變量，效果就不一樣了

var i number

exec :i:=1;

select * from scott.t where id=:i;

執行計劃

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

Plan hash value: 371777749

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

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

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

|   0 | SELECT STATEMENT |          |  4286 | 12858 |     9   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T_ID |  4286 | 12858 |     9   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("ID"=TO_NUMBER(:I))

以上測試說明，在表中數據分布不均衡的情況下執行選擇性低的查詢，如果有完整準確的直方圖統計信息，并且采用綁定變量，CBO會選擇索引掃描。如果沒有直方圖信息，CBO將不走索引而選擇全表掃描。此處使用直方圖統計信息使得查詢性能得到了較大提升。