ITPub博客

首页 > 数据库 > Oracle > Oracle直方图统计信息的应用

Oracle直方图统计信息的应用

原创 Oracle 作者:llnnmc 时间:2017-11-06 16:29:54 0 删除 编辑

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


可以看到表中数据的分布严重不均衡,ID026的记录各只有1条,而ID1的记录有29991条,ID345的记录也各只有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将不走索引而选择全表扫描。此处使用直方图统计信息使得查询性能得到了较大提升。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28974745/viewspace-2146890/,如需转载,请注明出处,否则将追究法律责任。

上一篇: dump数据块
请登录后发表评论 登录
全部评论

注册时间:2016-12-29

  • 博文量
    91
  • 访问量
    63472