A-A+

oracle 11G impdp 导入过慢

2016年07月07日 Oracle 暂无评论 阅读 1,252 次

昨天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 Pump EXCLUDE=STATISTICS parameter on either the export or import operation. You can then use the DBMS_STATS PL/SQL package to regenerate the statistics on the target database after the import has completed.
inewup_oracle_dbms_stats
标签:

给我留言

Copyright © 2011-2016 零下二十四度Theme By  Ality  京ICP备16007547号   关于本站

用户登录