Oracle 12c数据库优化器统计信息收集的最佳实践(三)|何时不需要收集统计信息

原文链接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf 译者 胡红伟

虽然优化器需要准确的统计信息来选择最优的执行计划,但是有些场景下,收集统计信息比较困难,或消耗资源较高,或收集统计信息不能及时完成,那么就需要另一种备选策略。

不稳定的表

不稳定的表即随着时间的变化,数据会发生巨大变化的表。例如,一个订单队列表,一天的开始它是空的,随着时间推移,订单会填满这个表,一旦某一订单被处理又会从这个订单表中删除,一天的结尾表又会变为空。

如果你依赖于自动收集统计信息job来维护这类表的统计信息,那么统计信息会经常显示此表为空。因为晚上此表是空的,而收集统计信息的job也正是晚上才开始执行。然而,在白天的过程中,这个表可能有成百上千条记录。

在这种情况下,最好在表被填充时收集一组有的代表性的统计信息,并锁住。锁住统计信息会阻止自动收集的统计信息覆盖他们。另外,你可以依赖于动态采样来收集这些表的统计信息。优化器在优化一个语句之前编译sql语句的时候会使用动态采样来收集表的基本统计信息。尽管动态采样收集的统计信息没有完全由DBMS_STATS包收集的统计信息质量高,但在大多数情况下他们已经足够好了。

全局临时表

在应用程序上下文中,全局临时表经常被用于存储中间结果。全局临时表在系统级别与具有适当权限的所有用户共享其定义,但里面的数据内容在会话之间是相互独立和私有的。针对此表,直到有数据插入时才会分配物理存储。

一个全局临时表可以是事务特定的(提交时删除行记录),也可以是会话特定的(提交时保留行记录)。收集事务特定表的统计信息会导致此表被清空。相反,收集一个全局临时表的统计信息是可能的(会保留行记录),但是在之前的数据库版本这不是一个好方案,因为使用全局临时表的所有会话不得不共用同一组统计信息,以致于很多系统依赖于动态采样的统计信息。

然而,在oracle 12c版本,现在可以实现每个使用全局临时表的会话拥有自己独立的统计信息。全局临时表上的统计信息是否共享取决于DBMS_STATS包的一个新选项GLOBAL_TEMP_TABLE_STATS。默认情况此选项设置为会话,即每个使用全局临时表的会话都有自己独立的统计信息。优化器会首先使用会话的统计信息,如果会话统计信息不存在,才会使用共享的统计信息。

图13:改变默认方式:从全局临时表不共享统计信息到共享统计信息

如果你是从11g升级到12c,但数据库应用没有被修改去利用全局临时表的会话统计信息,你可能需要保持全局临时表默认的方式与升级之前一致,通过设置DBMS_STATS的GLOBAL_TEMP_TABLE_STATS选项为共享模式(或者至少等到应用被升级)。

当使用直接路径的方式填充一个全局临时表(提交时保留行记录)时,在线统计信息收集会自动创建会话级别的统计信息,这将减少运行额外统计信息收集的必要性,也不会影响其他会话的统计信息。

图14:使用直接路径方式填充一个全局临时表会导致会话级别的统计信息被自动收集

中间表

中间表通常被看做一个ETL进程或一个复杂事务的一部分。这些表只被写一次,读一次,然后被清空或删除。在这种情况下收集统计信息的成本大于好处,因为统计信息只被使用一次。反倒是动态采样应该用于这些场景。建议你锁住这些中间表上的统计信息以防止自动统计信息收集任务再次对他们收集统计信息。

收集其他类型的统计信息

自从基于成本的优化器是现在唯一被支持的优化器,数据库中所有的表需要有统计信息,包括所有的字典表(owner是sys、system等等,且位于system、sysaux表空间中的表),以及动态性能视图使用的x$表。

数据字典统计信息

数据字典表上的统计信息是通过运行在夜间维护窗口上的自动统计信息收集任务来维护的。强烈建议你允许自动统计信息收集任务来维护数据字典统计信息,即使你关掉主要应用账户上的自动统计信息收集job。你可以使用DBMS_STATS.SET_GLOBAL_PREFS存储过程修改AUTOSTATS_TARGET的值为ORACLE,以代替AUTO,来这样做。

exec dbms_stats.set_global_prefs('autostats_target','oracle')

内部对象统计信息

从oracle数据库12c开始,内部对象统计信息如果之前没有被收集过,那么它就会被自动统计信息收集任务收集。在此版本之前,数据库是不会收集内部对象统计信息的。不像其他的数据库表,当统计信息缺失时动态采样不会自动应用于包含x$表的sql语句,此时优化器会使用预定义的统计信息默认值。这些默认值可能没有代表性,可能会导致非最优的执行计划,这可能会导致严重的性能问题,正是因为这个原因,我们强烈建议你手动收集内部对象统计信息。

你可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS存储过程收集内部对象统计信息。因为x$表的瞬态性质,重要的是在系统有一定代表性负载时收集内部对象统计信息。在大型系统中,这并不总是可行的,因为收集统计信息需要占用额外的资源。你不能在系统高峰负荷时收集内部对象统计信息,你应该在系统预热之后,三种重要类型的内部对象表被填充时收集内部对象统计信息。

  • 结构数据 例如,涵盖数据文件,控制文件内容的视图等
  • 基于会话的数据 例如,v$session, v$access 等
  • 工作负载数据 例如,v$sql, v$sql_plan 等

如果你做了一个重大的数据库或应用的升级,或实现一个新的模块,或改变数据库的配置,强烈建议你重新收集内部对象统计信息。例如,你增加了SGA的大小,包含buffer cache和shared pool信息的所有x$表可能变化很大。例如用于v$buffer_pool或v$shared_pool_advice的x$表。

系统统计信息

系统统计信息使得优化器能更准确的计算执行计划中每一步操作的成本,通过使用实际硬件系统执行sql的信息,例如CPU的速度和IO的性能。

系统统计信息是默认开启的,会以默认值自动初始化,这些值对于大多数系统是有代表性的。

总 结

为了使oracle优化器准确地确定执行计划的成本,那么sql语句中涉及到的全部对象(表和索引)必须有准确的统计信息,且必须有准确的系统统计信息。这两部分白皮书系列详细地解释了什么统计信息是必要的,以及这些统计信息怎么被使用,以及不同的统计信息收集方法。

通过自动统计信息收集任务和此白皮书中描述的其他技术手段的组合使用,一个DBA可以为他们的环境维护一组准确的统计信息,以确保优化器得到必要的信息去选择一个最优的执行计划。一旦一个统计信息收集策略被实施,如果要改变策略,必须要在一个可控的方式下进行,并利用关键的特性例如待定统计信息以确保对应用的性能没有不良的影响。

参考文献

Oracle白皮书:Understanding Optimizer Statistics with Oracle Database 12c Release 2

Oracle白皮书:Optimizer with Oracle Database 12c Release 2

Oracle白皮书:Database 12c Real Application Testing Overview

原文发布于微信公众号 - 沃趣科技(woqutech)

原文发表时间:2017-09-01

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏编程札记

Goroutine并发调度模型深入之实现一个协程池

9405
来自专栏芋道源码1024

Dubbo源码解析 —— Router

前言 估算了一下, dubbo里面涉及的东西还是比较多的.比如谈到框架的时候,设计模式都是一个老生常谈的话题,再比如我们开发中我们不常用的一些概念, spi、...

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

云存储产品浅析

云上存储产品主要有对象存储,块存储,网络文件系统(NAS),还有最赚钱的CDN,我们将针对这些主流产品,讲讲他们产品特点,有云上存储时候知道如何选型,当然我们是...

4523
来自专栏Java学习123

Linux 性能分析工具 nmon for Linux

3956
来自专栏编程之路

羊皮书APP(Android版)开发系列(十六)Android 开源库的使用

1092
来自专栏Coding+

如何多端同步 Hexo 框架博客

这个必要性其实不用多说,用着用着你就自己能体会到,比如头天晚上在家里发布了一篇技术文章,第二天在公司 Coding 的过程中突然想到之前发的文某个地方有 Bug...

1391
来自专栏北京马哥教育

NoSQL之mongodb我见

NoSQL介绍: NoSQL数据管理系统是目前非常流行的一种非关系性、分布式、不支持ACID设计规范式的数据库;NoSQL简单的数据模型、元数据和数据分离、弱一...

3259
来自专栏Java进阶架构师

dubbo源码解析-详解router

估算了一下,dubbo里面涉及的东西还是比较多的.比如谈到框架的时候,设计模式都是一个老生常谈的话题,再比如我们开发中我们不常用的一些概念,spi、javass...

1143
来自专栏北京马哥教育

一秒内诊断 Linux 服务器的性能

60,000 毫秒内对 Linux 的性能诊断 当你为了解决一个性能问题登录到一台 Linux 服务器:在第一分钟你应该检查些什么? 在 Netflix,我们有...

3676
来自专栏我的安全视界观

【一起玩蛇】python武器库的打开方式

23511

扫码关注云+社区

领取腾讯云代金券