Dav_笔记8-Managing Optimizer Statistics之概述及管理

一. 优化程序统计信息概述

优化程序统计信息描述有关数据库和数据库中对象的详细信息。 查询优化器使用这些统计信息为每个SQL语句选择最佳执行计划。

优化器统计信息包括以下内容：

■Table statistics

–Number of rows

–Number of blocks

–Average row length

■Column statistics

–Number of distinct values (NDV) in column

–Number of nulls in column

–Data distribution (histogram)

–Extended statistics

■Index statistics

–Number of leaf blocks

–Levels

–Clustering factor

■System statistics

–I/O performance and utilization

–CPU performance and utilization

数据库将优化程序统计信息存储在数据字典中。 您可以使用数据字典视图访问这些统计信息。

由于数据库中的对象可能会不断更改，因此必须定期更新统计信息，以便准确描述这些对象。 Oracle数据库自动维护优化器统计信息。

您可以使用DBMS_STATS包手动维护优化程序统计信息。 例如，您可以保存和还原统计信息的副本。 您可以从一个数据库导出统计信息，并将这些统计信息导入另一个数 例如，您可以将统计信息从生产系统导出到测试系统。 您还可以锁定统计信息以防止更改。

二. Managing Automatic Optimizer Statistics Collection

管理自动优化器统计信息收集

Oracle建议您启用自动优化程序统计信息收集。在这种情况下，数据库会自动收集缺少或过时统计信息的表的优化程序统计信息。如果表需要新的统计信息，则数据库会为表和相关索引收集它们。

自动收集消除了许多与管理优化器相关的手动任务。它还显着降低了因缺少或过时的统计数据而导致执行计划不佳的风险。

自动优化程序统计信息收集调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC过程。此内部过程与使用GATHER AUTO选项的DBMS_STATS.GATHER_DATABASE_STATS过程类似。主要区别在于GATHER_DATABASE_STATS_JOB_PROC优先处理需要统计信息的数据库对象，以便在维护窗口关闭之前首先处理最需要更新统计信息的对象。

本节包含以下主题：

■启用和禁用自动优化程序统计信息收集

■收集统计信息时的注意事项

2.1 Enabling and Disabling Automatic Optimizer Statistics Collection

自动维护任务基础结构（称为AutoTask）计划在Oracle Scheduler窗口中自动运行的任务，称为维护窗口。 默认情况下，为一周中的每一天安排一个窗口。 自动优化程序统计信息收集作为AutoTask的一部分运行，默认情况下启用以在所有预定义的维护窗口中运行。

如果由于某种原因禁用了自动优化程序统计信息收集，则可以使用DBMS_AUTO_TASK_ADMIN程序包中的ENABLE过程启用它：

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

/

如果要禁用自动优化程序统计信息收集，可以使用DBMS_AUTO_TASK_ADMIN程序包中的DISABLE过程将其禁用：

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection'

, operation => NULL

, window_name => NULL

);

END;

/

自动优化程序统计信息收集依赖于修改监视功能，如第13-8页上的“确定过时统计信息”中所述。 如果禁用此功能，则自动优化程序统计信息收集作业无法检测过时统计信息。 当STATISTICS_LEVEL参数设置为TYPICAL或ALL时，将启用此功能。 TYPICAL是默认值。

2.2 收集统计数据时的注意事项

本节讨论：

■何时使用手动统计

■还原以前版本的统计信息

■锁定统计信息

2.2.1 何时使用手动统计

对于大多数以适中速度修改的数据库对象，自动优化器统计信息收集应该足够了。但是，在某些情况下，收集可能不够。由于集合在维护窗口期间运行，因此对全天显著修改的表的统计信息可能会过时。通常有两种类型的此类对象：

■在一天中删除或截断并重​​建的易失性表。

■作为大批量加载目标的对象，它们会向对象的总大小添加10％或更多。

对于高度易变的表，有两种方法：

■这些表的统计信息可以为null。当Oracle数据库遇到没有统计信息的表时，数据库会动态收集必要的统计信息，作为查询优化的一部分。 OPTIMIZER_DYNAMIC_SAMPLING参数控制此动态统计信息功能。将此参数设置为值2（默认值）或更高。

您可以通过删除然后锁定统计信息将统计信息设置为null：

BEGIN

DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');

DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');

END;

/

■可以将这些表的统计信息设置为表示表的典型状态的值。当表具有代表性行数时，您应该收集有关表的统计信息，然后锁定统计信息。

这可能比自动优化器统计信息收集更有效，因为在隔夜批处理窗口期间在表上生成的任何统计信息可能不是白天工作负载的最合适的统计信息。

对于批量加载的表，请在加载过程后立即对表运行统计信息收集过程。最好将这些过程作为运行批量加载的同一脚本或作业的一部分运行。

数据库可以通过以下方式收集外部表的统计信息：

■GATHER_TABLE_STATS procedure

■GATHER_SCHEMA_STATS procedure

■GATHER_DATABASE_STATS procedure

■Automatic optimizer statistics collection processing

如果您使用的是GATHER_TABLE_STATS，则显式将ESTIMATE_PERCENT选项设置为NULL，100或AUTO_SAMPLE，因为不支持对外部表进行采样。由于数据库不允许对外部表进行数据操作，因此数据库永远不会将外部表的统计信息标记为过时。如果外部表需要新的统计信息，例如，因为基础数据文件发生更改，则删除现有统计信息并重新收集它们。

如果通过将STATISTICS_LEVEL设置为BASIC来禁用监视功能，则自动优化程序统计信息收集无法检测过时统计信息。在这种情况下，您必须手动收集统计信息。请参阅第13-8页的“确定过时统计信息”以了解自动监视工具。

系统统计信息是您必须手动收集的另一种统计信息。数据库不会自动收集这些统计信息。有关更多信息，请参阅第13-11页的“系统统计信息”。

您必须使用GATHER_FIXED_OBJECTS_STATS过程手动收集有关固定对象（例如动态性能表）的统计信息。固定对象记录当前数据库活动。您应该在数据库具有代表性活动时收集统计信息。

2.2.2 恢复以前版本的统计信息

每当修改字典中的统计信息时，将自动保存旧版本的统计信息以供将来恢复。 您可以使用DBMS_STATS包的RESTORE过程还原统计信息。 有关更多信息，请参阅第13-20页的“还原以前版本的统计信息”。

2.2.3 锁定统计数据

在某些情况下，您可能希望防止DBMS_STATS_JOB进程在表或模式上收集任何新统计信息，例如第13-3页上的“何时使用手动统计信息”中讨论的高度易失性表。 在这些情况下，DBMS_STATS包提供了锁定表或模式的统计信息的过程。 有关详细信息，请参阅第13-21页的“锁定表或架构的统计信息”。