In this article I have shown how to prove benefit of using append hint with PDML enabled.
This is benefical for datawarehoue load job.
This test has been done in oracle database 19c and 26 ai to show the change in feature
--I have created a script which does following
1. Create an empty table with same structure using CTAS but with 1=2 condition
2. Load data from a big table with append parallel hint
3. show execution plan which clearly says PDML is disabled
I have seen many cases where developer thinks parallelism is enabled for their bulk insert.
This is not true as PDML is always disabled by default.
SQL> @test_pdml.sql
11:53:24 SQL> set pagesize 3000
11:53:24 SQL> create table cisadm.ci_cc_bkp tablespace cists_01 as select * from cisadm.ci_cc where 1=2;
Table created.
11:53:24 SQL> insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from cisadm.ci_cc;
125347818 rows created.
12:03:37 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13bpyjxguarsc, child number 0
-------------------------------------
insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from
cisadm.ci_cc
Plan hash value: 547790614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | CI_CC_BKP | | 2070K| 2070K| 2070K (0)|
| 2 | PX COORDINATOR | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 125M| | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 125M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 125M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 125M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 15
- PDML is disabled in current session
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
32 rows selected.
In this case we can see that Select is using parallel query slave but hint clearly says PDML is disabled.
What is the effect of APPEND here?
Basically it bypasses buffer cache and load data directly into table above high water mark.Once it is committed high water mark is adjusted.
This can be proved like below
get the OS process id of the session which is executing SQL.This can be found using below
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
grep -i "direct path read" ccbx_p001_71004.trc
WAIT #140032935645648: nam='direct path write' ela= 270 file number=75 first dba=1147016 block cnt=8 obj#=4538084 tim=432713792172
WAIT #140032935645648: nam='direct path write' ela= 974 file number=75 first dba=4062721 block cnt=15 obj#=4538084 tim=432730881085
WAIT #140032935645648: nam='direct path write' ela= 94 file number=60 first dba=2092031 block cnt=1 obj#=4538084 tim=432732169887
WAIT #140032935645648: nam='direct path write' ela= 315 file number=123 first dba=2764673 block cnt=15 obj#=4538084 tim=432732178566
WAIT #140032935645648: nam='direct path write' ela= 347 file number=123 first dba=3763601 block cnt=15 obj#=4538084 tim=432742177527
We can also check like below from ash
select sample_time,SESSION_ID,SESSION_SERIAL#,EVENT from v$active_session_history where event like '%direct path write%'
SQL> /
SAMPLE_TIME SESSION_ID SESSION_SERIAL# EVENT
------------------------------ ---------- --------------- ----------------------------------------------------------------
13-APR-26 01.34.58.375 PM 81 32622 direct path write
13-APR-26 01.34.41.367 PM 81 32622 direct path write
13-APR-26 01.34.34.363 PM 81 32622 direct path write
It takes almost 7 mis to load the data into a table.
Now we will enable parallel DML in session level and see whether we can count records inserted
12:45:45 SQL> alter session enable parallel dml;
Session altered.
12:45:45 SQL> insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from cisadm.ci_cc;
125347818 rows created.
12:47:08 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13bpyjxguarsc, child number 2
-------------------------------------
insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from
cisadm.ci_cc
Plan hash value: 2226083196
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 125M| | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| CI_CC_BKP | | 12M| 12M| 2070K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 125M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 125M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 125M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 144 because of degree limit
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Now it completed in 2 minutes and there is a slight change in the plan operation from
12:03:37 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13bpyjxguarsc, child number 0
-------------------------------------
insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from
cisadm.ci_cc
Plan hash value: 547790614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | CI_CC_BKP | | 2070K| 2070K| 2070K (0)|
| 2 | PX COORDINATOR | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 125M| | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 125M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 125M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 125M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 15
- PDML is disabled in current session
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
32 rows selected.
In this case we can see that Select is using parallel query slave but hint clearly says PDML is disabled.
What is the effect of APPEND here?
Basically it bypasses buffer cache and load data directly into table above high water mark.Once it is committed high water mark is adjusted.
This can be proved like below
get the OS process id of the session which is executing SQL.This can be found using below
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
grep -i "direct path read" ccbx_p001_71004.trc
WAIT #140032935645648: nam='direct path write' ela= 270 file number=75 first dba=1147016 block cnt=8 obj#=4538084 tim=432713792172
WAIT #140032935645648: nam='direct path write' ela= 974 file number=75 first dba=4062721 block cnt=15 obj#=4538084 tim=432730881085
WAIT #140032935645648: nam='direct path write' ela= 94 file number=60 first dba=2092031 block cnt=1 obj#=4538084 tim=432732169887
WAIT #140032935645648: nam='direct path write' ela= 315 file number=123 first dba=2764673 block cnt=15 obj#=4538084 tim=432732178566
WAIT #140032935645648: nam='direct path write' ela= 347 file number=123 first dba=3763601 block cnt=15 obj#=4538084 tim=432742177527
We can also check like below from ash
select sample_time,SESSION_ID,SESSION_SERIAL#,EVENT from v$active_session_history where event like '%direct path write%'
SQL> /
SAMPLE_TIME SESSION_ID SESSION_SERIAL# EVENT
------------------------------ ---------- --------------- ----------------------------------------------------------------
13-APR-26 01.34.58.375 PM 81 32622 direct path write
13-APR-26 01.34.41.367 PM 81 32622 direct path write
13-APR-26 01.34.34.363 PM 81 32622 direct path write
It takes almost 7 mis to load the data into a table.
Now we will enable parallel DML in session level and see whether we can count records inserted
12:45:45 SQL> alter session enable parallel dml;
Session altered.
12:45:45 SQL> insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from cisadm.ci_cc;
125347818 rows created.
12:47:08 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 13bpyjxguarsc, child number 2
-------------------------------------
insert into cisadm.ci_cc_bkp select /*+ append parallel */ * from
cisadm.ci_cc
Plan hash value: 2226083196
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 125M| | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| CI_CC_BKP | | 12M| 12M| 2070K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 125M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 125M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 125M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 144 because of degree limit
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Now it completed in 2 minutes and there is a slight change in the plan operation from
LOAD AS SELECT to LOAD AS SELECT (HYBRID TSM/HWMB)
This has been explained in detailed in below blog which says temporary segments are created and merged together to support parallel DML.
https://blogs.oracle.com/optimizer/space-management-and-oracle-direct-path-load#:~:text=As%20the%20name%20suggests%2C%20this%20is%20a,segment%20merge%20and%20high%20water%20mark%20brokering
We also see below error as in 19c we can not read in the same session where parallel DML is enabled.This restriction has been lifted in 26ai
11:52:38 SQL> select count(*) from cisadm.ci_cc_bkp;
select count(*) from cisadm.ci_cc_bkp
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
So if you have a code which update another tables based on some updated data we just did then transaction will fail.
Now we did same thing in oracle 26 ai but this time with hint enable_parallel_dml
13:28:52 SQL> set pagesize 3000
13:28:52 SQL> alter session set container=PDBTEST;
Session altered.
13:28:52 SQL> create table cisadm.ci_cc_bkp tablespace cists_01 as select * from cisadm.ci_cc where 1=2;
Table created.
13:28:52 SQL> insert into cisadm.ci_cc_bkp /*+ enable_parallel_dml */ select /*+ append parallel */ * from cisadm.ci_cc;
125347818 rows created.
13:35:34 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cawjwksmavw38, child number 0
-------------------------------------
insert into cisadm.ci_cc_bkp /*+ enable_parallel_dml */ select /*+
append parallel */ * from cisadm.ci_cc
Plan hash value: 547790614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | CI_CC_BKP | | 2071K| 2071K| 2071K (0)|
| 2 | PX COORDINATOR | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 113M| | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 113M| 256K| 256K| 4096K (0)|
| 5 | PX BLOCK ITERATOR | | 113M| | | |
|* 6 | TABLE ACCESS FULL | CI_CC | 113M| | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
- PDML is disabled in current session
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
33 rows selected.
13:35:34 SQL> alter session enable parallel query;
Session altered.
13:35:34 SQL> select count(*) from cisadm.ci_cc_bkp;
COUNT(*)
----------
125347818
13:38:02 SQL> commit;
Commit complete.
So we see hint report says PDML is disabled.
This works fine when we use both hint parallel enable_parallel_dml together..
insert /*+ enable_parallel_dml parallel */ into cisadm.ci_cc_bkp
select /*+ append parallel */ * from cisadm.ci_cc
