Parallel Dml On Remote Host Not Working Even Calling A Procedure (Doc ID 1231725.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.

Goal

We create a procedure to perform the PDML:
CREATE OR REPLACE procedure NCBEX.sri_test_dist_parallel
as
begin
commit;
execute immediate 'alter session enable parallel dml';
insert /*+ parallel(a,6) */ into sri_test_dist a select rownum+5,rownum+10 from inst_fact_ncbex;
commit;
end;
/

Executing the procedure locally we can see below execution plan where both the select and insert are executed in parallel (ie: load as select operation has a parallel entry)

PLAN_TABLE_OUTPUT
-------------------------------------
INSERT /*+ parallel(a,6) */ INTO SRI_TEST_DIST A SELECT ROWNUM+5,ROWNUM+10 FROM INST_FACT_NCBEX

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 32 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20001 | 250K| 32 (0)| 00:00:01 | | | Q2,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | | Q2,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | | Q2,01 | PCWC | |
| 5 | PX RECEIVE | | 250K| 32 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN | :TQ20000 | 250K| 32 (0)| 00:00:01 | | | | S->P | RND-ROBIN |
| 7 | COUNT | | | | | | | | | |
| 8 | PX COORDINATOR | | | | | | | | | |
| 9 | PX SEND QC (RANDOM) | :TQ10000 | 250K| 32 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 10 | PX BLOCK ITERATOR | | 250K| 32 (0)| 00:00:01 | 1 | 14 | Q1,00 | PCWC | |
| 11 | BITMAP CONVERSION TO ROWIDS | | 250K| 32 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 12 | BITMAP INDEX FAST FULL SCAN| NCBEX_INSF_ISCORE | | | | 1 | 42 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

12 - access(:Z>=:Z AND :Z<=:Z)



2)when we don't include the parallel hint inside the sql we get below execution plan when executing procedure in local or remote, so here insert is not done in parallel

PLAN_TABLE_OUTPUT
-------------------------------------
INSERT INTO SRI_TEST_DIST SELECT ROWNUM+5,ROWNUM+10 FROM INST_FACT_NCBEX

-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 32 (100)| | | | | | |
| 1 | COUNT | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 250K| 32 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 250K| 32 (0)| 00:00:01 | 1 | 14 | Q1,00 | PCWC | |
| 5 | BITMAP CONVERSION TO ROWIDS | | 250K| 32 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 6 | BITMAP INDEX FAST FULL SCAN| NCBEX_INSF_ISCORE | | | | 1 | 42 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)



3) If we execute the procedure from remote with parallel hint set, we may expect the plan as in 1) above, as we're just doing everything in same database
(ie there are no distributed selects nor DML)

In such scenario, the select goes in parallel but there is no PDML

PLAN_TABLE_OUTPUT
-------------------------------------
INSERT /*+ parallel(a,6) */ INTO SRI_TEST_DIST A SELECT ROWNUM+5,ROWNUM+10 FROM INST_FACT_NCBEX

-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 32 (100)| | | | | | |
| 1 | COUNT | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 250K| 32 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 250K| 32 (0)| 00:00:01 | 1 | 14 | Q1,00 | PCWC | |
| 5 | BITMAP CONVERSION TO ROWIDS | | 250K| 32 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 6 | BITMAP INDEX FAST FULL SCAN| NCBEX_INSF_ISCORE | | | | 1 | 42 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms