exp/imp与expdp/impdp对比及使用中的一些优化事项(四)

2.3 Expdp/Impdp

据泵与exp/imp 来说性能有很大的提高，其中影响最大的就是paralle。 可以这么来看: expdp/impdp=exp/imp+direct mode + paralle。所以，使用数据泵，要想提高速度，就要设置并行参数。

先看2个参数：

Setting Parallelism

For export and import operations, the parallelism setting (specified with the PARALLEL parameter) should be less than or equal to the number of dump files in the dump file set. If there are not enough dump files, the performance will not be optimal because multiple threads of execution will be trying to access the same dump file.

The PARALLEL parameter is valid only in the Enterprise Edition of Oracle Database 10g.

Using Substitution Variables

Instead of, or in addition to, listing specific filenames, you can use the DUMPFILE parameter during export operations to specify multiple dump files, by using a substitution variable (%U) in the filename. This is called a dump file template. The new dump files are created as they are needed, beginning with 01 for %U, then using 02, 03, and so on. Enough dump files are created to allow all processes specified by the current setting of the PARALLEL parameter to be active. If one of the dump files becomes full because its size has reached the maximum size specified by the FILESIZE parameter, it is closed, and a new dump file (with a new generated name) is created to take its place.

如果我们使用如下语句：

expdp full=y directory=dump dumpfile=orcl_%U.dmp parallel=4

导出的dump文件和paralle有关系，那么导入也有关系。 paralle要小于dump文件数。 如果paralle 大于dump文件的个数，就会因为超过的那个进程获取不到文件，就不能对性能提高。

查看CPU 个数：

SQL> show parameter cpu

注意事项：

（1）导入的时候可能会停在某个地方，比如在创建索引的时候，可能在一个地方停了十几分钟。这个时候切记不要中断过程。 这个时候可能是需要导入的数据比较多。

可以在不同时段观察下表空间大小的变化。 如果表空间一直在变化，说明还在导入，这个时候耐心等待就好。

查看表空间可以用如下SQL:

/* Formatted on 2010/12/23 13:14:13 (QP5 v5.115.810.9015) */

SELECT a.tablespace_name,

ROUND (a.total_size) "total_size(MB)",

ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",

ROUND (b.free_size, 3) "free_size(MB)",

ROUND (b.free_size / total_size * 100, 2) || '%' free_rate

FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size

FROM dba_data_files

GROUP BY tablespace_name) a,

( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name(+);

（2）导出导入的过程，尽量避免用ssh连上服务器，在客户端的ssh里执行备份恢复命令。 因为这样，如果连接中断，备份也就中断了。 可以将备份脚本添加到crontab 里。 让备份在服务器上执行。 这样即使ssh中断，备份和恢复也不受影响。