A-A+
oracle 11G impdp 导入过慢
昨天expdp,impdp了几个schemas,expdp的时候速度还可以,等到impdp的时候慢的不要不要的了
expdp导出过程,估算导出83G
Total estimation using BLOCKS method: 83.03 GB
实际导出
[root@rac back]# du -sh db1_20160706.dmp
68G db1_20160706.dmp
导出用时 01:48:51
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/back/db1_20160706.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at Wed Jul 6 12:01:03 2016 elapsed 0 01:48:51
impdp 导入
用时8个多小时
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 2 error(s) at Wed Jul 6 22:42:09 2016 elapsed 0 08:22:01
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
这三项耗费了大量时间
有个最佳实践来着,就是使用sqlfile 不导入INDEX和STATISTICS,使用nologging的方式创建索引,在收集统计信息。
Data Pump Export dump files that are created with a release prior to 12.1, and that contain large amounts of statistics data, can cause an import operation to use large amounts of memory. To avoid running out of memory during the import operation, be sure to allocate enough memory before beginning the import. The exact amount of memory needed will depend upon how much data you are importing, the platform you are using, and other variables unique to your configuration.One way to avoid this problem altogether is to set the Data PumpEXCLUDE=STATISTICS
parameter on either the export or import operation. You can then use theDBMS_STATS
PL/SQL package to regenerate the statistics on the target database after the import has completed.
- Oracle Database SQL Tuning Guide for information about manual statistics collection using the
DBMS_STATS
PL/SQL package- Oracle Database PL/SQL Packages and Types Reference for more information about the
DBMS_STATS
PL/SQL package- The Data Pump Export EXCLUDE parameter
- The Data Pump Import EXCLUDE parameter