Oracle分析表和索引

dbms_stats包问世以后，Oracle专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用老式的分析表和dbms_util

analyze table tablename compute statistics;
analyze index indexname compute statistics;

对于使用CBO很有好处，可以使用更可靠的table信息，从而执行计划也可以更准确一些，在10g会自动analyze，之前的版本需要手动定期

生成统计信息,,选择合理的执行计划..

Oracle的online document这样描述analyze的作用：
Use the ANALYZE statement to collect non-optimizer statistics, for example, to:

1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3、Identify migrated and chained rows of a table or cluster.

对于收集统计信息这块，我一般使用dbms_stats这个package，收集统计信息的做用使CBO方式的优化选择执行计划更准确。

统计信息最好定期收集，以业务的不同确定不同的收集周期 

dbms_stats包问世以后，Oracle专家可通过一种简单的方式来为CBO收集统计数据。目前，已经不再推荐你使用老式的分析表和dbms_utility方法来生成CBO统计数据。那些古老的方式甚至有可能危及SQL的性能，因为它们并非总是能够捕捉到有关表和索引的高质量信息。CBO使用对象统计，为所有SQL语句选择最佳的执行计划。

dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

清单A展示了dbms_stats的一次示范执行情况，其中使用了options子句。

execdbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
)

为了充分认识dbms_stats的好处，你需要仔细体会每一条主要的预编译指令（directive）。下面让我们研究每一条指令，并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据。

使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式：

Luminal

用AI以光速清理、转换和分析电子表格

72

查看详情

注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，，发生了多少次插入、更新和删除操作。

以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。

dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。

某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图。使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gather auto。begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;