Home » RDBMS Server » Server Utilities » Parallel IMPDP not working, Jobs spawning only a single Worker Process (Oracle Database, 11.2.0.3, RHEL 5.8)
Parallel IMPDP not working, Jobs spawning only a single Worker Process [message #623668] Fri, 12 September 2014 07:41 Go to next message
MTC_SysAdmin
Messages: 3
Registered: September 2014
Location: Windhoek, Namibia
Junior Member

Hi,


I'm running an 11.2.0.3 Database for the Data Warehouse.

I'm busy with testing Exporting and Importing a Production Table.

I managed to run EXPDP Parallel=10 successfully, extracting 60GB of data in about 4 Minutes. Really pleased with that.


The problems come in when I Import this Data to a "Replica Table" on the same Server, on the same Database and Schema.

Does not matter what I set the Parallel setting to for IMPDP, it just spawns a Single DW Process, and I can see the data is imported in Serial by running the STATUS command in IMPDP regularly.


I used the following Parameter File for EXPDP (I'm testing with 10 Partitions first):

DIRECTORY=EXPDP
DUMPFILE=PPTSIDW.FACT_PREPAID_GSM_CALLS_%U.expdp.dmp
LOGFILE=PPTSIDW.FACT_PREPAID_GSM_CALLS.expdp.log
TABLES=PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130324,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130325,PPTSIDW.FA CT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_2
0130326,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130327,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130328,PPTSIDW.F ACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_
20130329,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130330,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130331,PPTSIDW. FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS
_20130401,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130402,PPTSIDW.FACT_PREPAID_GSM_CALLS:PREPAID_GSM_CALLS_20130403
CONTENT=DATA_ONLY
PARALLEL=10
FILESIZE=100G



I used the following IMPDP Parameter File (using REMAP_TABLE to Import to the "Replica Table"):

DIRECTORY=EXPDP
DUMPFILE=PPTSIDW.FACT_PREPAID_GSM_CALLS_%U.expdp.dmp
LOGFILE=PPTSIDW.FACT_PREPAID_GSM_CALLS.impdp.log
TABLES=PPTSIDW.FACT_PREPAID_GSM_CALLS
REMAP_TABLE=PPTSIDW.FACT_PREPAID_GSM_CALLS:FACT_PREPAID_GSM_CALLS_CT
PARALLEL=8


While the Import is running, I check the status of the job by using "IMPDP .. ATTACH=.."

Import> status

Job: SYS_IMPORT_TABLE_01
Operation: IMPORT
Mode: TABLE
State: EXECUTING
Bytes Processed: 12,851,799,152
Percent Done: 17
Current Parallelism: 8
Job Error Count: 0
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_%u.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_01.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_02.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_03.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_04.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_05.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_06.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_07.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_08.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_09.expdp.dmp
Dump File: /_EXPDP/PPTSIDW.FACT_PREPAID_GSM_CALLS_10.expdp.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: PPTSIDW
Object Name: FACT_PREPAID_GSM_CALLS
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 3
Completed Bytes: 6,501,306,480
Worker Parallelism: 8

Import>



Some more information:

I created the "Replica Table" using the DDL of the Source Table, and altered the table to allow for Parallel 10.
I removed all Indexes and Constraints on the Replica


Regards
Stephan

[Updated on: Fri, 12 September 2014 08:59]

Report message to a moderator

Re: Parallel IMPDP not working, Jobs spawning only a single Worker Process [message #623690 is a reply to message #623668] Fri, 12 September 2014 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe the other tables are done and there is only one left.

Re: Parallel IMPDP not working, Jobs spawning only a single Worker Process [message #623697 is a reply to message #623690] Fri, 12 September 2014 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/database/121/SUTIL/dp_import.htm#SUTIL921
Re: Parallel IMPDP not working, Jobs spawning only a single Worker Process [message #623727 is a reply to message #623690] Sat, 13 September 2014 06:55 Go to previous messageGo to next message
MTC_SysAdmin
Messages: 3
Registered: September 2014
Location: Windhoek, Namibia
Junior Member

It is one table with multiple partitions. There was 10 Files Created with the EXPDP, thus partition data was split among them, so IMPDP should detect that there is more than one file, and thus run more than one DW Process. But its not.
Re: Parallel IMPDP not working, Jobs spawning only a single Worker Process [message #623730 is a reply to message #623697] Sat, 13 September 2014 07:20 Go to previous messageGo to next message
MTC_SysAdmin
Messages: 3
Registered: September 2014
Location: Windhoek, Namibia
Junior Member

I have already read that page, as well as the following document, my settings are correct: http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/datapump11gr2_parallel_1106.pdf

The problem I have with IMPDP is that it is creating multiple PX processes for One Data Worker, where it should create multiple Data Worker processes...
Re: Parallel IMPDP not working, Jobs spawning only a single Worker Process [message #623733 is a reply to message #623730] Sat, 13 September 2014 08:26 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
submit Service Request with MOS.
Previous Topic: sql loader control file+ Unix scripting
Next Topic: SQL*Loader-510: Physical record in data file is longer than the maximum(20971520)
Goto Forum:
  


Current Time: Thu Mar 28 10:52:28 CDT 2024