RE: Parallel DML
Date: Thu, 23 Jun 2016 16:12:00 -0400
Message-ID: <8EA4B8FB14B7884381AF85059F549A4E061CCAB0B1_at_NYEXVS01.corp.exlservice.com>
Ram, could it be that the DBA has a custom Resource Manager plan in place to limit degree-of-parallelism? Are both inserts executed by the same user, at the same time of day, with the same session Module and Action values, from the same client program? Any of those variables (and likely others I have forgotten) could cause the Resource Manager to limit degree-of-parallelism depending on what has been configured on your database.
Gary
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Thursday, June 23, 2016 3:52 PM
To: oracle-l-freelists; ram.cheruvattath_at_gmail.com
Subject: Re: Parallel DML
Ram, have you checked the plans for the various parallel specifications to see if at some point the plan changes? What are all your database parallel parameter settings? (parallel_degree_limit)
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ram Cheruvattath <ram.cheruvattath_at_gmail.com> Sent: Thursday, June 23, 2016 3:24:00 PM To: Ram Cheruvattath; oracle-l-freelists Subject: Re: Parallel DML
To clarify my statement below:
"However, any increase in parallel thereafter does not result in an increase in slaves."
What I meant is "However, any increase in parallel thereafter does not result in doubling of slaves."
In other words, increasing the parallelism for INSERT and SELECT to 8 does not result in 16 slaves. It results in 8 slaves.
Ram
From: Ram Cheruvattath<mailto:ram.cheruvattath_at_gmail.com>
Sent: Thursday, June 23, 2016 2:19 PM
To: oracle-l-freelists<mailto:oracle-l_at_freelists.org>
Subject: Parallel DML
Hi
We have a situation wherein we need to copy the data from one table to another. Both source and target tables are partitioned. There are no constraints other than NULL constraints on the target table. There are no indexes, triggers etc. The target table is no logging mode.
We are using parallel DML with direct path load. The issue is that we are not able to scale beyond 4 slaves even of the PARALLEL clause used is more than 2. The sample script below uses 2 for both INSERT and SELECT portion of the statement. We see 4 slaves being used. However, any increase in parallel thereafter does not result in an increase in slaves.
Interestingly enough, there is another SQL that I have (posting it below this example) which works as expected.
Please let me know your thoughts.
Ram
Sample PARALLEL LOAD AS SELECT script
EXAMPLE 1
alter session set current_schema=MY_APP;
alter session enable parallel dml;
set echo on timing on
insert /*+ APPEND PARALLEL(2) */ into piece_cache_temp partition (PCECCHE_105) SELECT /*+ PARALLEL(2) */ * from piece_cache partition (PCECCHE_105);
ROLLBACK;
CREATE TABLE MY_APP.PIECE_CACHE_TEMP
(
ZZZZZZ_PARTITION_KEY NUMBER(4) NOT NULL, DATE_PARTITION_KEY NUMBER(3) NOT NULL, ZZZZG_DATE DATE NOT NULL, IMB_MID_SEQ_ID NUMBER(6) NOT NULL, JOB_SEQ_ID NUMBER(10) NOT NULL, ML_CL_CODE NUMBER(2) NOT NULL, IMB_SERIAL_NBR VARCHAR2(9 BYTE) NOT NULL, IMB_CODE_PARTIAL VARCHAR2(16 BYTE), PIECE_RANGE_SEQ_ID NUMBER(15), PHYS_PIECE_SEQ_ID NUMBER(16), YYY_GRP_SEQ_ID NUMBER(13) NOT NULL, YYY_SCAN_PARTITION_KEY NUMBER(4) NOT NULL, ACTIVE_IND VARCHAR2(1 BYTE) NOT NULL, UNIQUE_IND VARCHAR2(1 BYTE) NOT NULL)
TABLESPACE DATA08_TS
INITRANS 10
STORAGE (INITIAL 1M)
NOLOGGING
PARTITION BY RANGE (ZZZZZZ_PARTITION_KEY) SUBPARTITION BY LIST (DATE_PARTITION_KEY)
(
PARTITION PCECCHE_105 VALUES LESS THAN (106) NOLOGGING
TABLESPACE DATA08_TS
( SUBPARTITION PCECCHE_105_PCECCHE_1 VALUES (1) ,
SUBPARTITION PCECCHE_105_PCECCHE_2 VALUES (2) ,
SUBPARTITION PCECCHE_105_PCECCHE_3 VALUES (3) ,
SUBPARTITION PCECCHE_105_PCECCHE_4 VALUES (4) ,
SUBPARTITION PCECCHE_105_PCECCHE_5 VALUES (5) ,
SUBPARTITION PCECCHE_105_PCECCHE_6 VALUES (6) ,
SUBPARTITION PCECCHE_105_PCECCHE_7 VALUES (7) ,
SUBPARTITION PCECCHE_105_PCECCHE_8 VALUES (8) ,
SUBPARTITION PCECCHE_105_PCECCHE_9 VALUES (9) ,
SUBPARTITION PCECCHE_105_PCECCHE_10 VALUES (10) ,
SUBPARTITION PCECCHE_105_PCECCHE_11 VALUES (11) ,
SUBPARTITION PCECCHE_105_PCECCHE_12 VALUES (12) ,
SUBPARTITION PCECCHE_105_PCECCHE_13 VALUES (13) ,
SUBPARTITION PCECCHE_105_PCECCHE_14 VALUES (14) ,
SUBPARTITION PCECCHE_105_PCECCHE_15 VALUES (15) ,
SUBPARTITION PCECCHE_105_PCECCHE_16 VALUES (16) ,
SUBPARTITION PCECCHE_105_PCECCHE_17 VALUES (17) ,
SUBPARTITION PCECCHE_105_PCECCHE_18 VALUES (18) ,
SUBPARTITION PCECCHE_105_PCECCHE_19 VALUES (19) ,
SUBPARTITION PCECCHE_105_PCECCHE_20 VALUES (20) ,
SUBPARTITION PCECCHE_105_PCECCHE_21 VALUES (21) ,
SUBPARTITION PCECCHE_105_PCECCHE_22 VALUES (22) ,
SUBPARTITION PCECCHE_105_PCECCHE_23 VALUES (23)
)
)
/
EXAMPLE 2 - This works and uses more parallel slaves as the parallel hint is adjusted
create table my_app.my_tab_statistics_source
(
owner varchar2(128) not null,
table_name varchar2(128) not null,
last_analyzed date
)
tablespace MY_APP_DATA02_TS
nologging
partition by range (LAST_ANALYZED)
(
partition p2014 values less than (TO_DATE('01-01-2015','mm-dd-yyyy')),
partition p2015 values less than (TO_DATE('01-01-2016','mm-dd-yyyy')),
partition p2016 values less than (TO_DATE('01-01-2017','mm-dd-yyyy'))
)
spool insert1.txt
alter session enable parallel dml;
set echo on timing on
insert /*+ APPEND PARALLEL(4) */ into MY_APP.my_tab_statistics_source partition (p2016) SELECT /*+ PARALLEL(4) */ OWNER, TABLE_NAME, SYSDATE FROM DBA_TAB_STATISTICS;
COMMIT;
spool off
exit
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 23 2016 - 22:12:00 CEST