Oracle 19c New Database Parameter (MAX_DATAPUMP_PARALLEL_PER_JOB)
Description:-
It specifies the maximum number of parallel processes allowed per Oracle Data Pump job.
When this parameter has a value of AUTO, Oracle Data Pump derives its value to be 50 percent of the value of the SESSIONS initialization parameter.
Note:This parameter is available starting with Oracle Database release 19c, version 19.1.
It introduced to control the number of parallel workers that can be used for an individual Data Pump job. This is an improvement to implement resource usage policies in DataPump.
In a database with MAX_DATAPUMP_PARALLEL_PER_JOB set to N, even if parallel>N is specified for the DataPump job, only N number of Worker processes will be started,
Demo:-
Changing Parameter at database level
[oracle@dev19c ~]$ export ORACLE_SID=dbwr
[oracle@dev19c ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Sep 30 23:50:54 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show parameter MAX_DATAPUMP_PARALLEL_PER_JOB
NAME TYPE VALUE
———————————— ———– ——————————
max_datapump_parallel_per_job string 50 (default)
Changing parameter from 50 to 1 (default is 50)
SQL> alter system set MAX_DATAPUMP_PARALLEL_PER_JOB=1 container=all;
System altered.
SQL> show parameter MAX_DATAPUMP_PARALLEL_PER_JOB
NAME TYPE VALUE
———————————— ———– ——————————
max_datapump_parallel_per_job string 1
Example 1:-
expdp job start with parallel=4
[oracle@dev19c ~]$ expdp system/oracle@dbwrpdb directory=DATA_PUMP_DIR dumpfile=djob1.dmp full=yes parallel=4 logtime=all metrics=yes
Export: Release 19.0.0.0.0 – Production on Tue Oct 1 00:03:36 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
01-OCT-19 00:03:56.315: Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/********@dbwrpdb directory=DATA_PUMP_DIR dumpfile=djob1.dmp full=yes parallel=4 logtime=all metrics=yes
01-OCT-19 00:04:00.080: W-1 Startup took 2 seconds
01-OCT-19 00:04:15.362: W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
01-OCT-19 00:06:07.838: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
01-OCT-19 00:06:15.051: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
01-OCT-19 00:06:23.905: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Note:-Only one worker process is working.even though we have given parallel=4.
Check at database level
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr and UPPER (s.program) LIKE ‘%DM0%’ or UPPER (s.program) LIKE ‘%DW0%’;
DATE PROGRAM SID
——————- ———————————————— ———-
2019-10-01 00:04:35 oracle@dev19c (DW00) 76
Example:-2
Changing Parameter at database level
[oracle@dev19c ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Oct 1 00:16:20 2019
Version 19.3.0.
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show parameter MAX_DATAPUMP_PARALLEL_PER_JOB
NAME TYPE VALUE
———————————— ———– ——————————
max_datapump_parallel_per_job string 1
– in a database with MAX_DATAPUMP_PARALLEL_PER_JOB set to 2
SQL> alter system set MAX_DATAPUMP_PARALLEL_PER_JOB=2 container=all;
System altered.
SQL> show parameter MAX_DATAPUMP_PARALLEL_PER_JOB
NAME TYPE VALUE
———————————— ———– ——————————
max_datapump_parallel_per_job string 2
– any DataPump job can have up to maximum 2 Workers, even if the job is started with parallel>2:
[oracle@dev19c ~]$ expdp system/oracle@dbwrpdb directory=DATA_PUMP_DIR dumpfile=djob2.dmp full=yes parallel=4 logtime=all metrics=yes
Export: Release 19.0.0.0.0 – Production on Tue Oct 1 00:18:24 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
01-OCT-19 00:18:48.228: Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/********@dbwrpdb directory=DATA_PUMP_DIR dumpfile=djob2.dmp full=yes parallel=4 logtime=all metrics=yes
01-OCT-19 00:18:50.177: W-1 Startup took 2 seconds
01-OCT-19 00:18:56.787: W-2 Startup took 1 seconds
01-OCT-19 00:18:56.954: W-2 Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
01-OCT-19 00:18:56.988: W-2 Completed 1 MARKER objects in 0 seconds
01-OCT-19 00:18:57.095: W-2 Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
01-OCT-19 00:18:57.105: W-2 Completed 1 MARKER objects in 0 seconds
01-OCT-19 00:19:10.888: W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
01-OCT-19 00:19:11.708: W-2 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
01-OCT-19 00:19:24.954: W-2 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
01-OCT-19 00:19:26.095: W-2 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
01-OCT-19 00:19:26.900: W-2 Completed 8 INDEX_STATISTICS objects in 1 seconds
01-OCT-19 00:19:28.027: W-2 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
01-OCT-19 00:19:28.212: W-2 Completed 1 TABLE_STATISTICS objects in 1 seconds
01-OCT-19 00:20:03.967: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
01-OCT-19 00:20:04.733: W-1 Processing object type DATABASE_EXPORT/TABLESPACE
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr and UPPER (s.program) LIKE ‘%DM0%’ or UPPER (s.program) LIKE ‘%DW0%’;
DATE PROGRAM SID
——————- ———————————————— ———-
2019-10-01 00:19:55 oracle@dev19c (DW00) 89
2019-10-01 00:19:55 oracle@dev19c (DW02) 84
Note:-There is no warning or error reported when the DP job is started with a number of parallels higher than the value defined by MAX_DATAPUMP_PARALLEL_PER_JOB.
Connect with me:-
Telegram:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba