使用impdp导入lob字段数据hang问题处理

  近期在做数据迁移时，在使用数据泵impdp方式导入时，出现hang，导入将近2天未完成，查看数据库impdp会话event为direct path sync，且event对应的p1以及p2内容持续为发生变化，查询metalink查询到问题如下：将导入job通过交互模式stop_job=immediate，重启数据库启动job导入正常。
 
Import Slow For LOB Objects (文档 ID 950350.1

In this Document


Symptoms


Cause


Solution
APPLIES TO:


Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 28-NOV-2012***
SYMPTOMS


Import of a LOB object is extremely slow on a UNIX environment.
CAUSE


The AWR reports the wait event 'direct path sync' spending a lot of time doing a fsync system calls which is an OS issue. The performance problem is due to the filesystemio_options database parameter is not to SETALL which enables direct I/O as well as asynchronous I/O.
I/O operations in UNIX and Linux systems typically go through the file system cache. This extra processing does require resources. Thus reducing the I/O performance. The default value for filesystemio_options is none. And setting the value to SETALL enables both direct I/O and asynchronous I/O where possible.
SOLUTION


Setting the parameter for FILESYSTEMIO_OPTIONS to SETALL or DIRECTIO improves the import performance dramatically. Restarting the instance is needed as this parameter is static parameter.
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;