Home » RDBMS Server » Server Utilities » Increasing parallel process in impdp in runtime (10g 64 bit enterprise edition, Linux 5.3 64bit)
Increasing parallel process in impdp in runtime [message #551097] Sun, 15 April 2012 11:21 Go to next message
morad_dba
Messages: 72
Registered: June 2008
Member

Dear all,

I started datapump not using parallel option

I issued the following command

impdp t24/t24 directory=dp_dump dumpfile=bef_cob_%U.dmp schemas=t24

The total dumpfile size is 200 GB..

Now I want to add parallel process to the job..

Please help me.. how can i do it.


Regards,

Morad
Re: Increasing parallel process in impdp in runtime [message #551101 is a reply to message #551097] Sun, 15 April 2012 11:39 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
bcm@bcm-laptop:~$ impdp help=yes

Import: Release 11.2.0.1.0 - Production on Sun Apr 15 09:37:52 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keywords are: SKIP_CONSTRAINT_ERRORS.

DIRECTORY
Directory object to be used for dump, log and sql files.

DUMPFILE
List of dumpfiles to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ESTIMATE
Calculate job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Import everything from source [Y].

HELP
Display help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

LOGFILE
Log file name [import.log].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine datafile references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_TABLESPACE
Tablespace object are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [N].

SCHEMAS
List of schemas to import.

SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION
Edition to be used for extracting metadata.

SQLFILE
Write all the SQL DDL to a specified file.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION
Enable the loading of Streams metadata

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to import.

TARGET_EDITION
Edition to be used for loading metadata.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES
List of datafiles to be imported by transportable mode.

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.



can you read & do the necessary changes now?
Re: Increasing parallel process in impdp in runtime [message #575936 is a reply to message #551101] Tue, 29 January 2013 10:24 Go to previous messageGo to next message
rzmuda
Messages: 4
Registered: January 2013
Location: Poland/Wroclaw
Junior Member
Dear Swan,
If you have hothing to say - just don't. The question was how to change parallelism level on already running datapump job, not how to use parallel parameter in datapump, so read and understand what's you reading, everybody knows how to display datapump help Wink

Answering the original question :
To increase level of parallelism on running datapump job do the following :
- identify job name (select owner_name, job_name from dba_datapump_jobs);
- connect to it : impdp attach=schema.job_name
- change parallelism : parallel=n (n - number of parallel workers)
- wait a bit for change to aplly
- confirm change : status

Regards
Rafal Zmuda
Re: Increasing parallel process in impdp in runtime [message #575939 is a reply to message #575936] Tue, 29 January 2013 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Dear Rafal Zmuda,

Do you honestly think that Morad has been patiently waiting 9+ MONTHS for your words of wisdom?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Increasing parallel process in impdp in runtime [message #575941 is a reply to message #575936] Tue, 29 January 2013 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, are you sure parallel parameter can be dynamically changed?
Did you try it?

Regards
Michel
Re: Increasing parallel process in impdp in runtime [message #575945 is a reply to message #575941] Tue, 29 January 2013 12:15 Go to previous messageGo to next message
rzmuda
Messages: 4
Registered: January 2013
Location: Poland/Wroclaw
Junior Member
Well, i'm sure he didn't but the idea of forums is to help others with the same problem as well. That's why i posted this, and yes i have checked this. It is possible to change parallel execution for datapump jobs dynamically.

Regards
Rafal
Re: Increasing parallel process in impdp in runtime [message #575946 is a reply to message #575945] Tue, 29 January 2013 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When I said "did you check it", I meant did you check that ALL the workers are working not are just present.

Regards
Michel
Re: Increasing parallel process in impdp in runtime [message #575947 is a reply to message #575946] Tue, 29 January 2013 12:41 Go to previous messageGo to next message
rzmuda
Messages: 4
Registered: January 2013
Location: Poland/Wroclaw
Junior Member
Of course i did, all workers were in EXECUTING state. What kind of question is it ?
Re: Increasing parallel process in impdp in runtime [message #575952 is a reply to message #575947] Tue, 29 January 2013 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What kind of question is it ?


A legitimate question.
The workers are not always working EVEN if you set a PARALLEL degree.
For instance, creation of indexes or gathering or statistics are ALWAYS done by only ONE worker, the other ones are WAITING FOR WORK.
So the fact that Oracle adds more workers does not mean they are working.

Regards
Michel

[Updated on: Tue, 29 January 2013 13:40]

Report message to a moderator

Re: Increasing parallel process in impdp in runtime [message #575954 is a reply to message #575952] Tue, 29 January 2013 13:47 Go to previous messageGo to next message
rzmuda
Messages: 4
Registered: January 2013
Location: Poland/Wroclaw
Junior Member
Yes, i know that. In this case there were 25 dump files, around 30GB each. Import was kicked with parallel 8, then i changes it to parallel 14. All 14 workers were busy importing data (EXECUTING).

Regards,
Rafal
Re: Increasing parallel process in impdp in runtime [message #611911 is a reply to message #575954] Thu, 10 April 2014 02:15 Go to previous messageGo to next message
dave1234
Messages: 1
Registered: April 2014
Junior Member
Thank you for answering the question of how to increase number of worker threads even though it was posted long time ago.

I'm a newbie trying to get import and indexing done faster. Oracle documentation says parallel parameter "Enables you to increase or decrease the number of active worker processes and/or PQ slaves for the current job". So i was wondering why 3 of the 4 threads were sitting idle.

From your answer I understand that there needs to be multiple dump files to use parallel processing. Now I can ask dba to use parallel parameter when creating dump file so that I can import faster.
Re: Increasing parallel process in impdp in runtime [message #611918 is a reply to message #611911] Thu, 10 April 2014 02:42 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum (and the zombie topic). Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

When trying to tune an import, I always:
set all indexes to nologging, before the export
set all constraints to enable novalidate, before the export
set _pga_max_size=2147483648, before the import
Re: Increasing parallel process in impdp in runtime [message #611982 is a reply to message #611911] Thu, 10 April 2014 09:28 Go to previous messageGo to next message
Roachcoach
Messages: 1198
Registered: May 2010
Location: UK
Senior Member
dave1234 wrote on Thu, 10 April 2014 08:15
Thank you for answering the question of how to increase number of worker threads even though it was posted long time ago.

I'm a newbie trying to get import and indexing done faster. Oracle documentation says parallel parameter "Enables you to increase or decrease the number of active worker processes and/or PQ slaves for the current job". So i was wondering why 3 of the 4 threads were sitting idle.

From your answer I understand that there needs to be multiple dump files to use parallel processing. Now I can ask dba to use parallel parameter when creating dump file so that I can import faster.



Parallel with datapump is more complicated than you'd expect.

http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/datapump11gr2_parallel_1106.pdf

Object size is a factor and, as I discovered to my horror, LOBs don't count. I had a 200mb "table" with xTbs of LOB data....serial expdp. "Have a nice day". Sure, it's sensible in the strictist terms but slightly disappointing they weren't smarter with it.


Also there are differences between parallel datapump slaves and parallel DB processes. And import behaves differently from export.


Somewhat irritatingly I've only ever found this information in that white paper above.

[Updated on: Thu, 10 April 2014 09:31]

Report message to a moderator

Re: Increasing parallel process in impdp in runtime [message #611983 is a reply to message #611982] Thu, 10 April 2014 09:32 Go to previous message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
I've said here before.
It takes 1 woman 9 months to make a baby.
You can't have 9 women take 1 month to make a baby.
Some processes can not be parallelized.
Previous Topic: LOADER. Get all errors at once
Next Topic: SQLLDR conventional load via multiple sqlldr instances
Goto Forum:
  


Current Time: Sat Aug 30 15:52:18 CDT 2014

Total time taken to generate the page: 0.11133 seconds