Performance of datapump export.

From: Fred Tilly <ftilly_at_btinternet.com>
Date: Mon, 11 Aug 2008 14:11:12 +0000 (GMT)
Message-ID: <12058.30719.qm@web86505.mail.ukl.yahoo.com>

All, We have a database that we upgraded from 9.2.0.5 to 10.2.0.2 ( Microsoft Windows 2003) and each night we perform an export (exp) which takes one hour. After reading the documentation around data pump I decided to try using it to reduce the time taken for the export. I have run the expdp several times and it takes 1hr 15minutes to perform the export. This is longer than using the old exp utility. The parameters for expdp are : schemas=FLINE directory=data_pump_dir dumpfile=expdp.dmp job_name=dump1 I have traced the process and the export is fast up until it hits the largest table we have which is 5GB and this takes 55 minutes and has the structure : ID NOT NULL NUMBER(9) NAME VARCHAR2(255) CREATED DATE DOCUMENT BLOB() The actual trace file is full of the following statements when reading this table : WAIT #2: nam='Datapump dump file I/O' ela= 1 count=0 intr=32 timeout=2147483647 obj#=47296 tim=5124529558 WAIT #14: nam='direct path read' ela= 30087 file number=25 first dba=14727 block cnt=1 obj#=47296 tim=5124559787 WAIT #14: nam='direct path read' ela= 95409 file number=25 first dba=15374 block cnt=1 obj#=47296 tim=5124655334 WAIT #14: nam='direct path read' ela= 16008 file number=23 first dba=16544 block cnt=1 obj#=47296 tim=5124671551 WAIT #14: nam='direct path read' ela= 2 file number=23 first dba=16560 block cnt=1 obj#=47296 tim=5124671627 WAIT #14: nam='direct path read' ela= 3771 file number=23 first dba=16576 block cnt=1 obj#=47296 tim=5124675448 WAIT #14: nam='direct path read' ela= 3510 file number=23 first dba=16592 block cnt=1 obj#=47296 tim=5124679023 WAIT #14: nam='direct path read' ela= 2 file number=23 first dba=16608 block cnt=1 obj#=47296 tim=5124679079 WAIT #14: nam='direct path read' ela= 7144 file number=23 first dba=15797 block cnt=1 obj#=47296 tim=5124686272 WAIT #14: nam='direct path read' ela= 2 file number=23 first dba=15813 block cnt=1 obj#=47296 tim=5124686337 WAIT #14: nam='direct path read' ela= 4001 file number=23 first dba=15829 block cnt=1 obj#=47296 tim=5124690387 WAIT #14: nam='direct path read' ela= 3496 file number=23 first dba=15845 block cnt=1 obj#=47296 tim=5124693972 WAIT #14: nam='direct path read' ela= 1 file number=23 first dba=15861 block cnt=1 obj#=47296 tim=5124694049 WAIT #14: nam='direct path read' ela= 3112 file number=23 first dba=15924 block cnt=1 obj#=47296 tim=5124697220 WAIT #14: nam='direct path read' ela= 2784 file number=23 first dba=15940 block cnt=1 obj#=47296 tim=5124700044 WAIT #14: nam='direct path read' ela= 1047 file number=23 first dba=15956 block cnt=1 obj#=47296 tim=5124701123 WAIT #14: nam='direct path read' ela= 1895 file number=23 first dba=15972 block cnt=1 obj#=47296 tim=5124703050 WAIT #14: nam='direct path read' ela= 12228 file number=17 first dba=75254 block cnt=1 obj#=47296 tim=5124715526 WAIT #14: nam='direct path read' ela= 1 file number=17 first dba=75270 block cnt=1 obj#=47296 tim=5124715595 WAIT #14: nam='direct path read' ela= 5045 file number=17 first dba=75031 block cnt=1 obj#=47296 tim=5124720691 WAIT #14: nam='direct path read' ela= 2080 file number=17 first dba=75047 block cnt=1 obj#=47296 tim=5124722826 WAIT #14: nam='direct path read' ela= 5620 file number=17 first dba=75063 block cnt=1 obj#=47296 tim=5124728501 WAIT #14: nam='direct path read' ela= 4354 file number=17 first dba=75310 block cnt=1 obj#=47296 tim=5124732920 WAIT #14: nam='direct path read' ela= 2166 file number=17 first dba=75326 block cnt=1 obj#=47296 tim=5124735139 WAIT #14: nam='direct path read' ela= 2160 file number=17 first dba=75342 block cnt=1 obj#=47296 tim=5124737364 WAIT #14: nam='direct path read' ela= 2 file number=17 first dba=75358 block cnt=1 obj#=47296 tim=5124737440 WAIT #14: nam='direct path read' ela= 5825 file number=17 first dba=75374 block cnt=1 obj#=47296 tim=5124743325 WAIT #14: nam='direct path read' ela= 1 file number=17 first dba=74662 block cnt=1 obj#=47296 tim=5124743406 WAIT #14: nam='direct path read' ela= 11533 file number=17 first dba=74566 block cnt=1 obj#=47296 tim=5124754998 WAIT #14: nam='direct path read' ela= 8671 file number=17 first dba=74678 block cnt=1 obj#=47296 tim=5124763743 WAIT #14: nam='direct path read' ela= 1 file number=17 first dba=74582 block cnt=1 obj#=47296 tim=5124763777 WAIT #14: nam='direct path read' ela= 3618 file number=17 first dba=74694 block cnt=1 obj#=47296 tim=5124767426 WAIT #14: nam='direct path read' ela= 2 file number=17 first dba=74963 block cnt=1 obj#=47296 tim=5124767459 WAIT #2: nam='Datapump dump file I/O' ela= 4757 count=1 intr=256 timeout=-1 obj#=47296 tim=5124772292 Has anyone else had performance issues around expdp on Oracle 10.2.0.2 on Microsoft Windows 2003. Thanks Fred

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 11 2008 - 09:11:12 CDT

This message: [ Message body ]
Next message: Riyaj Shamsudeen: "Re: AWR Report - "Segments by Row Lock Waits" -> Column "% of Capture""
Previous message: VIVEK_SHARMA: "AWR Report - "Segments by Row Lock Waits" -> Column "% of Capture""

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message