Performance of datapump export.
- From: Fred Tilly <ftilly@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 11 Aug 2008 14:11:12 +0000 (GMT)
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
Other related posts:
- » Performance of datapump export.
