CODEWITHSUNDEEP
performanceoracle11gexportdatapump
Hedi Fourati
Hedi Fourati

Reputation: 31

Oracle Datapump Export is very slow

My Oracle 11.2.0.3 FULL DATABASE Datapump Export is very slow, when i ask V$SESSION_LONGOPS

SELECT USERNAME,OPNAME,TARGET_DESC,SOFAR,TOTALWORK,MESSAGE,SYSDATE,ROUND(100*SOFAR/TOTALWORK,2)||'%' COMPLETED FROM V$SESSION_LONGOPS where SOFAR/TOTALWORK!=1

it show me 2 records, in opname one containing the SYS_EXPORT_FULL_XX, and another "Rowid Range Scan" and the message for the last one is

Rowid Range Scan : MY_SCHEMA.BIG_TABLE: 28118329 out of 30250532 Blocks done and it takes hours and hours. I.E : MY_SCHEMA.BIG_TABLE is a 220 GB table size having 2 CLOB colunn.

Upvotes: 2

Views: 8028

Answers (2)

DbTuner
DbTuner

Reputation: 1

Well for 11g and 12cR1 the Streams AQ Enqueue is a common culprit for this as well. If you ALTER SYSTEM SET EVENTS 'IMMEDIATE TRACE NAME MMAN_CREATE_DEF_REQUEST LEVEL 6' this will help if the issue is the very common Streams AQ Enqueue.

Upvotes: 0

Daniel Overby Hansen
Daniel Overby Hansen

Reputation: 36

If you have CLOBs in the table it will take a long time to export because that wont parallelize. Exactly what phase are you stuck in? Could you paste the last lines from the log file or get a status from data pump?

There are some best practices that you could try out:

  • SecureFile LOBs can be faster than BasicFile LOBs. That is yet another reason for going to SecureFile LOBs.

  • You could try to increase the STREAMS_POOL_SIZE to 256 MB (at least) although I think that is not the reason.

  • Use PARALLEL option and set it to 2 x CPU cores. Never export statistics - it is better to either export using DBMS_STATS or regather at target database.

Regards, Daniel

Upvotes: 1

Related Questions