RE: impdp performance in 11.1 vs 11.2
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: Michael Schmitt <mschmitt@xxxxxxxxxxxx>
- Date: Thu, 21 Aug 2014 12:31:36 +0200
Hi Michael,
since you have identified the index creation step as the culprit it is
very likely you're running into a bug (8604502) introduced in 11.1.0.7
where indexes that are marked as PARALLEL aren't generated in PARALLEL
by IMPDP. This was fixed in 11.2.0.2, so that the CREATE INDEX
statements issued by IMPDP for parallel indexes are then using the
PARALLEL clause again.
This might explain the difference in run time you see.
All this doesn't change the fact that the index creation step of IMPDP
will always be performed by a single worker thread (possibly making use
of Parallel Execution for a single CREATE INDEX with the bug fix in
place) even if the PARALLEL clause was used for IMPDP, so if you're
faced with literally thousands of serial index objects this approach
very likely doesn't really make efficient use of the resources
available, even with the bug fix in place.
Some time ago I've therefore developed a small PL/SQL package that with
the help of Advanced Queueing allows to run index creation DDLs (or any
DDL in principle) with multiple worker threads in parallel.
The idea is to skip the index creation step in IMPDP, generate a SQL
file from the DUMP, massage this file using some AWK/SED scripts to
extract the CREATE INDEX statements only and correct the PARALLEL clause
and finally use this SQL file as input to my package to run those DDLs
in the file in parallel.
See this link for more details about the bug, the package and
surrounding Unix scripts:
http://oracle-randolf.blogspot.com/2011/02/concurrent-index-creation.html
And if anyone wants to give it a try it's useful to read through the
comments, since several people have encountered / tested things I
haven't done, for example the AWK/SED script step doesn't support Domain
Indexes, some very special function-based indexes etc.
Randolf
I think I might have found the issue though, but have not confirmed. I am
rerunning the 11.1.0.7 impdp this morning, and it looks like the index
creations are not running in parallel. I have 1 of the 4 processes doing work
and the other 3 are sitting on "wait for unread message on broadcast channel".
All 4 processes were loading data during the table creations. I will upgrade
again after this to see if 11.2.0.4 runs the index creation in parallel
--
//www.freelists.org/webpage/oracle-l
Other related posts:
