investigating expdp slowness
From: rjamya <rjamya_at_gmail.com>
Date: Wed, 26 Oct 2011 22:09:14 -0400
Message-ID: <CAGurbTN5Du+LM8ibbmzFtjuEr5SgV=oFP6haa3MQCotd0YtGCQ_at_mail.gmail.com>
ok here is the deal, 11gr2 RAC, running expdp to export few schemas, everything works fine but one single table takes long time, it has only 7m rows, few raw columns and a varchar2(4000). no large objects of any kind. This one takes long time, more than 15-20 minutes. So I traced the expdp session and found that in normal direct path mode, it only writes 64 blocks at a time. What gives? why is this limit? can this limit be tweaked? Also just for fun i expdp'd this table in external_table mode and it runs under 4 minutes.
Best regards
Raj
Date: Wed, 26 Oct 2011 22:09:14 -0400
Message-ID: <CAGurbTN5Du+LM8ibbmzFtjuEr5SgV=oFP6haa3MQCotd0YtGCQ_at_mail.gmail.com>
ok here is the deal, 11gr2 RAC, running expdp to export few schemas, everything works fine but one single table takes long time, it has only 7m rows, few raw columns and a varchar2(4000). no large objects of any kind. This one takes long time, more than 15-20 minutes. So I traced the expdp session and found that in normal direct path mode, it only writes 64 blocks at a time. What gives? why is this limit? can this limit be tweaked? Also just for fun i expdp'd this table in external_table mode and it runs under 4 minutes.
here is snipped from DW trace
KUPF:21:07:05.261: ...ObjLen = 19136512 KUPF:21:07:05.261: In kupfReqBuffer... KUPF:21:07:05.261: In kupfioReqBuffer... KUPF:21:07:05.261: ...returning buffer with length = 262144 KUPF:21:07:05.261: ...bdx->kupfbdxLpBlkBln = 0 KUPF:21:07:05.261: In kupfWriteBuffer... KUPF:21:07:05.261: In kupfioWriteBuffer... KUPF:21:07:05.261: ...nBytesToWrite = 262144 KUPF:21:07:05.261: ...nBytesToWrite+kupfbdxLpBlkBln = 262144 KUPF:21:07:05.261: ...nBlocksToWrite = 64 KUPF:21:07:05.261: ...nBlocksAvail = 4294962612 KUPF:21:07:05.261: ...nBlocksThisPass = 64 KUPF:21:07:05.261: ...nBytesThisPass = 262144 KUPF:21:07:05.261: ...nBlocksToSave = 0 KUPF:21:07:05.263: ...wrote 64 blocks to dump file 1 KUPF:21:07:05.263: ...nBytesThisPass = 262144 KUPF:21:07:05.263: ...nBytesThisPass-kupfbdxLpBlkBln = 262144 KUPF:21:07:05.263: ...ObjLen = 19398656 KUPF:21:07:05.263: In kupfReqBuffer... KUPF:21:07:05.263: In kupfioReqBuffer... KUPF:21:07:05.263: ...returning buffer with length = 262144 KUPF:21:07:05.263: ...bdx->kupfbdxLpBlkBln = 0 KUPF:21:07:05.264: In kupfWriteBuffer... KUPF:21:07:05.264: In kupfioWriteBuffer... KUPF:21:07:05.264: ...nBytesToWrite = 262144 KUPF:21:07:05.264: ...nBytesToWrite+kupfbdxLpBlkBln = 262144 KUPF:21:07:05.264: ...nBlocksToWrite = 64 KUPF:21:07:05.264: ...nBlocksAvail = 4294962548 KUPF:21:07:05.264: ...nBlocksThisPass = 64 KUPF:21:07:05.264: ...nBytesThisPass = 262144 KUPF:21:07:05.264: ...nBlocksToSave = 0 KUPF:21:07:05.266: ...wrote 64 blocks to dump file 1 KUPF:21:07:05.266: ...nBytesThisPass = 262144 KUPF:21:07:05.266: ...nBytesThisPass-kupfbdxLpBlkBln = 262144
Trying to understand why direct path takes over 20 minutes wtiring 64 blocks at a time and external_Table can dump same table in under 4.
Any ideas? many thanks in advance
Best regards
Raj
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 26 2011 - 21:09:14 CDT