表分区“拖出”的性能Error

作者介绍：

曾令军，云和恩墨技术专家，2009年开始接触ORACLE数据库，8年数据库运维经验。思维敏捷，擅长于数据库开发、解决棘手的数据库故障和性能问题。服务于公司华南区多个客户，曾参与过国内多家股份制银行、城市商业银行的核心业务系统、数据仓库的部署建设和生产运维工作，在数据库故障诊断、运维监控、性能优化方面积累了丰富的经验。

问题现象

客户反馈核心业务系统中，出现多条Sql执行效率很低，执行计划走了错误的索引，原因是统计信息不对，经过统计信息收集并显示授权后（相当于收集统计信息时设置了noinvalidation=false选项，共享池中原有的游标会失效），执行计划并没有及时改变。一段时间后又恢复正常。

令人疑惑的问题点：

1）为什么收集完统计信息并对SQL查询的表显示授权后，执行计划并没有马上改变

2）为什么每天定时收集统计信息的任务没有起作用

问题分析

1. 根据客户提供的sql_id，分析该sql_id的执行计划，走错了索引。

h_kns_tran_fund表有1亿+纪录数，sql条件中字段明显应该走pk_h_kns_tran_fund索引，但CBO优化器却选择了idx_h_kns_tran_fund_acctno这个索引，这样会导致分区全扫描。通常这种情况都是统计信息不对引起，检查统计信息，确实有问题。于是手工收集，但是过了一个小时左右，执行计划才恢复正常。

针对这个问题：

检查业务程序，发现程序中存在一个if else判断，如果传入的日期为当天，就不会查h_kns_tran_fund这个表，如果是历史日期才查。通过这条线索，检查dba_hist_sql_bind_capture视图，发现该sql执行频率很低，隔一两个小时才有一次，因此执行计划没有改变的原因，可以断定就是SQL在原有的执行计划失效后，一直都没有符合输入历史查询条件的SQL执行过，没有发生新的SQL解析，产生新的正确的执行计划。

2. 为什么系统开启了每天的自动收集统计信息任务，该表却没有收集到统计信息呢？

检查表结构，发现这是一个分区表，每天一个分区（例如p20160428)，与开发人员确认，当天的业务数据是记录在kns_tran_fund表中，这些数据每天晚上由业务程序插入到历史表h_kns_tran_fund的pever分区中，插完数据之后再将pever进行拆分，split出一个上日的分区（例如p20160429和pever)，这就是说p20160429在当天被split出来之后，数据就不会再发生变化。

oracle 10g 统计信息自动收集的原则，是看数据量变更是否超过了10% ，这个变化量可以在dba_tab_modifications视图中查到，如果达到这个阀值，收集统计信息的定时任务运行时就会收集这个表的统计信息。

例如：

插入10万条记录到pever分区，手工执行dbms_stats. flush_database_monitoring_info ()，dba_tab_modifications视图中就会多出一条pever分区的纪录，插入次数为10万。

这个案例的问题在于：

数据插入到历史表，然后再split出一个p20160429分区，p20160429这个分区虽然此时有10万条纪录，但数据不再变化，所以dba_tab_modifications视图中不会有这个新分区的变化记录，统计信息收集时并不会收集这个分区。

知识点：dba_tab_modifications视图的数据来源于mon_mods_all$，数据库后台有一个任务，将对象的dml统计信息刷新到mon_mods_all$。这个刷新周期在9i是15分钟，在10g后变成了一天。

基于以上推测，来做一个实验进行验证：

场景一：

正常插入分区数据，用于对比

--此时可以看到dba_tab_modifications中纪录了表和分区0502的变化情况。

--检查一次分区的统计信息，还没有更新

select table_name,partition_name,num_rows,blocks from user_tab_partitions where table_name='TEST_PART';

--执行一次自动统计信息收集的program_action，分区0502的统计信息更新了。

begin dbms_stats.gather_database_stats_job_proc; end;

结论：

正常的数据插入操作，数据变化量达到收集统计信息的比例，自动任务能够收集

场景二：

插入数据到最大分区，然后split出新分区，观察新分区的统计信息收集情况

--这里看到dba_tab_modifications中只纪录了表和分区PEVER的变化情况，却没有分区0503的纪录

--先检查一次分区的统计信息，这里分区0503当前是空值

select table_name,partition_name,num_rows,blocks from user_tab_partitions where table_name='TEST_PART';

--执行一次自动统计信息收集的program_action，分区0503的统计信息也更新了。

begin dbms_stats.gather_database_stats_job_proc; end;

结论：

dba_tab_modifications视图中没有新分区的变化纪录，但统计信息仍然更新了，原因是新分区此时的统计信息为空，自动收集时会将这类对象一并收集。不经意间，又发现了一个知识点。

场景三：

再次插入数据到最大分区，然后split出新分区

--再执行一次，情况发生了变化

--注意此时分区0504被split出来之后，统计信息并不是空的，而是0

--执行一次自动统计信息收集的program_action，分区0504的统计信息并没有更新

begin dbms_stats.gather_database_stats_job_proc; end;

但实际上这个分区的纪录数是1万行。

--随后又做了几次插入、拆分、收集，发现后面插入的数据，分区统计信息都是0，没有再更新：

结论：

dba_tab_modifications视图中没有新分区的变化纪录，且新分区的统计信息是0，自动收集时并不收集这个新分区的统计信息。

这里引出了一个新的问题：为什么分区初始的统计信息，第一次split分区后，分区初始的统计信息是空，而之后的split分区操作新分区的统计信息却是0呢？通过反复实验、仔细对比观察，原来是split产生的新分区的初始统计信息继承了split操作的源分区的统计信息。例如，如果从pever这个分区上split出两个分区P1和P2，那么P1和P2的统计信息都与pever相同，pever的行数是空，新分区也是空，pever的行数是0，新分区也是0，比较隐秘的设计。

说了这么多，是时候总结一下啦。通过这个案例我们学到了什么：

SQL优化时，如果能够分析和理解业务程序的逻辑，往往事半功倍

表或分区有超过10%的数据变化，且被数据库记录到，自动收集任务才会更新这些对象的统计信息

对象的统计信息为空时，自动收集任务会更新这些对象

Split分区操作时，新分区即使有大量的数据，统计信息也可能不会更新

Split分区操作时，新分区的初始统计信息继承于源分区