一、前言

在日常数据运维过程中，生产环境SQL性能问题频发，很多SQL经历过测试环境预发布无异常，但在生产环境中数据库统计信息的不同，仍可能存在有性能隐患。
故可以尝试维护一个这样的环境，空库，但是拥有生产的统计信息，进而模拟生产的执行计划。
实现方法是基于DBMS_STATS包，以下是需要用到的方法。

1、CREATE_STAT_TABLE

（1）说明

根据用户指定的名称创建一个增加了前缀的临时统计信息表，用于保存待导出的统计信息。统计信息保存到该表中后，可以使用 DM 的数据导入导出工具进行跨实例导入导出。MPP 环境下暂不支持统计信息导入导出功能。
创建的统计信息表需要通过包方法 DROP_STAT_TABLE 进行删除，不支持使用 DROP TABLE 语句删除。

（2）语法

PROCEDURE CREATE_STAT_TABLE (
STATOWN VARCHAR(128),
STATTAB VARCHAR(128),
TABLESPACE VARCHAR(128) DEFAULT NULL,
GLOBAL_TEMPORARY BOOLEAN DEFAULT FALSE
);

（3）参数详解

STATOWN
统计信息表的模式名，区分大小写。
STATTAB
统计信息表名，区分大小写，系统实际创建的表会增加前缀。
TABLESPACE
表空间名，默认为 NULL，区分大小写。
GLOBAL_TEMPORARY
是否创建为会话级的全局临时表。

2、EXPORT_TABLE_STATS

（1）说明

把目标表的统计信息导出到指定统计信息表中。

（2）语法

PROCEDURE EXPORT_TABLE_STATS(
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR(128),
STATID VARCHAR(128) DEFAULT '',
CASCADE BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR(128) DEFAULT NULL,
STAT_CATEGORY VARCHAR(128) DEFAULT NULL
);

（3）参数详解

OWNNAME
目标表的模式名，区分大小写。
TABNAME
目标表名，区分大小写。
PARTNAME
目标表分区名，如果不指定分区，则一起导出所有子表的统计信息，缺省为 NULL。
STATTAB
统计信息表名，区分大小写。
STATID
由用户指定的统计信息标识名，缺省认为空字符串。
CASCADE
是否连列和索引的统计信息一起导出，缺省为 TRUE
STATOWN
统计信息表的模式名，区分大小写，缺省为 NULL。
STAT_CATEGORY
保留参数，无实际意义。

3、IMPORT_TABLE_STATS

（1）说明

把统计信息表中的统计信息导入到目标表中。

（2）语法

PROCEDURE IMPORT_TABLE_STATS(
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR(128),
STATID VARCHAR(128) DEFAULT '',
CASCADE BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR(128) DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT NULL,
FORCE BOOLEAN DEFAULT FALSE,
STAT_CATEGORY VARCHAR(128) DEFAULT NULL
);

（3）参数详解

OWNNAME
目标表的模式名，区分大小写。
TABNAME
目标表名，区分大小写。
PARTNAME
目标表分区名，如果不指定分区，则一起导入所有子表的统计信息，缺省为 NULL。
STATTAB
统计信息表名，区分大小写。
STATID
由用户指定的统计信息标识名，缺省为空字符串。
CASCADE
是否连列和索引的统计信息一起导出，缺省为 TRUE
STATOWN
统计信息表的模式名，区分大小写，缺省为 NULL。
NO_INVALIDATE
是否让依赖游标失效，缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
FORCE
保留参数，无实际意义。
STAT_CATEGORY
保留参数，无实际意义。

二、准备数据

1、数据库安装及初始化

略

2、模拟测试数据

CREATE TABLE T1(C1 INT, C2 INT);
INSERT INTO T1 SELECT LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
INSERT INTO T1 SELECT 1000,1000 FROM DUAL CONNECT BY LEVEL <= 10000;
CREATE INDEX I1 ON T1(C1);
COMMIT;

3、查看数据

SELECT C1, COUNT(*) FROM T1 GROUP BY C1 ORDER BY C1;

4、更新统计信息

CALL DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T1',NULL,100);

5、查看统计信息

CALL DBMS_STATS.COLUMN_STATS_SHOW('SYSDBA','T1','C1');

6、测试SQL执行计划

（1）过滤性好的条件

EXPLAIN SELECT C2 FROM T1 WHERE C1 = 1;

（2）过滤性差的条件

EXPLAIN SELECT C2 FROM T1 WHERE C1 = 1000;

三、备份统计信息

1、创建临时统计信息表

DBMS_STATS.CREATE_STAT_TABLE(STATOWN=>'SYSDBA',STATTAB=>'SYSDBA_STATS')

2、导出统计信息至临时信息表

（1）单表导出

DBMS_STATS.EXPORT_TABLE_STATS('SYSDBA', 'T1', NULL, 'SYSDBA_STATS',A, TRUE, 'SYSDBA');

（2）多表导出

DECLARE
BEGIN
FOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('USER1','USER2'))
LOOP
DBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,NULL,'SYSDBA_STATS','A',TRUE,'SYSDBA');
END LOOP;
END;

（3）查看临时统计信息表

SELECT * FROM STAT$_SYSDBA_STATS;

3、导出临时统计信息表

dimp SYSDBA/SYSDBA:5236 DIRECTORY=/dmbak/ file=dexp_stats_20240722.dmp log=dimp_stats_20240722.log tables=sysdba.'STAT$_SYSDBA_STATS' TABLE_EXISTS_ACTION=REPLACE

四、目标库准备环境

1、目标库安装及初始化

略

2、新建同样的表结构

CREATE TABLE T1(C1 INT, C2 INT) STORAGE(WITHOUT COUNTER);
CREATE INDEX I1 ON T1(C1);
COMMIT;

3、创建临时统计信息表

DBMS_STATS.CREATE_STAT_TABLE(STATOWN=>'SYSDBA',STATTAB=>'SYSDBA_STATS')

五、导入统计信息并验证

1、导入统计信息临时表

dimp SYSDBA/SYSDBA:5237 DIRECTORY=/dmbak/ file=dexp_stats_20240722.dmp log=dimp_stats_20240722.log tables=sysdba.'STAT$_SYSDBA_STATS'

2、恢复统计信息数据至数据字典

（1）单表导入

CALL DBMS_STATS.IMPORT_TABLE_STATS('SYSDBA', 'T1', NULL, 'SYSDBA_STATS','A', TRUE, 'SYSDBA');

（2）多表导入

DECLARE
BEGIN
FOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER IN ('SYSDBA','USER2'))
LOOP
BEGIN
DBMS_STATS.IMPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,NULL,'SYSDBA_STATS','A',TRUE,'SYSDBA');
EXCEPTION
WHEN OTHERS THEN
PRINT ('OWNER:' || RS.OWNER);
PRINT ('TABLE_NAME:' || RS.TABLE_NAME);
PRINT ('CODE: ' || SQLCODE);
PRINT ('ERRM: ' || SQLERRM);
END;
END LOOP;
END;

3、查询统计信息

六、验证执行计划

1、直接查询

（1）过滤性好的条件

SELECT COUNT(*) FROM T1;
EXPLAIN SELECT C2 FROM T1 WHERE C1 = 1;

（2）过滤性差的条件

SELECT COUNT(*) FROM T1;
EXPLAIN SELECT C2 FROM T1 WHERE C1 = 1000;

（3）结论

发现，结果并未符合预期，执行计划中的行数估算为真实情况，并未只参照执行计划（表中无数据）

2、使用统计信息提示

手动指定统计信息内的行数

（1）过滤性好的条件

SELECT COUNT() FROM T1;
EXPLAIN SELECT /+ STAT(T1 10010)*/ C2 FROM T1 WHERE C1 = 1;

（2）过滤性差的条件

SELECT COUNT() FROM T1;
EXPLAIN SELECT /+ STAT(T1 10010)*/ C2 FROM T1 WHERE C1 = 1000;

（3）结论

同时使用统计信息提示，这时的执行计划是符合预期的，是基于统计信息字典表生成的执行计划。

七、结论

经过以上测试结果，使用空库模拟生产环境执行计划的方法，是完全可行的。
只是目前在空库模拟时，需要带上统计信息提示，才可完全的使用到生产的统计信息。有没有其他越过的方法，后续个人会进一步测试。