DataPump Export/Import Of LOBs Are Not Executed in Parallel

本文内容转自metalink

In this Document

Symptoms

Cause

Solution

References

---

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 09-DEC-2014***

Symptoms

You try to export/import a table with a LOB column using DataPump with PARALLEL > 1 and observed that the parallelism is not used.

Cause

DataPump does not export or import table data in parallel, if the table contains BasicFiles LOB columns. This was a restriction of BasicFiles LOBs. Starting with Oracle Database 11g, this restriction is removed when using the new SecureFiles LOBs.

Solution

Oracle recommends to migrate to the new LOB storage format of SecureFiles while working in 11gR1 onwards, for this and other performance reasons.

Please note, a SecureFile can only be created in an automatic segment space management (ASSM) tablespace.

For more information, please refer to:

Note 1490228.1 Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs)

While working in 10.2.x versions, or with BasicFiles LOBS, there are several workarounds which could be used to try to speed up the data transfer:

1. If the table is partitioned, then there would be DataPump Worker processes active (where is the value of the parallel parameter), each unloading data from one of the table partitions.

2. Include a where clause in the EXPDP command to segment the export and run multiple EXPDP to simulate parallelism:

The expdp command has a QUERY parameter which enables you to filter the data that is exported by specifying a clause for a SQL SELECT statement.
The query_clause is typically a WHERE clause for fine-grained row selection, but could be any SQL clause
This QUERY parameter could be used to run different exports from the same table at the same time, exporting a range of rows in each operation.

For example:

- First export could be run using QUERY parameter:

  QUERY=table1:'"WHERE primary_key_row > value1 AND primary_key_row  < value2"'

- Second export could be run using QUERY parameter:

  QUERY=table1:'"WHERE primary_key_row > value2+1 AND primary_key_row  < value3"'

- Third export could be run using QUERY parameter:

  QUERY=table1:'"WHERE primary_key_row > value3+1 AND primary_key_row  < value4"'

and so on.

Please note that DataPump can acquire exclusive locks and can get stuck if it cannot get it. For DataPump Import you may want to specify DATA_OPTIONS=DISABLE_APPEND_HINT, which can help with the locking issues on import.

Please refer to:

Note 462365.1 - DataPump Export (expdp) Or Import (impdp) Hangs On Lock

3. Excluding the use of DataPump, create table as select with parallel hint using a DBLINK to the target DB:

In the target DB, create a table as select using a dblink to the table in the source database. This query should include the parallel hint.

For example:

create table table2 (......) as select /*+ parallel (table1, 4) */ * from table1@dblink to source DB;

In this case you would need to keep in mind restrictions like the LONG RAW field, which raises error:

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

when queried through dblink.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21754115/viewspace-1732183/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21754115/viewspace-1732183/