测试Oracle统计信息的导出导入

1、测试Oracle统计信息的导出导入模拟环境：11.2.0.4->11.2.0.4 和 11.2.0.4->12.1.0.1参考 ：一、背景有时我们会希望可以对Oracle的统计信息整体进行导出导入。比如在数据库迁移前后，希望统计信息保持不变;又比如想对统计信息重新进行收集，但是担心重新收集的结果反而引发性能问题，想先保存当前的统计信息，这样即使重新收集后效果不好还可以导入之前的统计信息。Oracle提供给我们一些方法，比较常用的粒度有两种：schema级别统计信息的导出导入通过调用DBMS_STATS.EXPORT_SCHEMA_STATS和DBMS_STATS.IMPORT_SC

2、HEMA_STATS来进行。database级别统计信息的导出导入通过调用DBMS_STATS.EXPORT_DATABASE_STATS和DBMS_STATS.IMPORT_DATABASE_STATS来进行。统计信息存放的表可以通过DBMS_STATS.CREATE_STAT_TABLE和DBMS_STATS.DROP_STAT_TABLE来进行创建或是删除。1.示例schema级别统计信息的导出导入2.示例database级别统计信息的导出导入3.验证统计信息导出导入效果二、示例1.示例schema级别统计信息的导出导入2.示例database级别统计信息的导出导入3.验证统计信息导出导

3、入效果1.示例schema级别统计信息的导出导入比如我将JINGYU这个schema下所有的统计信息进行导出导入：-源端统计信息导出：begin DBMS_STATS.CREATE_STAT_TABLE('SYSTEM','JINGYU_STATS_20181217'); DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'JINGYU', STATTAB => 'JINGYU_STATS_20181217', STATOWN => 'SYSTEM');end;/

4、expdp '/ as sysdba' directory=xtts dumpfile=stats_jingyu.dmp logfile=stats_jingyu.log tables=system.jingyu_stats_20181217-目标端统计信息导入：impdp '/ as sysdba' directory=xtts dumpfile=stats_jingyu.dmp logfile=stats_jingyu.log cluster=nexec DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME => 'J

5、INGYU', STATTAB => 'JINGYU_STATS_20181217', STATOWN => 'SYSTEM');-删除存放统计信息的表（根据实际需要选择性执行）：exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','JINGYU_STATS_20181217');2.示例database级别统计信息的导出导入如果想将数据库所有统计信息进行导出导入，方法非常类似，使用对应的过程：-源端统计信息导出：begin DBMS_STATS.CREATE_STAT_TABL

6、E('SYSTEM','DB_STATS_20181217'); DBMS_STATS.EXPORT_DATABASE_STATS(STATTAB => 'DB_STATS_20181217', STATOWN => 'SYSTEM');end;/expdp '/ as sysdba' directory=xtts dumpfile=stats.dmp logfile=stats.log tables=system.db_stats_20181217-目标端统计信息导入：impdp '/ as

7、sysdba' directory=xtts dumpfile=stats.dmp logfile=stats.log cluster=nexec DBMS_STATS.IMPORT_DATABASE_STATS (STATTAB => 'DB_STATS_20181217', STATOWN => 'SYSTEM');-删除存放统计信息的表（根据实际需要选择性执行）：exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','DB_STATS_20181217');3.验证统计信息导

8、出导入效果以数据库级别统计信息的导出导入为例，验证下实际的效果： 目前数据库JINGYU用户下各表在统计信息记录数：SYSorcl> select owner, table_name, NUM_ROWS from dba_tables where owner = 'JINGYU'OWNER TABLE_NAME NUM_ROWS- - -JINGYU TEST 100708JINGYU ASH_TMP 226此时按照之前的步骤导出数据库的统计信息，步骤不再赘述。然后在某一张表插入数据，重新收集该表的统计信息：SYSorcl> insert into jingyu.a

9、sh_tmp select * from jingyu.ash_tmp;SYSorcl> commit;SYSorcl> exec dbms_stats.gather_table_stats('JINGYU','ASH_TMP');PL/SQL procedure successfully completed.再去查询统计信息记录的该表行数:SYSorcl> select owner, table_name, num_rows from dba_tables where owner='JINGYU'OWNER TABLE_NAM

10、E NUM_ROWS- - -JINGYU TEST 100708JINGYU ASH_TMP 452此时按照之前的步骤导入数据库的统计信息，步骤不再赘述。再去查询统计信息记录的该表行数，已经恢复到当时的导出时刻：SYSorcl> select owner, table_name, num_rows from dba_tables where owner='JINGYU'OWNER TABLE_NAME NUM_ROWS- - -JINGYU TEST 100708JINGYU ASH_TMP 226SYSorcl> 另外，需要注意如果统计信息导入的目标环境，数据库

11、版本比源环境高（多发生在数据库升级场景），导入统计信息时会遇到下面这样的错误：ERROR at line 1:ORA-20002: Version of statistics table SYSTEM.DB_STATS_20181217 is too old.Please try upgrading it with dbms_stats.upgrade_stat_tableORA-06512: at "SYS.DBMS_STATS", line 11648ORA-06512: at "SYS.DBMS_STATS", line 11665ORA-0651

12、2: at "SYS.DBMS_STATS", line 12800ORA-06512: at line 1这时只需要按照提示执行下 dbms_stats.upgrade_stat_table：exec dbms_stats.upgrade_stat_table('SYSTEM','db_stats_20181217');再尝试导入统计信息就可以成功了。三、个人实验1.示例schema级别统计信息的导出导入-将scott这个schema下所有的统计信息进行导出导入：source profile_11gsqlplus / as sysdbase

13、lect username,account_status from dba_users where username='SCOTT'alter user scott identified by tiger account unlock;select directory_name,directory_path from dba_directories;show parameter name-生成scott用户统计信息exec dbms_stats.gather_schema_stats('SCOTT',estimate_percent=>20,no_inva

14、lidate=>DBMS_STATS.AUTO_INVALIDATE,method_opt=>'for all columns size auto',force=>TRUE,cascade=>TRUE,degree=>4);select * from system.SCOTT_STATS_20201128;-生成收集整个数据库的统计信息exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>

15、'ALL');源端：DATA_PUMP_DIR:/u01/app/oracle/admin/DB11G/dpdump/目标端：DATA_PUMP_DIR：/u01/app/oracle/admin/DB11G/dpdump/export DISPLAY=192.168.235.1:0.0source profile_11gdbca建库-源端统计信息导出：begin DBMS_STATS.CREATE_STAT_TABLE('SYSTEM','SCOTT_STATS_20201128'); DBMS_STATS.EXPORT_SCHEMA_STAT

16、S(OWNNAME => 'SCOTT', STATTAB => 'SCOTT_STATS_20201128', STATOWN => 'SYSTEM');end;/select * from system.SCOTT_STATS_20201128;source profile_11gexport ORACLE_SID=DB11Gexpdp '/ as sysdba' directory=DATA_PUMP_DIR dumpfile=stats_scott.dmp logfile=stats_scott.log

17、tables=system.SCOTT_STATS_20201128-目标端统计信息导入：export ORACLE_SID=DB11Gimpdp '/ as sysdba' directory=DATA_PUMP_DIR dumpfile=stats_scott.dmp logfile=stats_scott.log cluster=nsqlplus / as sysdbaexec DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME => 'SCOTT', STATTAB => 'SCOTT_STATS_2020

18、1128', STATOWN => 'SYSTEM');select * from system.SCOTT_STATS_20201128;-删除存放统计信息的表（根据实际需要选择性执行）：可实验完毕后再视情况是否删除exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','SCOTT_STATS_20201128');2.示例database级别统计信息的导出导入begin DBMS_STATS.CREATE_STAT_TABLE('SYSTEM','DB_STATS_20201

19、128'); DBMS_STATS.EXPORT_DATABASE_STATS(STATTAB => 'DB_STATS_20201128', STATOWN => 'SYSTEM');end;/select * from system.DB_STATS_20201128;source profile_11gexport ORACLE_SID=DB11Gexpdp '/ as sysdba' directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log tables=

20、system.db_stats_20201128-目标端统计信息导入：export ORACLE_SID=DB11Gimpdp '/ as sysdba' directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log cluster=nsqlplus / as sysdbaexec DBMS_STATS.IMPORT_DATABASE_STATS (STATTAB => 'DB_STATS_20201128', STATOWN => 'SYSTEM');select * fr

21、om system.DB_STATS_20201128;-删除存放统计信息的表（根据实际需要选择性执行）：exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','DB_STATS_20201128');3.验证统计信息导出导入效果个人实验：以数据库级别统计信息的导出导入为例，验证下实际的效果： 目前数据库SCOTT用户下各表在统计信息记录数：SYSDB11G> select owner, table_name, NUM_ROWS from dba_tables where owner = 'SCOTT'OWNE

22、R TABLE_NAMENUM_ROWS- - -SCOTT DEPT 4SCOTT EMP 14SCOTT BONUS 0SCOTT SALGRADE 5此时按照之前的步骤导出数据库的统计信息，步骤不再赘述。然后在某一张表插入数据，重新收集该表的统计信息：-查询约束:select CONSTRAINT_NAME from dba_constraints where table_name='EMP'select CONSTRAINT_NAME,COLUMN_NAME from dba_cons_columns where table_name='EMP'-删除约

23、束:alter table scott.emp drop constraint pk_emp;insert into scott.emp select * from scott.emp;commit;exec dbms_stats.gather_table_stats('SCOTT','EMP');PL/SQL procedure successfully completed.再去查询统计信息记录的该表行数:SYSDB11G> select owner, table_name, num_rows from dba_tables where owner=&#

24、39;SCOTT'OWNER TABLE_NAME NUM_ROWS- - -SCOTT DEPT 4SCOTT EMP 28SCOTT BONUS 0SCOTT SALGRADE 5此时按照之前的步骤导入数据库的统计信息，步骤不再赘述。再去查询统计信息记录的该表行数，已经恢复到当时的导出时刻：SYSDB11G> select owner, table_name, num_rows from dba_tables where owner='JINGYU'OWNER TABLE_NAME NUM_ROWS- - -SCOTT DEPT 4SCOTT EMP 14SCOTT BONUS 0SCOTT SALGRADE 5SYSDB11G> -目标端建库source profile_122c export DISPLAY=192.168.235.1:0.0dbca建库sqlplus / as sysdba=>DATA_PUMP_DIR：/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/log