藏在表分区统计信息背后的小秘密-阿里云开发者社区

藏在表分区统计信息背后的小秘密

原创 2017-09-19 曾令军数据和云

云和恩墨技术专家，8年数据库运维经验。思维敏捷，擅长于数据库开发、解决棘手的数据库故障和性能问题，在数据库故障诊断、运维监控、性能优化方面积累了丰富的经验。

本文由一个表分区统计信息没有按预期更新的问题，逐步深入设疑、探因、求实，解开关于表分区统计信息收集的秘密。曲径通幽处，禅房花木深。让我们打开数据库知识的那扇窗，去看到花木浓茂幽静自然的美好。

案例背景

客户的业务系统中，做了AB表设计。A代表日间业务表，只存放一天的交易数据；B代表历史数据表，每天一个分区。每天晚上A表的所有数据会转入B表的最大分区中，然后B表的最大分区分裂成当天分区和新的最大分区。

请点击此处输入图片描述

在做完上述数据转换及分区拆分之后，虽然此时P_20170101这个分区有大量的数据，虽然自动收集统计信息的任务每天都会运行，但这个分区的统计信息始终为0。

【问题】：在数据库自动收集统计信息任务运行后，分区P_20170101的统计信息错误，导致执行计划选择错误，与历史数据有关联的查询运行特别缓慢。

场景模拟

创建历史数据表，按交易日期做范围分区：

请点击此处输入图片描述

插入10000行数据后提交，模拟业务表数据转入历史表：

请点击此处输入图片描述

检查分区的统计信息：

请点击此处输入图片描述

此时虽然插入了数据，但没有手动或自动收集过，因此统计信息仍然为空。

且让它保持为空，并在这个前提下，继续往下做。

接下来拆分分区，然后再次检查分区的统计信息：

请点击此处输入图片描述

拆分完成之后，原分区PEVER和新分区P20170821此时的num_rows均为空。

调用自动收集统计信息任务的过程，然后检查分区的统计信息：

请点击此处输入图片描述

小贴士：dbms_stats.gather_database_stats_job_proc过程就是自动收集统计信息任务执行的程序，此处手工调用，模拟客户数据库每天自动收集任务的运行。该过程相当于使用gather auto选项调用了dbms_stats.gather_database_stats过程

注意此时统计的P20170821记录数为10000行，统计信息完全正确！并没有模拟出与客户问题相符的现象。

继续插入20000行数据，但日期换成20170822，模拟第二天的交易：

请点击此处输入图片描述

拆分分区，然后检查分区的统计信息：

请点击此处输入图片描述

注意这里的差异，新分区P20170822的num_rows并不是空，而是0。

调用自动收集统计信息任务的过程，然后检查分区的统计信息：

请点击此处输入图片描述

问题模拟出来了。新分区P20170822实际上有20000行数据，但自动收集的任务运行后，该分区的行数仍然是0。

模拟过程中引出来的问题：

1）为什么拆分分区的初始化统计信息开始是空，而后面又变成 0？

2）为什么分区的统计信息为空时，自动收集任务运行后，统计信息更新了？

3）为什么分区的统计信息为0时，这个分区有大量数据，而统计信息始终不更新？

4） PEVER分区一开始是空，先插入了20000行，然后数据又分裂出去，重新变回一个空分区，为什么它的统计信息又更新了？

后三个问题，都指向了同一个问题：自动收集任务运行时，哪些对象被收集？

拨开迷雾

请点击此处输入图片描述

问：为什么拆分分区的初始化统计信息开始是空，而后面又变成0？

请点击此处输入图片描述

答：分区分裂时，新分区的统计信息继承了原分区的统计信息值

请点击此处输入图片描述

第一次分裂的时候，由于PEVER分区的统计信息为空，因此分裂出来的P20170821和新的PEVER分区初始的统计信息也为空；第二次分裂的时候，由于此时PEVER分区的统计信息被收集过，变成了 0行，那么分裂出来的P20170821和新的PEVER分区的初始统计信息就是0行。那假如PEVER分区是10000行，新分区也会是10000行，依此类推。

请点击此处输入图片描述

问：自动收集任务运行时，哪些对象被收集？

请点击此处输入图片描述

答：存在缺失和陈旧的统计信息的表、索引、分区

请点击此处输入图片描述

自动收集任务运行时,优先收集缺失统计信息的对象，然后再收集陈旧统计信息的对象。缺失或陈旧统计信息的对象，可以从dba_tab_statistics和dba_ind_statistics这两个视图中查询。

请点击此处输入图片描述

问：如何判断对象的统计信息为陈旧？

请点击此处输入图片描述

答：表或分区的数据变化量超过10%

请点击此处输入图片描述

控制统计信息是否陈旧的数据变化量的比例默认为10%，可通过dbms_stats.get_prefs这个函数查询，这个值也可通过DBMS_STATS.SET_GLOBAL_PREFS修改，但一般不建议这样做。

问题分析到这里，之前提出来的四个问题的答案就已经全部解开了。

为什么拆分分区的初始化统计信息开始是空，而后面又变成 0 ？

----分裂出来的分区继承原分区的统计信息
为什么分区的统计信息为空时，自动收集任务运行后，统计信息更新了？

----缺失统计信息的对象，会被收集
为什么分区的统计信息为0时，这个分区有大量数据，而统计信息始终不更新？

----不满足缺失或陈旧的条件，不被收集
PEVER分区一开始是空，先插入了20000行，然后数据又分裂出去，重新变回一个空分区，为什么它的统计信息又更新了？

----陈旧统计信息的对象，会被收集

那么这个案例如何优化呢？有两种选择都可以解决问题:

a) 修改程序逻辑顺序：先插入数据再SPLIT分区修改为先SPLIT分区再插入数据

b) 手动补充收集一次：原业务逻辑不变，但操作完之后，对split出来的分区单独收集

知识扩展

莎士比亚说：大海有崖岸,热烈的爱却没有边界。虽然问题已经解决，仔细思考，还有更多细致的问题在等着我们去探索：

请点击此处输入图片描述

1、如何查询对象的数据变化量？

2、对象的数据变化是如何刷新的？

3、分区表统计信息的更新机制？如果整个分区表的数据更新，会不会扫描那些没有发生数据变化的分区呢？

请点击此处输入图片描述

如何查询对象的数据变化量？

请点击此处输入图片描述

oracle提供了一个名为USER_TAB_MODIFICATIONS的视图，可以查询到分区以及分区表的DML操作次数，例如：

select * from dba_tab_modifications wheretable_name='T_HISTDATA_PART';

请点击此处输入图片描述

这个视图还能查询到对象被truncate的次数。但是存在一个问题：数据修改之后，并不能马上在视图中查询到，需要手工刷新：

begin

dbms_stats.flush_database_monitoring_info();

end;

数据修改不能立即刷新的原因，就是下面要讨论的问题。

请点击此处输入图片描述

对象的数据变化是如何刷新的？

请点击此处输入图片描述

USER_TAB_MODIFICATIONS的刷新机制

请点击此处输入图片描述

在10G之后，USER_TAB_MODIFICATIONS视图的数据并不能立即更新，而是每天只更新一次，因此需要通过这个视图准确查询到数据变化时，需要先手工刷新。

请点击此处输入图片描述

分区表统计信息的更新机制？

请点击此处输入图片描述

当分区的数据变化达到10%，自动收集统计信息任务运行时，会更新该分区的统计信息。

当分区表中所有分区中数据变化量的总和达到分区表总数据量的10%，会更新该分区表的统计信息。

分区表的统计信息收集更新时，以前必须要扫描该表所有的分区或整个表的数据，在10.2.0.5版本之后，可以设置分区表按增量变化统计，只收集有数据变化的分区。

要设置分区表按增量变化统计，可以设置表统计信息的incremental属性。

请点击此处输入图片描述

【学以致用】：对于一些数据量特别大的分区表，可以考虑设置INCREMENTAL=TRUE属性，能够显著提升分区表统计信息收集的速度。

不闻不若闻之，闻之不若见之，见之不若知之，知之不若行之。通过一个问题的深入剖析，逐层推进，我们看见了、理解了、实践了，最终也收获了。以上这些知识要点，在运维实战中，具备非常有价值的指导意义。

实验脚本：

CREATE TABLE T_HISTDATA_PART( TRANDT VARCHAR2(8),DESTRX VARCHAR2(200))

PARTITION BY RANGE(TRANDT)

(PARTITION PEVER VALUES LESS THAN (MAXVALUE));

begin

FOR I IN 1 .. 10000 LOOP

INSERT INTO T_HISTDATA_PART VALUES('20170821',LPAD('A',100,'A'));

END LOOP;

COMMIT;

end;

SELECT D.TABLE_OWNER,

D.TABLE_NAME,

D.PARTITION_NAME,

D.NUM_ROWS,

D.LAST_ANALYZED

FROM DBA_TAB_PARTITIONS D

WHERE D.TABLE_NAME = 'T_HISTDATA_PART';

ALTER TABLE T_HISTDATA_PART SPLIT PARTITION PEVER AT('20170822') INTO (PARTITION P20170821,PARTITION PEVER);

BEGIN dbms_stats.gather_database_stats_job_proc; end;

begin

FOR I IN 1 .. 20000 LOOP

INSERT INTO T_HISTDATA_PART VALUES('20170822',LPAD('A',100,'A'));

END LOOP;

COMMIT;

end;

ALTER TABLE T_HISTDATA_PART SPLIT PARTITION PEVER AT('20170823') INTO (PARTITION P20170822,PARTITION PEVER);

begin

dbms_stats.flush_database_monitoring_info();

end;

select * from dba_tab_modifications WHERE table_name='T_HISTDATA_PART';

SELECT * FROM sys.Mon_Mods$ d WHERE d.obj#=166616;

SELECT * FROM sys.Mon_Mods_All$ d WHERE d.obj#=166616;

SELECT * FROM Dba_Objects d WHERE d.OBJECT_NAME='T_HISTDATA_PART';

DBA_TAB_STATISTICS.LAST_ANALYZED

DBA_IND_STATISTICS.STALE_STATS='YES'

About Me

.............................................................................................................................................

● 本文整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店

.............................................................................................................................................

藏在表分区统计信息背后的小秘密