关于Oracle统计信息的导出导入

由于收集统计信息可能很慢,尤其对于大库。所以在迁移升级数据库的时候,我们可以考虑在测试阶段,把接近生产数据的测试环境做好统计信息的收集。
然后在生产割接的时候直接导入原测试环境导出的统计信息即可。这样能保障数据库性能在上线速度最快,并且保障性能可控(和测试期间效果至少相当)

导出统计信息:include=statistics

[oracle@lncs ~]$ expdp jyc/jyc directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat.log include=statistics schemas=jyc Export: Release 11.2.0.4.0 - Production on Fri Jan 19 09:29:35 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "JYC"."SYS_EXPORT_SCHEMA_01": jyc/******** directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat.log include=statistics schemas=jyc Estimate in progress using BLOCKS method... Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is: /oracle/dmp/stat.dmp Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jan 19 09:30:15 2024 elapsed 0 00:00:37 [oracle@lncs ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 09:30:44 2024 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>'JYC'); PL/SQL procedure successfully completed. SQL> EXIT Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@lncs ~]$ sqlplus jyc/jyc SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 09:31:27 2024 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name,num_rows,last_analyzed from user_tables where num_rows>0; no rows selected SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@lncs ~]$ impdp jyc/jyc directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat-1.log include=statistics schemas=jyc Import: Release 11.2.0.4.0 - Production on Fri Jan 19 09:31:50 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "JYC"."SYS_IMPORT_SCHEMA_01": jyc/******** directory=ORADMP cluster=n dumpfile=stat.dmp logfile=stat-1.log include=statistics schemas=jyc Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "JYC"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Jan 19 09:31:54 2024 elapsed 0 00:00:03 [oracle@lncs ~]$ sqlplus jyc/jyc SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 19 09:32:12 2024 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name,num_rows,last_analyzed from user_tables where num_rows>0; TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ ---------- ------------------- T 10 2024-01-19 09:27:58 TAB1 1 2024-01-19 09:27:58 SQL> select index_name,table_name,num_rows,last_analyzed from user_indexes; INDEX_NAME TABLE_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- LAST_ANALYZED ------------------- PK_T T 10 2024-01-19 09:27:58 IDX T 10 2024-01-19 09:27:58 SQL> select table_name,num_rows,last_analyzed from user_tables; TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ ---------- ------------------- T 10 2024-01-19 09:27:58 TAB1 1 2024-01-19 09:27:58 SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'T',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); BEGIN dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'T',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 24281 ORA-06512: at "SYS.DBMS_STATS", line 24332 ORA-06512: at line 1 SQL> SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'TAB1',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); BEGIN dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'TAB1',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 24281 ORA-06512: at "SYS.DBMS_STATS", line 24332 ORA-06512: at line 1 SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname => 'JYC'); PL/SQL procedure successfully completed. SQL> select table_name,num_rows,last_analyzed from user_tables; TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ ---------- ------------------- T 10 2024-01-19 09:27:58 TAB1 1 2024-01-19 09:27:58 SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'TAB1',method_opt=> 'for all columns',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(ownname => 'JYC',tabname => 'T',estimate_percent => '100',degree=>'16',granularity=>'all',cascade=>TRUE); PL/SQL procedure successfully completed. SQL> select table_name,num_rows,last_analyzed from user_tables; TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ ---------- ------------------- T 10 2024-01-19 09:47:07 TAB1 1 2024-01-19 09:46:15 SQL> select index_name,table_name,num_rows,last_analyzed from user_indexes; INDEX_NAME TABLE_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- LAST_ANALYZED ------------------- PK_T T 10 2024-01-19 09:47:07 IDX T 10 2024-01-19 09:47:07