My Oracle Support Banner

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

Last updated on MARCH 10, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

We create a procedure to perform the PDML:
CREATE OR REPLACE procedure <procedure name>
as
begin
commit;
execute immediate 'alter session enable parallel dml';
insert /*+ parallel(a,6) */ into <table name1 > a select rownum+5,rownum+10 from <table name2>;
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 <table name1>A SELECT ROWNUM+5,ROWNUM+10 FROM <table name2>

-------------------------------------------------------------------------------------------------------------------------------------------------
| 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| <Table name> | | | | 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 <table name1> SELECT ROWNUM+5,ROWNUM+10 FROM <table name2>

-------------------------------------------------------------------------------------------------------------------------------------------
| 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|<table name> | | | | 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 <table name1> A SELECT ROWNUM+5,ROWNUM+10 FROM <table name2> 

-------------------------------------------------------------------------------------------------------------------------------------------
| 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| <table name> | | | | 1 | 42 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------


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

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.