1 优化器统计信息作用及内容

SQL性能调优:依赖于收集准确的统计信息
优化器统计信息包括表、列、索引和系统统计信息。表和索引的统计信息存储在数据字典中。这些统计数据并不是实时更新的数据。而它们为优化器提供了数据存储和分布信息，优化器使用它来决定如何访问数据。
所收集的统计数据包括:

• Size of the table or index in database blocks
• Number of rows
• Average row size and chain count (tables only)
• Height and number of deleted leaf rows (indexes only)

优化器统计信息由一个自动维护作业自动收集，默认情况下，该作业在预定义的维护窗口期间每天运行一次。系统级统计信息是优化器使用的操作系统特征，这些统计信息不是自动收集的。
系统级统计信息与AWR中的数据级统计信息不同。

2 统计信息维护

收集的方式：
收集统计数据的方法:
自动:自动维护任务
手动:DBMS_STATS包
通过设置数据库初始化参数
通过从另一个数据库导入统计信息。
优化器统计信息是关于数据库对象的特定细节的数据集合。这些统计信息对于查询优化器为每个SQL语句选择最佳执行计划至关重要。这些统计数据是定期收集的，不会在两次收集之间发生变化。

2.1 自动收集

收集优化器统计信息的推荐方法是允许Oracle数据库服务器自动收集统计信息。自动维护任务可以在数据库创建时自动创建，并由调度器管理。它收集数据库中默认情况下缺少或失效的优化器统计信息的所有对象的统计信息。可以通过自动维护任务页面更改默认配置。
系统统计信息向查询优化器描述系统的硬件特征，如I/O和CPU性能和利用率。在选择执行计划时，优化器会估计每个查询所需的I/O和CPU资源。系统统计信息使查询优化器能够更准确地估计I/O和CPU成本，从而选择更好的执行计划。使用DBMS_STATS收集系统统计信息。

2.2 手动收集

如果选择不使用自动统计信息收集，则必须在所有模式(包括系统模式)中手动收集统计信息。如果数据库中的数据定期更改，还需要定期收集统计信息，以确保统计信息准确地表示数据库对象的特征。要手动收集统计信息，请使用DBMS_STATS包。这个PL/SQL包还用于修改、查看、导出、导入和删除统计信息。

2.3 设置初始化参数

还可以通过数据库初始化参数管理优化器和系统统计信息收集。例如:
OPTIMIZER_DYNAMIC_SAMPLING参数控制优化器执行的动态采样级别。当表和相关索引不可用或太过时而无法信任时，可以使用动态抽样来估计它们的统计信息。当收集的统计信息不能使用或可能导致估算中出现重大错误时，动态抽样还会估计单表谓词的选择性。
STATISTICS_LEVEL参数控制数据库中所有主要的统计信息收集或建议，并设置数据库的统计信息收集级别。该参数的值有BASIC、TYPICAL和ALL。可以查询V$STATISTICS_LEVEL视图，以确定STATISTICAL_LEVEL参数会影响哪些参数。
注意:将STATISTICS_LEVEL设置为BASIC会禁用许多自动特性，不建议这样做。

2.4 最佳实践建议

使用自动维护任务来收集优化器统计信息。要启用收集优化器统计信息的任务，必须确保将STATISTICS_LEVEL初始化参数设置为TYPICAL或ALL。
在某些时候，可能需要手动收集统计信息，例如当表的内容在自动收集作业之间发生了很大变化，以至于统计信息不再准确。对于在24小时内大小变化超过10%的大型表，这种情况很常见。
最佳实践技巧:
经常收集统计信息，这样在收集期间表的变化不会超过10%。这可能需要手工统计信息收集或额外的维护窗口。
统计信息可以通过使用Enterprise Manager或DBMS_STATS包手工收集。可以只使用DBMS_STATS包来收集系统统计信息。系统统计信息向查询优化器描述系统的硬件特征，如I/O和CPU性能和利用率。

3 操作

可以使用DBMS_STATS包直接收集优化器统计信息:

SQL> EXEC dbms_stats.gather_table_stats('HR','EMPLOYEES');
SQL> SELECT num_rows FROM dba_tables
  2  WHERE owner='HR' AND table_name = 'EMPLOYEES';
  NUM_ROWS
----------
       214
复制

注意，行数现在正确地反映了收集统计信息时表中的内容。DBMS_STATS还支持手动收集整个模式甚至整个数据库的统计信息。
除非工作负载发生显著变化，否则系统统计信息不会发生变化。因此，系统统计数据不需要频繁调整。DBMS_STATS.GATHER_SYSTEM_STATS过程将在指定的时间段内收集系统统计信息，或者可以开始收集系统统计信息，然后再次调用来停止收集。
最佳实践技巧:在创建数据库时使用以下命令:

SQL > EXEC dbms_stats.gather_system_stats(“NOWORKLOAD”);
复制

NOWORKLOAD选项会花费几分钟时间(取决于数据库的大小)，并捕获I/O特征的估计数，比如平均读寻道时间和I/O传输率。
注意，行数现在正确地反映了收集统计信息时表中的内容。DBMS_STATS还支持手动收集整个模式甚至整个数据库的统计信息。