Managing Optimizer Statistics

・ Database Performance Tuning Guide - Managing Optimizer Statistics

统计信息相关概念

  • 什么是统计信息？

    Oracle 数据库中的统计信息存储在数据字典中，从多个维度描述了 Oracle 数据库里的详细信息。

  • 统计信息作用是什么？

    CBO 优化器会利用统计信息计算目标 SQL 各种可能、不同的执行路径的成本，并从中选择一条最小的执行路径来作为目标 SQL 的执行计划。（统计信息不准确，SQL 的执行计划会走错，SQL 会出现性能问题）。

统计信息分类

  • 表的统计信息

    表的统计信息主要包含表的总行数（num_rows）, 表块数（blocks）以及平均长度（avg_row_len）。

  • 索引的统计信息

    索引的统计信息描述了索引的详细信息，所以索引的层级、叶子块的数量、聚簇因子等。

  • 列统计信息

    列统计信息记录了列的 distinct 值的数量、null 的数量、列最小值和列最大值。

  • 系统统计信息

    系统统计信息是描述了 oracle 数据库服务器的系统处理能力，包含 cpu 和 I/O 两个方面，可以通过这两个方面来知道数据库服务器的实际处理能力。

  • 数据字典统计信息

    描述了字典基表（tab,ind 等），数据字典基表上的索引。

  • 内部对象统计信息

    记录了一些内部表（x 系统表）的详细信息，它的维度和普通表的统计信息类似，但是其表块数为 0，x 系统表）的详细信息，它的维度和普通表的统计信息类似，但是其表块数为 0，x 系统表）的详细信息，它的维度和普通表的统计信息类似，但是其表块数为 0，x 实际上只是 oracle 自定义的内存结构，不占用实际物理空间。

收集统计信息方法选择

  1. analyze
  2. dbms_stats

analyze 命令收集

Oracle 7 开始，通过 analyze 命令来收集表、索引、列的统计信息。以下是一些典型的用法。

采样比为 15%，对 test 表搜集统计信息。

analyze table test estimate statistics smaple 15 percent for table;

计算模式 对 test 表收集统计信息，该模式下，只有 test 表有统计信息，test 的列和索引都没有统计信息，且收集的统计信息和实际情况是一致的。

analyze table test compute statistics for table;

计算模式下对 test 表 只对列 1 和列 2 收集统计信息，且之前的覆盖掉之前的收集统计信息。

analyze table test compute statistics for cloumns col1,col2;

计算模式下同时对表和列 1 和列 2 收集统计信息。

analyze table test compute statistics for table for cloumns col1,col2;

收集索引统计信息。

analyze index idx_1 statistics;

删除统计信息。

analyze table test delete statistics;

删除 test 表、列、所有索引的统计信息。

analyze index idx_1 delete statistics;
details/106810013

dbms_stats 包收集统计信息

oracle 8.1.5 开始，dbms_stats 被广泛应用于统计信息收集，也是 Oracle 官方推荐的方式。dbms_stats 有 4 个存储过程。

gather_table_stats: 用于收集目标表、列和索引的统计信息。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 15, --采样比是15%
                                method_opt       => 'for table',
                                cascade          => false, --默认是true，级联收集
        );
END;
/

gather_index_stats: 用于收集指定统计信息。

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(ownname          => 'SCOTT',
                                indname          => 'IDX_TEST',
                                estimate_percent => 100,
        );
END;
/

gather_schema_stats: 用于收集指定 schema 下的所有对象统计信息。

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname    => 'SCOTT',
                                cascade     => true,
                                granularity => 'ALL', --收集分区表
        );
END;
/

gather_database_stats: 用于收集全库所有的统计信息。

BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => 100,
                                   degree           => 8,
                                   cascade          => true,
                                   granularity      => 'ALL', --收集分区表
        );
END;
/

DBMS_STATS 重要参数详解

ownname: 表示表的拥有者，不区分大小写。

tabname: 表示表名字，不区分大小写。

granularity: 表示收集统计信息的粒度，该选项只对分区表生效，默认为 AUTO，表示让 Oracle 根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项，我们一般采用 AUTO 方式，也就是数据库默认方式，因此在后面的脚本中，省略该选项。

estimate_percent: 表示采样率，范围是 0.000 001～100。这个参数主要是用于 CBO 估算表的总行数，采样率越高，CBO 估算的表行数越接近于真实值，执行计划越能走正确。估算总行数 = 样本大小 (DBA_TAB_STATISTICS.SAMPLE_SIZE）*100 / 采样率 (estimate_percent)。一般对小于 1GB 的表进行 100% 采样，因为表很小，即使 100% 采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有 100% 采样，可能会导致统计信息不准。因此建议对小表 100% 采样。我们一般对表大小在 1GB～5GB 的表采样 50%，对大于 5GB 的表采样 30%。如果表特别大，有几十甚至上百 GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于 30%。<1GB 建议采样比 100%；1GB～5GB 建议采样比 50%；>5GB 建议采样比 30%。

method_opt: 用于控制收集直方图策略。直方图简单来说就是数据库了解表中某列的数据分布，从而更正确的走更优的执行计划。method_opt => 'for all columns size 1' 表示所有列都不收集直方图；method_opt => 'for all columns size skewonly' 表示对表中所有列收集自动判断是否收集直方图。选择率非常高的列和 null 的列不会收集（谨慎使用）；method_opt => 'for all columns size auto' 表示对出现在 where 条件中的列自动判断是否收集直方图；method_opt => 'for all columns size repeat' 表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。在实际工作中，当系统趋于稳定之后，使用 REPEAT 方式收集直方图。

no_invalidate: 表示共享池中涉及到该表的游标是否立即失效，默认值为 DBMS_STATS.AUTO_INVALIDATE，表示让 Oracle 自己决定是否立即失效。建议将 no_invalidate 参数设置为 FALSE，立即失效。因为发现有时候 SQL 执行缓慢是因为统计信息过期导致，重新收集了统计信息之后执行计划还是没有更改，原因就在于没有将这个参数设置为 false。

degree: 表示收集统计信息的并行度，默认为 NULL。如果表没有设置 degree。如果表没有设置 degree，收集统计信息的时候后就不开并行；如果表设置了 degree，收集统计信息的时候就按照表的 degree 来开并行。可以查询 DBA_TABLES.degree 来查看表的 degree，一般情况下，表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。

cascade: 表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为 DBMS_STATS.AUTO_CASCADE，表示让 Oracle 自己判断是否级联收集索引的统计信息。

analyze 和 dbms_stats 的区别

analyze 命令不能正确的收集分区表的统计信息，而 dbms_stats 包却可以。
analyze 命令不能并行收集统计信息，而 dbms_stats 包可以。
analyze 命令不能收集 x$ 的统计信息。

所以选择推荐使用 dbms_stats 来对表进行统计信息收集，推荐使用收集统计信息脚本：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                granularity      => 'ALL',
                                cascade          => TRUE);
END;
/