表分区“拖出”的性能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分区操作时,新分区的初始统计信息继承于源分区

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-08-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

Oracle最重要的九大性能视图

摘要:Oracle数据库的性能优化一直以来都是DBA关注的焦点,在不同的版本中,Oracle都提供了相关的工具用于数据库的性能诊断,事实上这些工具都是通过对数据...

33260
来自专栏杨建荣的学习笔记

MySQL中的double write(二)(r12笔记第17天)

MySQL里的double write是InnoDB的三大闪亮特性,另外两个是insert buffer 和自适应哈希,其实还有几个比如异步IO,Flu...

33590
来自专栏杨建荣的学习笔记

数据迁移中需要考虑的问题(r2第15天)

在生产环境中,做数据迁移需要考虑很多的可能性和场景,尽量排除可能发生的问题。我自己总结了下,大体有如下需要注意的地方。 1)充分的测试,评估时间,总结经验,提升...

32690
来自专栏python开发教学

Oracle与Sql server的区别 一直搞不明白Oracle数据库和sql server的区别,今天我特意查资料把他们的区别整理出来

Oracle数据库:Oracle Database,又名Oracle RDBMS,或简称Oracle。是甲骨文公司的一款关系数据库管理系统。

16530
来自专栏杨建荣的学习笔记

快速定位隐蔽的sql性能问题及调优(r5笔记第38天)

在前几天,有个开发同事问我一个问题,其实也算是技术救援,他说在有个job数据处理的频率比较高,在测试环境中很难定位出在哪有问题,而且速度也还能接受,但是在生产环...

37860
来自专栏大数据和云计算技术

转载:云HBase小组成功抢救某公司自建HBase集群,挽救30+T数据

使用过开源HBase的人都知道,运维HBase是多么复杂的事情,集群大的时候,读写压力大,配置稍微不合理一点,就可能会出现集群状态不一致的情...

11020
来自专栏性能与架构

Mysql 5.7 的重要特性

这几天在熟悉 Mysql 的新版本 5.7,发现这个版本的改进真的很大,例如提供了更强大的 GIS 功能、高性能的 JSON 数据操作、对多处地方的性能提升 …...

40150
来自专栏木子昭的博客

IP查询有啥用?

在线地址: https://fangyuanxiaozhan.com/demo/ip

17030
来自专栏数据和云

实战课堂:一则CPU 100%的故障分析处理知识和警示

编辑手记:在现实的生产环境中,DBA可能遭遇到各种各样的异常,或简单、或复杂,但是无一不考验DBA的经验和能力,在『实战课堂』栏目中,我们将整理和分享来自云和恩...

12730
来自专栏杨建荣的学习笔记

DBA和开发同事的一些代沟(一)(r7笔记第17天)

DBA同学在工作中不可避免和开发同学打交道,和开发的同学在交流中还是有不少的小插曲,有些想想也蛮有意思,但是有些是痛点。 我举几个例子来说明,可能比较片面,但是...

36550

扫码关注云+社区

领取腾讯云代金券