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

深藏阁楼爱情的钟 2022-06-09 08:42 289阅读 0赞

> 原文链接 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。默认情况此选项设置为会话，即每个使用全局临时表的会话都有自己独立的统计信息。优化器会首先使用会话的统计信息，如果会话统计信息不存在，才会使用共享的统计信息。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

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

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

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

图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

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220609/07831729cc5a4527bb0b9e312e1146f3.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220609/681e5c359507466d9adefe297a7bd8f7.png