数据库的统计信息是什么？有什么作用？包含什么内容？怎么查看？如何导入导出？

一、实操环境

• 实例：TESTDB（端口 5239）
• 测试用户：SYSDBA
• 测试表：TEST.EMPLOYEE（约100004行），TEST.DEPT（10行）

二、统计信息是什么？

统计信息是描述数据库中表和索引的数据分布、数据量、列值特征的元数据。查询优化器依赖统计信息来估算执行计划的成本，从而选择最优的SQL执行计划。

三、统计信息的作用

1. 帮助优化器估算表的行数，决定表的访问路径（全表扫描还是索引扫描）。
2. 估算列值的选择率，决定是否使用索引以及连接顺序。
3. 决定是否使用直方图来处理数据分布不均匀的列。
4. 影响连接操作的JOIN顺序和JOIN方法（Nested Loop、Hash Join、Sort Merge等）。

四、统计信息包含的内容

级别	内容
表级	表行数（NUM_ROWS）、数据块数（BLOCKS）、最后分析时间（LAST_ANALYZED）、平均行长度（AVG_ROW_LEN）
列级	不同值数量（NUM_DISTINCT）、NULL值数量（NUM_NULLS）、最小值（LOW_VALUE）、最大值（HIGH_VALUE）、直方图类型（HISTOGRAM）、桶数（NUM_BUCKETS）
索引级	索引层级（BLEVEL）、叶子块数（LEAF_BLOCKS）、聚簇因子（CLUSTERING_FACTOR）

五、实操过程

5.1 查看表级统计信息

命令：

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM DBA_TABLES
WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE';

输出：

行号  TABLE_NAME  NUM_ROWS  BLOCKS  LAST_ANALYZED
----- ---------- --------- ------- --------------
1     EMPLOYEE    NULL      NULL    NULL

LAST_ANALYZED 为 NULL 表示尚未收集统计信息。

5.2 查看列级统计信息

命令：

SELECT COLUMN_NAME, NUM_DISTINCT, NULLABLE, NUM_NULLS, LAST_ANALYZED
FROM DBA_TAB_COL_STATISTICS
WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE'
ORDER BY COLUMN_NAME;

输出：

COLUMN_NAME  NUM_DISTINCT  NULLABLE  NUM_NULLS  LAST_ANALYZED
----------- ------------- --------- ---------- ----------------
AGE          30            Y         0          NULL
DEPT         10            Y         0          NULL
ID           100004        N         0          NULL
NAME         100004        Y         0          NULL

5.3 查看直方图信息

命令：

SELECT COLUMN_NAME, HISTOGRAM, NUM_BUCKETS
FROM DBA_TAB_COL_STATISTICS
WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE';

输出：

COLUMN_NAME  HISTOGRAM  NUM_BUCKETS
----------- ---------- ------------
AGE          NONE       0
DEPT         NONE       0
ID           NONE       0
NAME         NONE       0

5.4 收集统计信息

命令：

CALL DBMS_STATS.GATHER_TABLE_STATS('TEST', 'EMPLOYEE');

输出：

DMSQL 过程已成功完成

遇到的问题及解决

问题：执行 CALL DBMS_STATS.GATHER_TABLE_STATS('TEST', 'EMPLOYEE'); 时命令卡住，无响应。

原因：表上有未提交的活动事务持有锁，导致统计信息收集被阻塞。通过查询 V$TRX 确认存在活动事务（TRX_ID=90154、90156），且数据库处于 SUSPEND 状态。

排查过程：

1. 查看活动事务：

   SELECT ID, SESS_ID, STATUS FROM V$TRX WHERE STATUS = 'ACTIVE';
   

   输出显示存在活动事务 90154 和 90156。
2. 查看对应的会话信息：

   SELECT SESS_ID, USER_NAME, STATE, CLNT_IP, TRX_ID, CREATE_TIME
   FROM V$SESSIONS
   WHERE SESS_ID IN (140700596497896, 140700612296632);
   

   确认会话处于 ACTIVE 状态。
3. 终止阻塞会话：

   CALL SP_CLOSE_SESSION(140700596497896);
   CALL SP_CLOSE_SESSION(140700612296632);
   

4. 确认数据库状态，恢复 OPEN：

   SELECT STATUS$ FROM V$INSTANCE;   -- 如果为 SUSPEND
   ALTER DATABASE OPEN;
   

5. 如果以上方法无效，重启数据库实例：

   pkill -f "dmserver.*5239"
   nohup /home/dmdba/dmdbms/bin/dmserver /home/dmdba/dmdata/test/TESTDB/dm.ini > /home/dmdba/dmdata/test/dm.log 2>&1 &
   

解决：重启数据库实例后，重新执行收集命令成功。

5.5 收集后重新查看统计信息

命令：

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM DBA_TABLES
WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE';

输出：

行号  TABLE_NAME  NUM_ROWS  BLOCKS  LAST_ANALYZED
----- ---------- --------- ------- --------------
1     EMPLOYEE    100004    640     2026-07-29

LAST_ANALYZED 已更新为当前时间，NUM_ROWS=100004，统计信息收集成功。

5.6 创建统计信息存储表

命令：

CALL DBMS_STATS.CREATE_STAT_TABLE('TEST', 'STAT_TABLE2');

输出：

DMSQL 过程已成功完成

注意：使用 CREATE_STAT_TABLE 创建的表，实际表名会带 $_ 前缀，即 STAT$_STAT_TABLE2。查询 USER_TABLES 可确认实际表名。

5.7 导出统计信息到存储表

命令：

CALL DBMS_STATS.EXPORT_TABLE_STATS('TEST', 'EMPLOYEE', NULL, 'STAT_TABLE2', 'STATID1', TRUE, 'TEST');

输出：

DMSQL 过程已成功完成

遇到的问题及解决

问题：初次执行 EXPORT_TABLE_STATS 报错。

错误信息：

[-7019]:无效的表名

原因：CREATE_STAT_TABLE 创建的表实际名称为 STAT$_STAT_TABLE2，但 EXPORT_TABLE_STATS 函数需要传入的是创建时指定的表名 STAT_TABLE2（不带前缀），函数内部会自动处理。第一次尝试时传入的是带前缀的表名，导致报错。

解决方法：

• 使用创建时指定的表名 STAT_TABLE2 导出
• 必须指定 statid 参数（非空约束）

修正后的命令：

CALL DBMS_STATS.EXPORT_TABLE_STATS('TEST', 'EMPLOYEE', NULL, 'STAT_TABLE2', 'STATID1', TRUE, 'TEST');

5.8 查看导出的统计信息内容

命令：

SELECT * FROM TEST.STAT$_STAT_TABLE2 WHERE ROWNUM <= 5;

输出（节选）：

STATID  OWNNAME  TABNAME  NAME      T_FLAG  T_TOTAL  N_SAMPLE  N_DISTINCT  N_NULL
------- -------- -------- --------- ------- -------- --------- ----------- -------
STATID1 TEST     EMPLOYEE EMPLOYEE  T       100004   0         0           0
STATID1 TEST     EMPLOYEE ID        C       100004   49381     49381       0
STATID1 TEST     EMPLOYEE NAME      C       100004   49381     49381       0
STATID1 TEST     EMPLOYEE AGE       C       100004   49381     30          0
STATID1 TEST     EMPLOYEE DEPT      C       100004   49381     2           0

各列含义：

• STATID：统计信息标识（导出时指定的 STATID1）
• OWNNAME：模式名
• TABNAME：表名
• NAME：列名
• T_FLAG：类型标识（T 表示表级，C 表示列级）
• T_TOTAL：总行数
• N_SAMPLE：采样行数
• N_DISTINCT：不同值数量
• N_NULL：空值数量

5.9 导入统计信息

命令：

CALL DBMS_STATS.IMPORT_TABLE_STATS('TEST', 'EMPLOYEE', NULL, 'STAT_TABLE2', 'STATID1', TRUE, 'TEST');

输出：

DMSQL 过程已成功完成

遇到的问题及解决

问题：导入时报错 无效的表名。

错误信息：

[-7019]:无效的表名

原因：IMPORT_TABLE_STATS 期望传入的是创建时指定的表名（不带 $_ 前缀），而不是实际表名。

解决方法：

CALL DBMS_STATS.IMPORT_TABLE_STATS('TEST', 'EMPLOYEE', NULL, 'STAT_TABLE2', 'STATID1', TRUE, 'TEST');

5.10 清理统计信息存储表

命令：

CALL DBMS_STATS.DROP_STAT_TABLE('TEST', 'STAT_TABLE2');

输出：

DMSQL 过程已成功完成

验证清理结果：

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '%STAT%';

输出为空，说明已清理完成。

六、统计信息管理总结

操作	方法	关键要点
查看表级统计信息	DBA_TABLES	关注 NUM_ROWS、LAST_ANALYZED
查看列级统计信息	DBA_TAB_COL_STATISTICS	关注 NUM_DISTINCT、NUM_NULLS、HISTOGRAM
收集统计信息	DBMS_STATS.GATHER_TABLE_STATS	需确保无活动事务阻塞
导出统计信息	DBMS_STATS.EXPORT_TABLE_STATS	必须指定 statid，传入创建时的表名（不带前缀）
导入统计信息	DBMS_STATS.IMPORT_TABLE_STATS	使用相同的 statid 和表名

七、注意事项

1. 统计信息收集卡住：通常由未提交的事务或锁阻塞引起，需先清理活动事务。
2. statid 参数：导出时 statid 必须指定，否则报错“违反列[STATID]非空约束”。
3. 表名带前缀：CREATE_STAT_TABLE 创建的表实际名称会带 $_ 前缀，但 EXPORT/IMPORT/DROP_STAT_TABLE 函数操作时应使用不带前缀的表名。
4. 数据库状态：SUSPEND 状态下事务无法提交，需先 ALTER DATABASE OPEN。

八、完整命令清单

-- 1. 查看表级统计信息
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM DBA_TABLES WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE';

-- 2. 查看列级统计信息
SELECT COLUMN_NAME, NUM_DISTINCT, NULLABLE, NUM_NULLS, LAST_ANALYZED
FROM DBA_TAB_COL_STATISTICS WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE'
ORDER BY COLUMN_NAME;

-- 3. 查看直方图信息
SELECT COLUMN_NAME, HISTOGRAM, NUM_BUCKETS
FROM DBA_TAB_COL_STATISTICS WHERE OWNER='TEST' AND TABLE_NAME='EMPLOYEE';

-- 4. 收集统计信息
CALL DBMS_STATS.GATHER_TABLE_STATS('TEST', 'EMPLOYEE');

-- 5. 创建统计信息存储表
CALL DBMS_STATS.CREATE_STAT_TABLE('TEST', 'STAT_TABLE2');

-- 6. 导出统计信息
CALL DBMS_STATS.EXPORT_TABLE_STATS('TEST', 'EMPLOYEE', NULL, 'STAT_TABLE2', 'STATID1', TRUE, 'TEST');

-- 7. 查看导出的内容
SELECT * FROM TEST.STAT$_STAT_TABLE2 WHERE ROWNUM <= 5;

-- 8. 导入统计信息
CALL DBMS_STATS.IMPORT_TABLE_STATS('TEST', 'EMPLOYEE', NULL, 'STAT_TABLE2', 'STATID1', TRUE, 'TEST');

-- 9. 清理统计信息存储表
CALL DBMS_STATS.DROP_STAT_TABLE('TEST', 'STAT_TABLE2');