DBMS_STAT系统包
📄字数 5.6K
👁️阅读量 加载中...
一、概述
DBMS_STAT系统包是一个统计信息管理系统包，用于对数据库表、列对象进行统计信息采集与维护。统计信息是优化器进行SQL执行计划选择的关键依据，主要包括列对象的最大值、最小值、、重复度、离散度、直方图等统计信息，它帮助数据库准确判断表数据的分布情况，从而做出更优的执行决策。
在该系统包中提供了以下接口：
| 包体函数名 | 简要描述 |
|---|---|
| ANALYZE_TABLE | 用于手动收集统计信息，支持全表、指定列 |
| GET_STAT_INFO | 获取收集的表对象统计信息 |
| SET_STAT_INFO | 手动设置列对象的统计信息 |
| SET_ANALYZE_COLUMNS | 开启或者关闭列对象统计 |
| SET_ANALYZE_SCHEME | 设置表对象的分析策略，如定时分析、立即分析等 |
| SET_ANALYZE_PARAM | 表对象的特定分析设置，如只分析索引列、数字列 |
| SET_ANALYZE_OPTIMIZE | 针对索引列，设置表对象的分析参数，如只收集索引的所有叶子块 |
典型应用场景，如：
- 查询缓慢，优化器执行计划异常，可能因为统计信息过旧或不准确，使用ANALYZE_TABLE手动采集统计信息
- 插入、更新大批数据后需更新统计信息，使用ANALYZE_TABLE手动刷新
- 模拟特定分布进行测试时，使用SET_STAT_INFO人工设置列统计值用于调优
- 使用SET_ANALYZE_COLUMNS、SET_ANALYZE_SCHEME、SET_ANALYZE_PARAM、SET_ANALYZE_OPTIMIZE进行自动分析参数设置
提示
SET_ANALYZE_COLUMNS、SET_ANALYZE_SCHEME、SET_ANALYZE_PARAM、SET_ANALYZE_OPTIMIZE是用于自动分析中的参数设置
二、ANALYZE_TABLE
2.1 功能描述
该接口用于对表对象上的列进行统计信息收集。统计信息包括最大值、最小值、重复度、离散值、非空率、直方图。统计信息存储于系统表SYS_COLUMNS中
说明
虚谷数据库V12.7版本引入了基于压缩直方图（Compressed Histogram）的统计功能，能够显著提升查询优化器的选择率估算精度。通过开启直方图统计，系统会自动生成最常见值（MCV，Most Common Values）列表和直方图列表，帮助优化器更好地理解数据分布，即使在数据分布不均匀的情况下也能提供更准确的选择率估算。
2.2 方法声明
sql
PROCEDURE ANALYZE_TABLE (
TABLE_NAME VARCHAR,
FIELD_NAME VARCHAR,
SAMPLE_RATE INTEGER,
HISTOGRAM_NUM INTEGER,
AUTO_ADJUST BOOL DEFAULT TRUE,
PARALLEL INTEGER DEFAULT 1
);2.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定收集的表对象，该参数要求指定模式名 |
| FIELD_NAME | VARCHAR | 无 | 否 | 指定收集的列对象 |
| SAMPLE_RATE | INTEGER | [1,100] | 否 | 采样间隔 |
| HISTOGRAM_NUM | INTEGER | [0,256] | 否 | 直方图桶数 |
| AUTO_ADJUST | BOOL | TRUE/FALSE | 是，默认为TRUE | 自动调整采样间隔 |
| PARALLEL | INTEGER | [1,8] | 是，默认为1 | 并行度 |
- 对于FIELD_NAME参数，其有多个选项：ALL COLUMNS、ALL INDEX COLUMNS、指定多列、单列。ALL COLUMNS表示收集所有列统计信息，ALL INDEX COLUMNS表示收集所有索引列统计信息
提示
1、采样间隔小于1时，系统默认置为1；大于100时，默认置为100；
2、直方图桶数小于0或者大于256时，系统默认置为100；
3、直方图统计信息在以下情况均满足的情况下进行收集：HISTOGRAM_NUM不为0且表数据量>=(300 * HISTOGRAM_NUM)；
4、并行度小于1时，系统默认置为1；大于8时，默认置为8；
2.4 示例
sql
-- 创建表T_ANA
SQL> CREATE TABLE T_ANA (id INT, c1 VARCHAR2(100));
-- 插入数据
SQL> BEGIN
FOR i IN 1..100 LOOP
INSERT INTO T_ANA (id, c1)
VALUES (i, '数据-' || TO_CHAR(i));
END LOOP;
END;
/
-- 收集表T_ANA所有列统计信息（默认不收集直方图）
SQL> EXEC DBMS_STAT.ANALYZE_TABLE('SYSDBA.T_ANA','ALL COLUMNS',1,NULL);
-- 收集表T_ANA指定列C1统计信息，包含直方图，桶数设置为100
SQL> EXEC DBMS_STAT.ANALYZE_TABLE('SYSDBA.T_ANA','c1',1,100);
-- 收集表T_ANA指定列C1统计信息，包含直方图，桶数设置为100,设置并行度为4
SQL> EXEC DBMS_STAT.ANALYZE_TABLE('SYSDBA.T_ANA','C1',1,100,false,4);2.5 场景应用
- 场景一：当数据库中新增表或向表中插入更新大量数据时，此时优化器还未能识别数据的分布特征，可以手动执行ANALYZE_TABLE进行统计信息收集
- 场景二：当执行某些SQL发现执行计划异常时，怀疑是统计信息不准，此时可以针对涉及的表手动收集
- 场景三：自动表分析下，默认允许自动调整采样间隔，可能出现统计信息不准，此时可以指定较小的采样间隔来手动执行表分析
- 场景四：由于直方图默认不收集，但当某些列值分布倾斜严重，导致估算错误，此时可以手动搜集直方图统计信息，并开启直方图优化
三、GET_STAT_INFO
3.1 功能描述
该接口用于获取表上所有列的统计信息
3.2 方法声明
sql
FUNCTION GET_STAT_INFO(TABLE_NAME VARCHAR) RETURN VARCHAR;3.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定收集的表对象，该参数要求指定模式名 |
3.4 示例
sql
-- 查询表T_ANA所有列统计信息
SQL> SELECT DBMS_STAT.GET_STAT_INFO('SYSDBA.T_ANA') FROM dual;
+--------------------------------------------------------------------------------------------------------------------+
| EXPR1 |
+--------------------------------------------------------------------------------------------------------------------+
| TABLE(SYSDBA.T_ANA) TOTAL ROW NUM: 100 +|
| ANALYZE PARAMS: policy=>TIME/ threshold=>0 level=>0 period=>0 +|
| COLUMNS ATTRIBUTES: +|
| col_no:0 col_name:ID type_name:INTEGER ana_flag:0 repeat:1 dist:100 min_val:1 max_val:100 non_null_rate:1 +|
| col_no:1 col_name:C1 type_name:CHAR ana_flag:0 repeat:1 dist:100 min_val:数据-1 max_val:数据-99 non_null_rate:1+|
| |
+--------------------------------------------------------------------------------------------------------------------+
-- TOTAL ROW NUM表示表总数据量
-- ANALYZE PARAMS表示当前表的参数设置，policy为分析策略，threshold为触发分析阈值，level为采样层级，period为分析周期
-- COLUMNS ATTRIBUTES表示每一列的统计信息，分别为列号，列名，列数据类型，是否允许分析，重复度、离散度、最小值、最大值、非空率、最常出现的值、直方图列表（后两者需进行直方图统计）3.5 场景应用
- 查询统计分析数据
四、SET_STAT_INFO
4.1 功能描述
手动设置统计信息，是一种人为干预优化器行为的手段，其通常用于运维和客户支持中指定的明确且必要的应用场景。支持设置列对象的统计值，包括最大值、最小值、重复度、离散度、非空率，直方图不支持手动设置
4.2 方法声明
sql
PROCEDURE SET_STAT_INFO (
TABLE_NAME VARCHAR,
FIELD_NAME VARCHAR,
MAX_VAL VARCHAR,
MIN_VAL VARCHAR,
REPET_RATE DOUBLE,
DISPERSION DOUBLE,
NON_NULL_RATE DOUBLE DEFAULT 0.0
);4.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定设置的表对象，该参数要求指定模式名 |
| FIELD_NAME | VARCHAR | 无 | 否 | 指定设置的列对象 |
| MAX_VAL | VARCHAR | 无 | 否 | 最大值 |
| MIN_VAL | VARCHAR | 无 | 否 | 最小值 |
| REPET_RATE | DOUBLE | 无 | 否 | 重复度 |
| DISPERSION | DOUBLE | 无 | 否 | 离散度 |
| NON_NULL_RATE | DOUBLE | [0.0,1.0] | 是,默认为0.0 | 非空率 |
4.4 示例
sql
-- 对T_ANA表上的C1列设置统计信息
SQL> EXEC DBMS_STAT.SET_STAT_INFO('SYSDBA.T_ANA','C1',100,1,1,100,0.1);4.5 场景应用
- 场景一：若某张超大表收集统计信息太耗时，此时可以直接设置一个合适的值，临时优化执行计划
- 场景二：在测试环境中，人为地制造“倾斜数据”、“空表”等场景，模拟极端数据分布
- 场景三：解决执行计划错误，但又不方便修改SQL的情况
五、SET_ANALYZE_COLUMNS
5.1 功能描述
用于开启或者关闭列统计，避免进行不必要的统计信息收集
5.2 方法声明
sql
PROCEDURE SET_STAT_INFO (
TABLE_NAME VARCHAR,
FIELD_NAME VARCHAR,
STATUS TINYINT
);5.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定收集的表对象，该参数要求指定模式名 |
| FIELD_NAME | VARCHAR | 无 | 否 | 指定收集的列对象 |
| STATUS | TINYINT | 0/1/2 | 否 | 0表示未指定，1表示允许，2表示禁止 |
5.4 示例
sql
-- 将表T_ANA上的C1列显式设置为允许分析
SQL> EXEC DBMS_STAT.SET_ANALYZE_COLUMNS('SYSDBA.T_ANA','c1',1);5.5 场景应用
- 场景一：在某些表关联复杂、SQL性能敏感，当前统计信息表现稳定的情况下，自动表分析更新统计信息可能导致执行计划突变，此时可以锁定该表统计信息，禁止分析
- 场景二：某些表极大，分析成本过高，此时可以跳过这些表，提升统计任务整体效率
六、SET_ANALYZE_SCHEME
6.1 功能描述
用于自动分析时控制表对象上的行为，包括定时分析、立即分析、优化分析
6.2 方法声明
sql
PROCEDURE SET_ANALYZE_SCHEME (
TABLE_NAME VARCHAR,
SCHE_NAME VARCHAR,
ENABLE BOOL
);6.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定收集的表对象，该参数要求指定模式名 |
| SCHE_NAME | VARCHAR | ALL/TIME/IMME/OPTI | 否 | TIME表示定时分析，IMME表示立即分析，OPTI表示优化分析，ALL为三者 |
| ENABLE | BOOL | TRUE/FALSE | 否 | TRUE表示允许，FALSE表示禁止 |
6.4 示例
sql
-- 允许T_ANA表对象的定时分析行为
SQL> EXEC DBMS_STAT.SET_ANALYZE_SCHEME('SYSDBA.T_ANA','TIME',TRUE);6.5 场景应用
- 场景一：自动表分析一般设置在每日夜间进行，若某张表不需要进行定时分析，此时可以关闭该表对象的定时分析行为
- 场景二：某业务核心表需要确保即使数据发生变更，执行计划不波动，此时可以关闭表对象的所有分析行为
- 场景三：导入某张表后马上可以进行报表分析，报表依赖最新数据查询，此时可以设置表对象为允许立即分析
- 场景四：当表字段众多，分析全部字段浪费资源，此时可以开启优化分析来仅限索引列
七、SET_ANALYZE_PARAM
7.1 功能描述
用于自动分析时设置表对象的特定分析参数，是实现精细化控制的关键手段，包括设置对象分析模式、分析阈值、采样层级
7.2 方法声明
sql
PROCEDURE SET_ANALYZE_PARAM (
TABLE_NAME VARCHAR,
MODE TINYINT,
THRESHOLD TINYINT,
LEVEL TINYINT
);7.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定收集的表对象，该参数要求指定模式名 |
| MODE | TINYINT | 1/2 | 否 | 分析模式，指定值为1表示只对索引列和显式允许分析的列进行分析，2则表示还需对数字、日期类字段分析 |
| THRESHOLD | TINYINT | [1，100] | 否 | 阈值触发变化率，1-100表示当前变化的数据量占表总数据量的比例 |
| LEVEL | TINYINT | [1,6] | 否 | 采样层级，1-6对应的采样间隔分别为32/16/8/4/2/1,采样间隔越小则需要收集的统计分析数据量越多 |
7.4 示例
sql
-- 设置表T_ANA的分析模式为1，阈值触发率为1%，采样层级为1
SQL> EXEC DBMS_STAT.SET_ANALYZE_PARAM('SYSDBA.T_ANA',1,1,1);7.5 场景应用
- 场景一：当表字段非常多，但大部分字段不参与查询计划，此时可以指定模式MODE只分析索引列等
- 场景二：表数据频繁写入但变动不影响查询计划，此时可以提高阈值触发比例THRESHOLD，变更较大才进行分析
- 场景三：某张表分析时间过长，此时可以兼顾分析时间和质量，采用合适的采样LEVEL
八、SET_ANALYZE_OPTIMIZE
8.1 功能描述
用于自动分析时设置对象的优化分析参数
8.2 方法声明
sql
PROCEDURE SET_ANALYZE_OPTIMIZE (
TABLE_NAME VARCHAR,
PERIOD SMALLINT,
MODE TINYINT
);8.3 参数说明
| 参数名 | 类型 | 取值范围 | 是否可选 | 描述 |
|---|---|---|---|---|
| TABLE_NAME | VARCHAR | 无 | 否 | 指定收集的表对象，该参数要求指定模式名 |
| PERIOD | SMALLINT | [30,1440] | 否 | 优化分析的周期(分钟) |
| MODE | TINYINT | 1/2/3 | 否 | 1表示采集索引列的meta0级块，2表示采集索引列的meta1级块，3表示采集索引的所有叶子块 |
8.4 示例
sql
-- 设置T_ANA表上的分析周期为10分钟，且只采集索引列的meta0级块
SQL> EXEC DBMS_STAT.SET_ANALYZE_OPTIMIZE('SYSDBA.T_ANA',10,1);8.5 场景应用
- 某些场景下，仅需要收集索引列统计信息，且需要加速统计信息收集效率