Hi all, Thanks for all the responses. So I reran the impdp in 11.2.0.4 and it looks to me that a single worker is taking advantage of parallel processing to some level. I am including a screen capture, but not sure if it will come across on the list. A search of dba_indexes and dba_tables indicates that we only have those objects setup with a degree of 0 or 1, so I am not sure if it might be inheriting the parallel=4 for the index creation even though it is only 1 worker doing the index creation. The functionality seemed different from 11.1.07 either way Thanks, Mike -----Original Message----- From: Randolf Geist [mailto:info@xxxxxxxxxxxxxxxxxxxxxxxxx] Sent: Thursday, August 21, 2014 5:32 AM To: Michael Schmitt Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: impdp performance in 11.1 vs 11.2 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
Attachment:
11204_import_p.jpg
Description: 11204_import_p.jpg
