验证Oracle收集统计信息参数granularity数据分析的力度

共计 3102 个字符，预计需要花费 8 分钟才能阅读完成。

最近在学习 Oracle 的统计信息这一块，收集统计信息的方法如下：

  本文主要对参数 granularity 进行了一下验证，

  granularity：数据分析的力度

  –global  — 全局

  –partition — 只在分区级别做分析

  –subpartition  – 只在子分区级别做分析

验证步骤如下：

num_rows: 表数据行数

blocks: 数据块数

last_analyzed：最近分析时间

 

四、查询表分区信息

select partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name =’TEST’;

PARTITION_NAME: 分区名称

NUM_ROWS: 数据行数

BLOCKS: 数据块数

last_analyzed：最近分析时间

 

五、查询索引统计信息

select num_rows,blevel,last_analyzed from user_indexes where index_name = ‘IND_ID’;

 

num_rows: 索引数据行数

blevel: 索引高度

last_analyzed: 分析时间

 

六、新增一个分区

alter table test add partition pmax values less than(maxvalue);

 

七、往新的分区中插入 10000 条数据

begin for i in 1..10000 loop — 插入 10000 条数据

insert into test values(100);

end loop;

commit;

end;

 

八、创建一个倾斜度非常大的分区

update test set id=10000 where id=100 and rownum=1; — 创造一个非常倾斜的 Pmax 分区

Commit;

 

九、查询分区数据

select id,count(*) from test partition(pmax) group by id;

 

十、不做分析，再次查询表的统计信息

select num_rows,blocks,last_analyzed from user_tables where table_name = ‘TEST’;

发现数据行数量和数据块数量没有发现变化

 

 

十一、查询 id=100 时执行计划

set autotrace traceonly

set linesize 1000

select * from test where id=100;

发现走了索引，正常情况下，因为 id=100 的数据在一个倾斜度非常高的分区 pmax 中，id 为 100 的数据有 9999 条，走索引的代价会比走全表的代价还要高（因为走索引需要回表），如果统计信息正确，优化器应该会选择走全表，但是这里没走全表而是走了索引，这里怀疑是统计信息不正确导致，后面验证

 

十二、收集分区统计信息

exec dbms_stats.gather_table_stats(user,’TEST’,partname => ‘PMAX’,granularity => ‘PARTITION’);

 

十三、再次查询表的统计信息和分区统计信息

select partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions where table_name =’TEST’;

发现和步骤四比较，分区信息有了变化，说明对分区进行统计信息收集后，分区信息进行了更新

 

select num_rows,blocks,last_analyzed from user_tables where table_name = ‘TEST’;

发现和步骤三想比，表的统计信息并没有发生变化，说明统计了分区信息后，表的统计信息么有更新

仍然走索引，说明在评估查询的时候，表的统计信息依然陈旧

 

十五、查询索引的统计信息

select num_rows,blevel,last_analyzed from user_indexes where index_name = ‘IND_ID’;

发现索引统计信息较步骤五没有变化，说明收集了分区的统计信息后，表的索引信息没有更新

表的统计信息已经更新

 

select num_rows,blevel,last_analyzed from user_indexes where index_name = ‘IND_ID’;

索引的统计信息也已经更新

 

十八、再次查询 id=100 的执行计划

这次发现走了全表，说明收集了全局的统计信息后，表的统计信息准确了，评估也就准确了。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-09/146935.htm