数据库管理-第九十八期 统计信息是多么重要（20230812）

数据库管理-第九十八期 统计信息是多么重要（20230812）

每天通过EM可视化巡视数据库执行情况，发现那些执行比较长的语句要么是索引没用上、要么是索引没建。但更多的是发现执行计划中“估计的行数”与“行数”（执行的）差距很大（长时间执行后前者远大于后者or后者远大于前者）、大表联查用的nested loop、活动查询发现只分了1个单位的CPU资源在慢慢跑等等。这种情况下往往是对应表或索引的统计信息出现了问题。
其实在以前的文章中也经常提到统计信息的重要性，但是无论我多么努力、用心的强调统计信息的重要性，但业务厂商那边依然经常因此出现问题，这次是在忍不住了，详细的说一下。

1 概念

统计信息，又称优化器统计信息（Optimizer Statistics），Oracle数据库的优化器统计信息是对数据库以及其对象（object）的细节的描述。
优化器成本模型依赖于收集的有关查询中涉及的对象以及运行查询的数据库和主机的统计信息。
优化器使用统计信息来估计从表、分区或索引中检索到的行数（和字节数）。优化器估计访问的成本，确定可能的计划的成本，然后选择成本最低的执行计划。
表、列、索引和系统的统计信息存放在数据字典中，并通过数据字典视图进行访问。

从统计信息的概念来看，优化器制定它认为最优的执行计划需要通过统计信息的内容来生成，除了语句的执行流程以外，还会根据各类统计信息来分配软硬件资源，比如CPU、IO和内存等。

2 统计信息类型

统计信息有以下一些类型：

1. 表统计信息：
   • 行数量
   • 块数量
   • 平均行长度
2. 列统计信息
   • 列中唯一值（NDV）数量
   • 空值数量
   • 数据分布（直方图）
   • 扩展统计信息
3. 索引统计信息
   • 叶块数量
   • 索引层级数量（高度）
   • 索引聚簇因子
4. 系统统计信息
   • IO性能与占用率
   • CPU性能与占用率

3 收集统计信息的方法

3.1 DBMS_STATS包

使用DBMS_STATS PL/SQL包来手机和管理统计信息。可以控制收集统计信息的内容和方式，包括并行度、采样模式以及分区表统计信息收集粒度。
一般来说数据库会通过自动统计信息收集任务在规定的窗口时间段内来收集、更新数据库相关的统计信息，并可通过下面的语句来开启、关闭此任务和查询此任务状态：

BEGINDBMS_AUTO_TASK_ADMIN.ENABLE (  client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/BEGINDBMS_AUTO_TASK_ADMIN.DISABLE (  client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;
/COL CLIENT_NAME FORMAT a31
SELECT CLIENT_NAME, STATUS FROM   DBA_AUTOTASK_CLIENT WHERE  CLIENT_NAME = 'auto optimizer stats collection';

这里需要注意的一点，如果数据库足够大，在单次（即一天）的窗口时间内，自动统计信息收集任务只能完成部分数据库对象的统计信息，因此可能出现维护窗口后仍有部分表统计信息不准确的情况。

3.2 额外的动态统计信息

默认情况下，当优化器统计信息丢失、过时或不足时，数据库会在解析过程中自动收集动态统计信息。数据库使用递归SQL来扫描表块的随机小样本。
一般来说我们不建议数据库使用过程中触发动态统计信息，因为它会对语句造成额外的开销。但在下列情况中动态统计信息是有用的：
- 由于谓词复杂，执行计划是次优的。
- 采样时间只是查询总执行时间的一小部分。
- 查询执行多次，以便摊销采样时间。

3.3 在线统计信息收集

在某些情况下，DDL和DML操作会触发在线统计信息收集：

3.3.1 批量数据处理的在线统计信息收集

数据库可以在以下类型的大量数据加载期间自动收集表统计信息： INSERT INTO … SELECT使用direct path插入和CREATE TABLE AS SELECT时。
默认情况下，并行插入使用direct path插入。您可以通过添加hint /*+APPEND*/的方式强制direct path插入。

3.3.2 目的

分析类应用通常需要将大量数据加载到数据库中。在12c之前版本最佳实践是在操作后手动收集统计信息。但是，由于疏忽或等待自动统计信息收集任务执行，许多统计信息没有及时收集。这是执行计划欠佳的主要原因。（当然在实际使用过程中，发现及时是12c之后的版本依然会出现相关操作后统计信息未更新的现象，部分源自于下面“限制”部分，但也有小概率不是）
带来的好处：

• 提升性能：在加载期间收集统计信息可以避免额外的表扫描来收集表的统计信息。
• 改进可管理型：无需用户干预可以在批量数据加载后收集统计信息。

3.3.3 插入分区表时收集全局统计信息

在将数据插入分区时，数据库会在插入过程中收集分区表的全局统计信息。

3.3.4 批量数据加载后创建直方图

在线统计信息收集后，数据库不会自动创建直方图，如果直方图是需要的可以通过下面的语句实现自动收集直方图统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'MYT', options=>'GATHER AUTO');

3.3.5 限制

在某些情况下，大数据量加载不会自动收集统计信息。
具体来说，当系列任何条件适用于目标表、分区或子分区时，大数据量加载不会自动收集统计信息：

• 对象已包含数据。大数据量加载仅在对象为空时自动在线收集统计信息。
• 它在Oracle拥有的schema中，如sys。
• 下列类型之一：嵌套表、索引组织表（IOT）、外部表或者定义为ON COMMIT DELETE ROWS的全局临时表。
• PUBLISH设置为FALSE。
• 其他统计信息已锁定。
• 使用多个insert语句加载数据。

3.3.6 用户控制的大数据量加载后的在线统计信息收集

可以使用hint GATHER_OPTIMIZER_STATISTICS来关闭默认启用的大数据量加载后的在线统计信息收集：

CREATE TABLE employees2 AS SELECT /*+NO_GATHER_OPTIMIZER_STATISTICS*/ * FROM employees

3.4 分区维护操作在线统计信息收集

Oracle 数据库在特定分区维护操作期间为联机统计信息提供类似的支持。
对于MOVE、COALESCE和MERGE操作，数据库在下列情况下在全局和分区层面维护统计信息：

• 如果分区使用增量或非增量统计信息，数据库会直接更新全局统计信息中的块数量的值。需要注意这个操作不是统计信息收集操作。
• 数据库为新生成的分区收集统计信息。如果启用增量统计信息，则数据库维护分区概要。
  对于TRUNCATE或DROP PARTITION，数据库会更新全局统计信息中的块数量和行数量的值。这个操作同样不是统计信息收集操作，统计信息更新同样在使用增量或非增量统计信息情况下。

3.5 实时统计信息收集

Oracle可以在常规DML操作期间自动收集统计信息。其主要作用是减少优化器被过时统计信息误导的可能性。在DML操作时，数据库会动态计算最基本统计信息的值。这里需要注意的是实时统计信息收集是增强而不是取代传统统计信息，仍要确保自动统计信息收集任务正确执行。
这个作为19c带来的新特性，在Oracle数据库管理每周一例-第十四期 19c需要调整的参数及操作中也提到过，祖上一些版本开启该功能会出现锁表的现象，所以我一般是通过OPTIMIZER_REAL_TIME_STATISTICSTRUEFALSE初始化参数关闭的。
如有需详细了解相关内容可查阅官方文档10.3.3.3 Real-Time Statistics。

4 统计信息来源

数据库在不同时间、从不同来源收集统计信息。
主要来自：

• DBMS_STATS执行（自动或手动）
  这个PL/SQL包是收集统计信息的主要方法。
• SQL编译
  在 SQL 编译期间，数据库可以扩充以前由DBMS_STATS收集的统计信息。在此阶段，数据库运行其他查询，以获取有关表中有多少行满足 SQL 语句中的WHERE子句谓词的更准确信息。
• SQL执行
  在执行期间，数据库可以进一步扩充以前收集的统计信息。在此阶段，Oracle 数据库收集 SQL 语句执行过程中每个行源生成的行数。在执行结束时，优化程序确定估计的行数是否足够准确，以保证在下一次语句执行时重新解析。如果将游标标记为重新分析，则优化程序将使用上一次执行的实际行计数，而不是估计值。
• SQL Profile
  SQL Profile是查询的辅助统计信息的集合。配置文件将这些补充统计信息存储在数据字典中。优化程序在优化期间使用 SQL 配置文件来确定最佳计划。

数据库将优化器统计信息存储在数据字典中，并根据需要更新或替换它们。您可以在数据字典视图中查询统计信息。
更多的关于数据库是如何收集统计信息的内容可以查看官方文档When the Database Gathers Optimizer Statistics。

5 DBMS_STATS

这里主要引申几个常见的统计信息收集语句（其余可以详见173 DBMS_STATS）：

5.1 收集数据库统计信息

DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent NUMBER   DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),block_sample     BOOLEAN  DEFAULT FALSE,method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),stattab          VARCHAR2 DEFAULT NULL, statid           VARCHAR2 DEFAULT NULL,options          VARCHAR2 DEFAULT 'GATHER',objlist          OUT      ObjectTab,statown          VARCHAR2 DEFAULT NULL,gather_sys       BOOLEAN  DEFAULT TRUE,no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),obj_filter_list ObjectTab DEFAULT NULL);DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent NUMBER   DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample     BOOLEAN  DEFAULT FALSE,method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),stattab          VARCHAR2 DEFAULT NULL, statid           VARCHAR2 DEFAULT NULL,options          VARCHAR2 DEFAULT 'GATHER',statown          VARCHAR2 DEFAULT NULL,gather_sys       BOOLEAN  DEFAULT TRUE,no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),obj_filter_list ObjectTab DEFAULT NULL);-- 一般执行：
exec dbms_stats.gather_database_stats(cascade=>true,degree=>32);

5.2 收集schema统计信息

DBMS_STATS.GATHER_SCHEMA_STATS ( ownname          VARCHAR2, estimate_percent NUMBER   DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample     BOOLEAN  DEFAULT FALSE, method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), stattab          VARCHAR2 DEFAULT NULL, statid           VARCHAR2 DEFAULT NULL, options          VARCHAR2 DEFAULT 'GATHER', objlist          OUT      ObjectTab,statown          VARCHAR2 DEFAULT NULL, no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE')),force             BOOLEAN DEFAULT FALSE,obj_filter_list  ObjectTab DEFAULT NULL);DBMS_STATS.GATHER_SCHEMA_STATS ( ownname          VARCHAR2, estimate_percent NUMBER   DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample     BOOLEAN  DEFAULT FALSE, method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), stattab          VARCHAR2 DEFAULT NULL, statid           VARCHAR2 DEFAULT NULL, options          VARCHAR2 DEFAULT 'GATHER', statown          VARCHAR2 DEFAULT NULL, no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE'), force            BOOLEAN DEFAULT FALSE,obj_filter_list  ObjectTab DEFAULT NULL);-- 一般执行：
exec dbms_stats.gather_schema_stats(ownname=>'USERNAME',cascade=>true,degree=>32);

5.3 收集表统计信息

DBMS_STATS.GATHER_TABLE_STATS (ownname          VARCHAR2, tabname          VARCHAR2, partname         VARCHAR2 DEFAULT NULL,estimate_percent NUMBER   DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')),block_sample     BOOLEAN  DEFAULT FALSE,method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),stattab          VARCHAR2 DEFAULT NULL, statid           VARCHAR2 DEFAULT NULL,statown          VARCHAR2 DEFAULT NULL,no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (get_param('NO_INVALIDATE')),stattype         VARCHAR2 DEFAULT 'DATA',force            BOOLEAN  DEFAULT FALSE,context          DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operativeoptions          VARCHAR2 DEFAULT get_param('OPTIONS'));-- 一般执行：
exec dbms_stats.gather_table_stats(ownname=>'USERNAME',tabname=>'TABLE_NAME',cascade=>true,degree=>8,method_opt=>'xxxx');
-- 其中method_opt可以指定收集统计信息的方式，比如多列统计信息、直方图统计信息等。

5.4 收集索引统计信息

DBMS_STATS.GATHER_INDEX_STATS (ownname          VARCHAR2, indname          VARCHAR2, partname         VARCHAR2 DEFAULT NULL,estimate_percent NUMBER   DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')),stattab          VARCHAR2 DEFAULT NULL, statid           VARCHAR2 DEFAULT NULL,statown          VARCHAR2 DEFAULT NULL,degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')),force            BOOLEAN DEFAULT FALSE);-- 我一般是在收集表统计信息的同时收集索引统计信息

总结

没啥说的，知道写了些啥。