Dav_笔记8-Managing Optimizer Statistics之手动收集

 手动收集统计数据

如果不使用自动优化程序统计信息收集，则必须运行DBMS_STATS以手动收集所有模式（包括系统模式）中的统计信息。 如果数据库内容定期更改，则还必须定期收集统计信息，以确保统计信息准确表示数据库对象的特征。

本节包含以下主题：

■使用DBMS_STATS过程收集统计信息

■设置手动统计信息收集的首选项

■何时收集统计信息

■将统计信息与DBMS_STATS函数进行比较

3.1 使用DBMS_STATS过程收集统计信息

您可以使用DBMS_STATS包收集统计信息。

此PL / SQL包还用于修改，查看，导出，导入和删除统计信息。

DBMS_STATS包可以收集有关表和索引以及表的各个列和分区的统计信息。 它不收集群集统计信息。 但是，您可以使用DBMS_STATS来收集有关各个表而不是整个群集的统计信息。

如果为表，列或索引生成统计信息，并且数据字典包含对象的统计信息，则Oracle数据库会更新现有统计信息。 旧的统计信息已保存。 如有必要，您可以稍后恢复它们。 请参阅第13-20页的“还原以前版本的统计信息”。

注意：

不要使用ANALYZE语句的COMPUTE和ESTIMATE子句来收集优化程序统计信息。 这些子句仅支持向后兼容性，可能会在将来的版本中删除。 DBMS_STATS包收集更广泛，更准确的统计信息，并更有效地收集统计信息。

您可以继续将ANALYZE语句用于与优化程序统计信息收集无关的其他目的：

■使用VALIDATE或LIST CHAINED ROWS子句

■收集有关空闲列表块的信息

收集系统架构的统计信息时，可以使用DBMS_STATS.GATHER_DICTIONARY_STATS过程。此过程收集所有系统模式的统计信息，包括SYS和SYSTEM，以及其他可选模式，如CTXSYS和DRSYS。

更新数据库对象的统计信息时，Oracle数据库会使访问该对象的任何当前已解析的SQL语句失效。下次执行此类语句时，将重新解析该语句，并且优化程序会根据新统计信息自动选择新的执行计划。使用远程数据库上的新统计信息访问对象的分布式语句不会失效。新统计信息将在下次解析SQL语句时生效。

表13-1列出了DBMS_STATS包中用于收集数据库对象统计信息的过程。

使用任何这些过程时，统计信息收集有几个重要的注意事项：

■使用采样进行统计收集

■并行统计信息收集

■分区对象的统计信息

■列统计和直方图

■确定陈旧统计

■用户定义的统计信息

使用抽样统计收集

统计数据收集操作可以利用抽样来估计统计数据。抽样是收集统计数据的重要技术。在不进行采样的情况下收集统计信息需要全表扫描和整个表的各种类型。采样可最大限度地减少收集统计数据所需的资源。

使用DBMS_STATS过程的ESTIMATE_PERCENT参数指定采样。虽然您可以将采样百分比设置为任何值，但Oracle建议将DBMS_STATS采集过程的ESTIMATE_PERCENT参数设置为DBMS_STATS.AUTO_SAMPLE_SIZE，以最大限度地提高性能，同时实现必要的统计精度。 AUTO_SAMPLE_SIZE允许Oracle数据库根据对象的统计属性确定良好统计信息所需的最佳样本大小。由于每种类型的统计信息都有不同的要求，因此实际采样的大小在表或列中可能不同

表13-1 DBMS_STATS包索引中的统计信息收集过程例如，要使用自动采样收集OE模式中所有表的表和列统计信息，您可以使用：

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS（'OE'，DBMS_STATS.AUTO_SAMPLE_SIZE）;

手动指定ESTIMATE_PERCENT参数时，如果指定的百分比未生成足够大的样本，则DBMS_STATS收集过程可能会自动增加采样百分比。这通过减少波动来确保估计值的稳定性。

并行统计收集

统计信息收集操作可以串行或并行运行。您可以使用DBMS_STATS收集过程的DEGREE参数指定并行度。数据库可以结合采样使用并行统计数据收集。 Oracle建议将DEGREE参数设置为DBMS_STATS.AUTO_DEGREE。此设置允许Oracle数据库根据对象的大小和与并行相关的init.ora参数的设置选择适当的并行度。

请注意，不会并行收集某些类型的索引统计信息，包括集群索引，域索引和位图连接索引。

分区对象的统计信息

对于分区表和索引，DBMS_STATS可以为每个分区收集单独的统计信息，并为整个表或索引收集全局统计信息。同样，对于复合分区，DBMS_STATS可以为子分区，分区以及整个表或索引收集单独的统计信息。

统计数据收集的粒度

根据正在进行优化的SQL语句，优化器可以选择使用分区，子分区或全局统计信息。全局和分区统计信息对大多数应用程序都很重要。

您可以使用DBMS_STATS过程的GRANULARITY参数确定要收集的分区统计信息的类型。 Oracle建议将GRANULARITY设置为AUTO以根据分区类型收集子分区，分区或全局统计信息。 ALL设置始终收集所有类型的统计信息。

增量统计收集

对于分区表，通常会将新数据添加到新分区中。在添加新分区和加载数据时，必须收集有关新分区的统计信息并使全局统计信息保持最新。

您可以使用INCREMENTAL来确定数据库是否执行全表扫描以维护分区表的全局统计信息。您可以使用DBMS_STATS.SET_TABLE_PREF过程来更改INCREMENTAL值。

当INCREMENTAL = false（默认值）时，数据库始终使用全表扫描来维护全局统计信息。对于大型表，这是一种高度资源密集且耗时的操作。强制全表扫描的替代方法是收集增量统计信息。满足以下条件时，数据库通过仅扫描已更改的分区来逐步更新全局统计信息：

■分区表的INCREMENTAL值为true。

■分区表的PUBLISH值为true。

■用户在收集表的统计信息时，为ESTIMATE_PERCENT指定AUTO_SAMPLE_SIZE，为GRANULARITY指定AUTO。

逐步收集表统计信息会产生以下后果：

■SYSAUX表空间占用额外空间来维护分区表的全局统计信息。

■如果表使用复合分区，则数据库仅收集已修改子分区的统计信息。对于未修改的子分区，数据库不会在子分区级别收集统计信息。通过这种方式，数据库通过跳过未修改的分区来减少工作量。

■如果表使用增量统计信息，并且此表具有本地分区索引，则数据库将收集全局级别的索引统计信息以及已修改（未修改）的索引分区。数据库不会从分区级索引统计信息生成全局索引统计信息。相反，数据库通过执行完整索引扫描来收集全局索引统计信息。

列统计和直方图

在表上收集统计信息时，DBMS_STATS收集有关表中列的数据分布的信息。有关数据分布的最基本信息是列的最大值和最小值。但是，如果列中的数据存在偏差，则此级别的统计信息可能不足以满足优化程序的需要。对于偏斜数据分布，还可以创建直方图作为列统计信息的一部分来描述给定列的数据分布。直方图在第13-28页的“查看直方图”中有更详细的描述。

使用DBMS_STATS收集程序的METHOD_OPT参数指定直方图。 Oracle建议将METHOD_OPT设置为FOR ALL COLUMNS SIZE AUTO。使用此设置，Oracle数据库会自动确定哪些列需要直方图以及每个直方图的桶数（大小）。您还可以手动指定哪些列应具有直方图以及每个直方图的大小。

确定陈旧统计数据

随着这些数据库对象随时间的变化，您必须定期收集有关数据库对象的统计信为了确定给定数据库对象是否需要新的数据库统计信息，Oracle数据库提供了一个表监视工具。当STATISTICS_LEVEL设置为TYPICAL或ALL时，默认情况下启用此监视。

监视跟踪该表的INSERT，UPDATE和DELETE的大致数量，以及自上次收集统计信息以来该表是否已被截断。您可以在USER_TAB_MODIFICATIONS视图中访问有关表更改的信息。在数据修改之后，Oracle数据库将信息传播到此视图时可能会有几分钟的延迟。使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO过程可立即反映内存中保留的未完成的受监视信息。

当OPTIONS参数设置为GATHER STALE或GATHER AUTO时，GATHER_DATABASE_STATS或GATHER_SCHEMA_STATS过程将收集具有过时统计信息的表的新统计信息。如果受监控的表的修改超过10％，则这些统计信息将被视为过时并再次收集。

用户定义的统计信息

您可以创建用户定义的优化程序统计信息以支持用户定义的索引和函数。将统计信息类型与列索引或域索引相关联时，只要为数据库对象收集统计信息，Oracle数据库就会在统计信息类型中调用统计信息收集方法。

在创建基于函数的索引之后，您应该在表上收集新的列统计信息，以允许Oracle数据库收集表达式的列统计信息等效信息。您可以通过调用METHOD_OPT参数设置为FOR ALL HIDDEN COLUMNS的统计信息收集过程来执行此任务。

3.2 设置手动统计信息收集的首选项

您可以使用DBMS_STATS.SET _ * _ PREFS过程来设置收集统计信息的DBMS_STATS过程使用的参数的默认值。 您可以在表，模式，数据库和全局级别为每个参数设置首选项，从而提供精细的控制粒度。

您可以使用DBMS_STATS.SET _ * _ PREFS过程来更改以下参数：

■AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)

■CASCADE

■DEGREE

■ESTIMATE_PERCENT

■GRANULARITY

■INCREMENTAL

■METHOD_OPT

■NO_INVALIDATE

■PUBLISH

■STALE_PERCENT

表13-2列出了用于设置首选项的DBMS_STATS过程。 在DBMS_STAT.GATHER _ * _ STATS过程中设置的参数值将取代其他设置。 如果尚未设置参数，则数据库将检查表级首选项。 如果不存在表首选项，则数据库使用GLOBAL首选项。

3.3 什么时候收集统计数据

手动收集统计信息时，您不仅需要确定如何收集统计信息，还需要确定收集新统计信息的时间和频率。

对于逐步修改表的应用程序，您可能只需要每周或每月收集新的统计信息。在这些环境中收集统计信息的最简单方法是使用脚本或作业调度工具定期运行GATHER_SCHEMA_STATS和GATHER_DATABASE_STATS过程。收集间隔的频率应平衡为优化器提供准确统计信息与统计信息收集过程产生的处理开销的任务。

对于在批处理操作中实质性修改的表（例如批量加载），请在批处理操作中收集这些表的统计信息。加载操作完成后立即调用DBMS_STATS过程。

有时只修改一个分区。在这种情况下，您只能在已修改的分区上收集统计信息，而不是在整个表上收集统计信息。但是，仍可能需要收集分区表的全局统计信息。

3.4 将统计信息与DBMS_STATS函数进行比较

DBMS_STATS使您可以比较来自两个不同来源的表的统计信息。 表13-3列出了DBMS_STATS包中用于比较统计信息的函数：

表13-3中的函数还比较了依赖对象（如索引，列和分区）的统计信息。 如果这些统计信息之间的差异超过某个阈值，它们将显示来自两个源的对象的统计信息。 您可以将阈值指定为函数的参数，默认值为10％。 Oracle数据库使用与第一个源对应的统计信息作为计算差异百分比的基础。