11.2.0.1 (64-bit / OEL 5.4) - Performance problem with impdp
- From: "Vijay Singh" <vsingh@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 May 2010 09:51:40 -0500
Hi,
I am trying to move a database to linux (OEL 5.4, 64-bit) and
11.2.0.1. This happens to be an EBS R12.0.4 database.
o source 11.1.0.6(32-bit), database size ~250GB, expdp takes 4 hours
to write 43GB dmp files
o destination is 11.2.0.1 (64-bit), after import database size is
~125GB, impdp takes 48 hours
48 hours !
So, I turned on the metrics and I found that the following took more
than an hour -
Completed 43369 PACKAGE_BODY objects in 86603 => 24 hours
Completed 346292 OBJECT_GRANT objects in 46907 => 13 hours
Completed 57170 INDEX objects in 24014 => 7 hours
Some more reading thru the trace and I find that even though parallel
is set to 8 (this is running on a box with 2 Quad cores) and 64GB RAM -
Problem 1=> OBJECT_GRANT and being done by a single worker and the trace
shows the following amounting to 9 out of 13 hours
(KUPW: KUPP$PROC.CHANGE_USER called.
KUPW: KUPP$PROC.CHANGE_USER returned.
KUPW: HARDconnected to SYS)
KUPW:22:40:58.407: 1: In function CONNECT_USER with connect type HARD
current user ALR new user SYS
KUPW:22:40:58.407: 1: KUPP$PROC.CHANGE_USER called.
KUPW:22:40:58.434: 1: KUPP$PROC.CHANGE_USER returned.
KUPW:22:40:58.434: 1: HARDconnected to SYS
KUPW:22:40:58.434: 1: DBMS_LOB.TRIM called. v_single_ddl_clob
KUPW:22:40:58.434: 1: DBMS_LOB.TRIM returned.
KUPW:22:40:58.434: 1: In procedure BUILD_OBJECT_STRINGS
KUPW:22:40:58.434: 1: Process order: 49451 and order number: 1
KUPW:22:40:58.434: 1: DBMS_LOB.COPY called.
KUPW:22:40:58.435: 1: DBMS_LOB.COPY returned.
KUPW:22:40:58.435: 1: In procedure PUT_DDL with OBJECT_GRANT:"ALR"
KUPW:22:40:58.435: 1: In function CONNECT_USER with connect type HARD
current user SYS new user ALR
KUPW:22:40:58.435: 1: KUPP$PROC.CHANGE_USER called.
KUPW:22:40:58.435: 1: KUPP$PROC.CHANGE_USER returned.
KUPW:22:40:58.435: 1: HARDconnected to ALR
KUPW:22:40:58.435: 1: In procedure RECREATE_DDL
KUPW:22:40:58.440: 1: Object created
Problem 2=> the package bodies are being loaded in serial by a single
worker !
Is anyone else seeing this ?
Any ideas on how to go about get this done faster ?
Thanks,
VKS
--
//www.freelists.org/webpage/oracle-l
Other related posts:
