oracle 为CBO提供统计数据

只有在对数据库对象有详细了解的情况下，CBO才可能遵循最佳的执行路径。从Oracle Database10g开始，提供这些统计数据的推荐方式是让数据库自动收集统计数据。这就是众所周知的自动优化程序统计数据收集特性，我们在第17章介绍过。也可以利用DBMS_STATS程序包为优化程序手动提供统计数据。请注意，统计数据无论是自动收集还是手动收集，Oracle都使用DBMS_STATS程序包来收集统计数据。

使用DBMS_STATS收集统计数据
尽管让数据窿自动收集优化程序统计数据是推荐的方法，但仍然可以使用DBMS_STATS程序包来手动收集优化程序统计数据。

提示 对于大表，Oracle建议只对数据进行采样，而不是考察所有的数据。Oracle允许你指定要采样的行或块，而且有时建议采样的大小要低于百分之五。用于评估的默认釆样大小也很低。Oracle还建议使用DBMS_STATS自动采样过程。

但是，利用采样数据收集的统计数据是不可靠的。所收集的评估值为30%和50%的优化程序统计数据在性能上的差异有时令人震惊。一般来讲，总是选择对所有对象收集全部统计数据的选项，即使收集频率不如仅对数据进行釆样的频率高也是如此。

在以下条件下，必须手动收集优化程序统计数据:
 使用外部表时；
 需要收集系统统计数据时；
 对固定对象收集统计数据时，如动态性能表（对于动态表，应当使用GATHER_FIXED_OBJECTS_STATS过程来收集优化程序统计数据）；
 运行大负荷作业之后，因为大负荷作业使得自动收集的统计数据不具有代表性。

以下几节说明如何使用DBMS_STATS程序包搜集统计数据。

注解 Oracle建议不要使用旧的ANALYZE语句为优化程序收集统计数据，而要使用DBMS_STATS程序包.仍然保留ANALYZE命令只是为了向后兼容，对于非优化程序统计数据的收集任务，必须使用该命令，如验证对象的合法性（使用VALIDATE子句），或者鉴别表中被移植的且链接的行（使用LIST CHAINED ROWS子句）.

•存储优化程序统计数据
可以使用DBMS_STATS程序包中各种不同的过程来收集优化程序统计数据。这些过程多数都有3个公共属性：STATOWN、STATTAB和STATIDo这些属性可以使你将收集到的统计数据保存在一个属于某个用户的数据库表中。默认情况下，这些属性的值为null,而旦，如果你的目的是为优化程序收集统计数据，那么就不应当为这些属性提供任何值。当忽略这些属性时，所收集到的优化程序统计数据将被默认存储在数据字典表中，Oracle优化程序可以访问到该表。

•收集统计数据
DBMS_STATS程序包有几个过程允许用户在不同的级别上收集数据。用于数据库表和索引数据的主要的数据收集过程如下所示。
 GATHER_DATABASE_STATISTICS 为数据库中的所有对象捜集统计数据；
 GATHER_SCHEMA_STATISTICS 为整个模式搜集统计数据；
 GATHER_TABLE_STATISTICS 为一个表及其索引搜集统计数据；
 GATHER_INDEX_STATISTICS 为一个索引搜集统计数据。

让我们首先使用DBMS_STATS程序包为一个模式收集统计数据，然后为一个单独的表收集统计数据。
在模式级上收集统计数据：
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'hr');

在表级上收集统计数据：
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('hr','employees');

GATHER_DATABASE_STATISTICS过程为整个数据库收集优化程序统计数据。这可能是使用DBMS_STATS程序包最常见的方法，因为你可以使用这个过程为单个语句的所有数据库对象收集统计数据。
EXECUTE dbms_stats.gather_database_stats(
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
GRANULARITY => ’ALL',
CASCADE => 'TRUE',
OPTIONS => 'GATHER AUTO');
提示 尽管可以使用ESTIMATE_PERCENT属性为一个采样收集优化程序统计数据，该采样的范围为表中行的0.000001%-100%.但还是应当争取为所有的行(使用null值作为该属性的值)收集统计数据。

基于采样收集的统计数据是充满危险的.除非表太大以至于无法在维护窗口内收集所有的统计数据，否则要争取为所有对象收集全部的统计数据，特别是对那些进行了大量DML更改的对象更应如此。

下面对前述的GATHER_DATABASE_STATS过程做一个简要解释：
 该例表明，只可以指定某些属性或参数。输入下面的命令可以看到完整的属性列表：
DESCRIBE DBMS_STATS.CATHER_DATABASE_STATS
 如果没有指定任何属性，Oracle使用这些属性的默认值。即使使用了默认值，为方便査看，还是将这些属性列岀。
 ESTIMATE_PERCENT属性指定了用来进行统计数据估算的行的百分比。这里，我们选择null值作为默认值。这里的null与我们认为的null的含义相反，它表示Oracle对表中的所有行收集统计 数据。这与在传统的ANALYZE命令中使用COMPUTE STATISTICS选项是相同的。这个属性的默认值允许Oracle使用D BMS_STATS.AUTO_SAMPLE_SIZE过程为每个对象估算采样尺寸。
 可以使用METHOD_OPT属性来指定几件事情，包括是否收集直方图。这里，我们选择FOR ALL ColUMNS SIZE AUTO,这是此属性的默认值。
 GRANULARITY属性只能应用于表。ALL值表示为所有表的子分区、分区以及全局统计最收集统计数据。
 CASCADE=>YES选项指定对所有的索引以及相随的表进行统计数据的搜集。
 OPTIONS属性是至关重要的。该属性的大部分重要的值如下：
■GATHER 为所有的对象搜集统计数据，不管它们是否具有过时的还是最新的统计数据。
■GATHER_AUTO 只为那些Oracle认为必要的对象收集统计数据。
■GATHER_EMPTY 只为没有统计数据的对象收集统计数据。
■GATHER_STALE 只为那些过时的对象收集统计数据，通过检查DBA_TAB_MODIFICATIONS视图可以判断对象是否过时。

请注意，还可以按下列格式执行GATHER_DATABASE_STATS过程，结果是同样的：
BEGIN
dbms_stats.gather_database_stats (ESTIWATE_PERCENT => NULL, METHOD_OPT =>'FOR ALL COLUMNS SIZE AUTO',GRANULARITY => 'ALL', CASCADE => 'TRUE', OPTIONS => 'GATHER AUTO');
END;
/

使用以下査询可以检査一个表最近一次分析的时间：
SELECT table_name, last_analyzed FROM dba_tables;

也可以对索引使用类似的查询，使用DBA_INDEXES视图。

提示 要确保将初始化参数JOB_QUEUE_PROCESSES设置为一个正数。如果没有设置此参数，其默认值为0, DBMS_STATS.GATHER_SYSTEM_STATS过程将不会工作。可以动态地对该参数值进行设置。例如, 执行命令ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20.