Ram, the blog article is interesting and I notice the HWMB in the second plan.
________________________________
From: Ram Cheruvattath <ram.cheruvattath@xxxxxxxxx>
Sent: Thursday, June 23, 2016 6:00:45 PM
To: Powell, Mark; oracle-l-freelists
Subject: Re: Parallel DML
Mark
Yes. There is a difference. See the plans posted below. Also, I found the note
below quite interesting. This is a 12c DB.
https://blogs.oracle.com/optimizer/entry/space_management_and_oracle_direct<wlmailhtml:{DAB7CC91-9C3D-4131-B679-E7A45FBD2DF9}mid://00000234/!x-usc:https://urldefense.proofpoint.com/v2/url?u=https-3A__blogs.oracle.com_optimizer_entry_space-5Fmanagement-5Fand-5Foracle-5Fdirect&d=CwMFAg&c=eIGjsITfXP_y-DLLX0uEHXJvU8nOHrUK8IrwNKOtkVU&r=BQgWMnANBrFKIxy7eYLV9rs0sx8tAoOokSSn_iV5PCY&m=IH7fABbJCIPwIyUKN1bHFkHXvP9_qksw5pF2s-_vJH4&s=BGQJddvgIk6-Wo6X4K3CwNRZsdiSUMdoADQQNNfhtRc&e=>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 957yggnqcv36a, child number 0
-------------------------------------
insert /*+ APPEND PARALLEL(2) MONITOR */ into piece_cache_temp
partition (PCECCHE_105) SELECT /*+ PARALLEL(2) */ * from piece_cache
partition (PCECCHE_105)
Plan hash value: 363843888
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | |
2070 (100)| | | | | | |
| 1 | PX COORDINATOR | | | |
| | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2434K| 185M|
2070 (1)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HIGH WATER MARK)| | | |
| | | | Q1,01 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 2434K| 185M|
2070 (1)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 2434K| 185M|
2070 (1)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND PARTITION (KEY) | :TQ10000 | 2434K| 185M|
2070 (1)| 00:00:01 | | | Q1,00 | P->P | PART (KEY) |
| 7 | PX BLOCK ITERATOR | | 2434K| 185M|
2070 (1)| 00:00:01 | 1 | 23 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | PIECE_CACHE | 2434K| 185M|
2070 (1)| 00:00:01 | 2393 | 2415 | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1sf9djw5nyy5k, child number 0
-------------------------------------
insert /*+ APPEND PARALLEL(4) MONITOR */ into piece_cache_temp
partition (PCECCHE_105) SELECT /*+ PARALLEL(4) */ * from piece_cache
partition (PCECCHE_105)
Plan hash value: 1431563342
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | |
| 1035 (100)| | | | | | |
| 1 | PX COORDINATOR | | |
| | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 2434K|
185M| 1035 (1)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HIGH WATER MARK BROKERED)| | |
| | | | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 2434K|
185M| 1035 (1)| 00:00:01 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 2434K|
185M| 1035 (1)| 00:00:01 | 1 | 23 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | PIECE_CACHE | 2434K|
185M| 1035 (1)| 00:00:01 | 2393 | 2415 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
From: Powell, Mark<mailto:mark.powell2@xxxxxxx>
Sent: Thursday, June 23, 2016 3:51 PM
To: oracle-l-freelists<mailto:oracle-l@xxxxxxxxxxxxx> ;
ram.cheruvattath@xxxxxxxxx<mailto:ram.cheruvattath@xxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Ram Cheruvattath
<ram.cheruvattath@xxxxxxxxx<mailto:ram.cheruvattath@xxxxxxxxx>>
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@xxxxxxxxx>
Sent: Thursday, June 23, 2016 2:19 PM
To: oracle-l-freelists<mailto:oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
