关于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