达梦数据库导入导出统计信息
-
一、源端库
在金融行业XC过程中，经常会遇到数据迁移的情况，特别是将国外厂商数据库迁移到国产数据库上。在数据迁移过程中，数据统计信息是否准确是非常重要的，如果统计信息不准确，会直接影响到业务系统的运行性能。同时，由于金融业务系统的敏感性，数据迁移时间窗口非常有限，在完成数据迁移的同时，又要保障数据统计信息的准确，对于数据量较大的OLAP业务系统来说，压力和挑战是极大的。部分OLAP业务系统中，单表数据行数接近20亿级，单表数据容量达到600GB，数据库容量超过5TB，在极短的数据迁移时间窗口下，同时完成数据迁移和数据统计信息收集，是不可能完成的工作。针对这种情况，可以提前三天或一周的左右的时间收集数据统计信息，在数据迁移过程中，通过导出导入统计信息快速实现数据统计信息更新。本文基于以上思考，提供以下参考方法，欢迎交流和分享。
1.1基础数据
用户和模式名：BTIM
表名：BOOKS，表数据行数14680064。
表结构信息：
CREATE
TABLE BOOKS
(
BOOK_ID VARCHAR2(4) ,
BOOK_NAME VARCHAR2(50),
PRICE VARCHAR2(5) ,
QTY VARCHAR2(4) ,
PUB VARCHAR2(50)
);
1.2重建系统包
SP_CREATE_SYSTEM_PACKAGES(1);
[执行语句1]:
SP_CREATE_SYSTEM_PACKAGES(1);
执行成功, 执行耗时2秒 650毫秒. 执行号:609
影响了1条记录
1.3检查表统计信息
select count(*) from "SYSDBA"."BOOKS";
COUNT(*)
14680064
SELECT
OWNER ,
TABLE_NAME,
IOT_NAME ,
STATUS ,
NUM_ROWS
FROM
DBA_TABLES
WHERE
OWNER ='SYSDBA'
AND TABLE_NAME='BOOKS';
OWNER TABLE_NAME IOT_NAME STATUS NUM_ROWS
BTIM BOOKS BOOKS VALID 14680064
DBMS_STATS.TABLE_STATS_SHOW('BTIM','BOOKS');
NUM_ROWS LEAF_BLOCKS LEAF_USED_BLOCKS
14680064 28288 28287
NUM_ROWS：表的总行数
LEAF_BLOCKS：总的页数
LEAF_USED_BLOCKS：已经使用的页数
1.4创建临时统计信息表
根据用户指定的名称创建一个增加了前缀的临时统计信息表，用于保存待导出的统计信
息。统计信息保存到该表中后，可以使用DM的数据导入导出工具进行跨实例导入导出。
DBMS_STATS.CREATE_STAT_TABLE('BTIM','TAB_BOOKS');
[执行语句1]:
DBMS_STATS.CREATE_STAT_TABLE('BTIM','TAB_BOOKS');
执行成功, 执行耗时134毫秒. 执行号:612
影响了1条记录
删除统计信息表
DBMS_STATS.DROP_STAT_TABLE ('BTIM','TAB_BOOKS');
1.5查看临时统计信息表信息
SELECT COUNT (*) FROM STAT$_TAB_BOOKS;
COUNT(*)
0
可以看到统计信息表为空。
1.6把目标表的统计信息导出到指定统计信息表中
执行下面备份语句
BEGIN FOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER='BTIM')
LOOP
DBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,STATTAB=>'TAB_BOOKS',STATID => 'T1_2024092601');
END LOOP;
END;
[执行语句1]:
BEGIN FOR RS IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER='BTIM')
LOOP
DBMS_STATS.EXPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME,STATTAB=>'TAB_BOOKS',STATID => 'T1_2024092601');
END LOOP;
END;
执行成功, 执行耗时22毫秒. 执行号:633
影响了0条记录
1.7查询临时统计信息表信息数量
SELECT COUNT (*) FROM STAT$_TAB_BOOKS;
COUNT(*)
6
可以看到有6行数据，
1.8导出统计信息表
./dexp userid=BTIM/cssWEB123 FILE=BOOKS0926.dmp DIRECTORY=/home/dmdba/dmbackup LOG=BOOKS0926.log TABLES=BTIM.'STAT$_TAB_BOOKS'
dmdba@dm1:/home/dmdba/dmdbms/bin$ ./dexp userid=BTIM/cssWEB123 FILE=BOOKS0926.dmp DIRECTORY=/home/dmdba/dmbackup LOG=BOOKS0926.log TABLES=BTIM.'STAT$_TAB_BOOKS'
dexp V8
version: 03134284194-20240621-232765-20108
start dexp:
BTIM/******@LOCALHOST:5236 FILE=BOOKS0926.dmp DIRECTORY=/home/dmdba/dmbackup LOG=BOOKS0926.log TABLES=BTIM.STAT$_TAB_BOOKS
---- [2024-09-28 21:06:51]export table:BTIM.STAT$_TAB_BOOKS -----
export the privilege of the table...
table :BTIM.STAT$_TAB_BOOKS export terminate, total export 6 rows, size 1.420 KB
----- export total 1 TABLE -----
all the export process spent total 0.153 s
terminate export success without warning
dmdba@dm1:/home/dmdba/dmdbms/bin$
二、目标库
完成BOOKS表统计信息还原，BOOKS表的数据迁移可以使用DTS、DEXP/DIMP、DMHS工具等，此处不对数据迁移进行详细描述。
2.1 创建临时统计信息表
DBMS_STATS.CREATE_STAT_TABLE('BTIM','TAB_BOOKS');
[执行语句1]:
DBMS_STATS.CREATE_STAT_TABLE('BTIM','TAB_BOOKS');
执行成功, 执行耗时15毫秒. 执行号:873
影响了1条记录
执行完成后，会自动生成STAT$_TAB_BOOKS表。
2.2 查询临时统计信息表信息
SELECT COUNT (*) FROM STAT$_TAB_BOOKS WHERE STATID='T1_2024092601';
COUNT(*)
0
将导出的dmp包上传到新数据库服务器上，执行下面命令将dmp数据导入到统计信息表中。
2.3 导入统计信息表
dmdba@dm1:/home/dmdba/dmdbms/bin$ ./dimp userid=BTIM/cssWEB123@192.168.2.11:5237 file=BOOKS0926.dmp DIRECTORY=/home/dmdba/dmbackup TABLE_EXISTS_ACTION=REPLACE
dimp V8
version: 03134284194-20240621-232765-20108
start dimp:
BTIM/******@192.168.2.11:5237 file=BOOKS0926.dmp DIRECTORY=/home/dmdba/dmbackup TABLE_EXISTS_ACTION=REPLACE
local code: PG_UTF8, dump file code: PG_UTF8
----- [2024-09-28 21:41:42]import table:BTIM.STAT$_TAB_BOOKS -----
[0/1]create table BTIM.STAT$_TAB_BOOKS
[1/1]creating table is complate, table STAT$_TAB_BOOKS data is importing...
import table BTIM.STAT$_TAB_BOOKS , has coped with 6 rows, size 1.420 KB
[1/1]all the import process spent total 0.056 s
terminate import success without warning
dmdba@dm1:/home/dmdba/dmdbms/bin$
2.4 查询临时统计信息表信息
SELECT COUNT (*) FROM STAT$_TAB_BOOKS WHERE STATID='T1_2024092601';
COUNT(*)
6
为什么是6，而不是5或7，或者其他值。通过STAT$_TAB_BOOKS表的详细信息就会明白。
SELECT * FROM STAT$_TAB_BOOKS;
NAME和T_FLAG字段，STAT$_TAB_BOOKS中存放的是BOOKS表和5个字段的统计信息。
2.5 还原统计信息
DECLARE
BEGIN
FOR RS IN
(
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER='BTIM' ORDER BY 2
)
LOOP
DBMS_STATS.IMPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME, STATTAB=>'TAB_BOOKS', STATID => 'T1_2024092601');
PRINT ('TABLE_NAME:' || RS.TABLE_NAME);
PRINT ('CODE: ' || SQLCODE) ;
PRINT ('ERRM: ' || SQLERRM) ;
NULL;
END LOOP;
END;
[执行语句1]:
DECLARE
BEGIN
FOR RS IN
(
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER='BTIM' ORDER BY 2
)
LOOP
DBMS_STATS.IMPORT_TABLE_STATS(RS.OWNER,RS.TABLE_NAME, STATTAB=>'TAB_BOOKS', STATID => 'T1_2024092601');
PRINT ('TABLE_NAME:' || RS.TABLE_NAME);
PRINT ('CODE: ' || SQLCODE) ;
PRINT ('ERRM: ' || SQLERRM) ;
NULL;
END LOOP;
END;
执行成功, 执行耗时52毫秒. 执行号:4614
TABLE_NAME:BOOKS
CODE: 0
ERRM: [0]:执行成功
TABLE_NAME:STAT$_TAB_BOOKS
CODE: 0
ERRM: [0]:执行成功
2.6 验证表的统计信息
select count(*) from "SYSDBA"."BOOKS";
COUNT(*)
14680064
SELECT
OWNER ,
TABLE_NAME,
IOT_NAME ,
STATUS ,
NUM_ROWS
FROM
DBA_TABLES
WHERE
OWNER ='SYSDBA'
AND TABLE_NAME='BOOKS';
OWNER TABLE_NAME IOT_NAME STATUS NUM_ROWS
BTIM BOOKS BOOKS VALID 14680064
DBMS_STATS.TABLE_STATS_SHOW('BTIM','BOOKS');
NUM_ROWS LEAF_BLOCKS LEAF_USED_BLOCKS
14680064 28288 28287
通过BOOKS表的count(*)，dba_tables表的NUM_ROWS值，和TABLE_STATS_SHOW值，三个值是一致的，确定BOOKS表的统计信息是最新的，也是准确的。