Parallel DML - Conventional (non-direct-path) Inserts As Select

In a recent discussion I've mentioned that I thought to remember that the DML part of conventional load as select inserts will always be executed serially, even with parallel DML enabled and requesting parallel DML execution. It's important to understand in this context that this is not the same as the parallel query execution of the SELECT part, which is possible independently from the parallel DML part.

After that discussion I realized that it was quite some time ago that I tested this scenario, probably it was back then with some 10.2 version.

So I quickly put together a small test case that I ran on 11g versions and the results were quite surprising which motivated me to take a closer look.

To set the scene here's a quote from the Oracle's Database Administrator's guide 10.2. You can find exactly the same paragraph in the most recent version of the guide (at the time of writing this 11.2.0.2) here, so according to the documentation nothing has changed in this regard:

"When you are inserting in parallel DML mode, direct-path INSERT is the default. In order to run in parallel DML mode, the following requirements must be met:

* You must have Oracle Enterprise Edition installed.
* You must enable parallel DML in your session. To do this, run the following statement:

ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

* You must specify the parallel attribute for the target table, either at create time or subsequently, or you must specify the PARALLEL hint for each

insert operation.

To disable direct-path INSERT, specify the NOAPPEND hint in each INSERT statement. Doing so overrides parallel DML mode."

I've highlighted the parts that are important to my test case. The first highlighted part seems to suggest that there is a "non-default" mode for parallel DML inserts, but the second highlighted part seems to tell then if you disable direct-path mode by using the NOAPPEND hint, no parallel DML will be used.

The first part also seems to suggest that when performing parallel DML the direct-path insert mode will be used as default, so without using the explicit usage of the APPEND hint. Note that this has the consequence that a statement that doesn't use the APPEND hint will use conventional inserts when parallel DML is not used, something that people are probably not that clear about.

So this:

insert /*+ parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

is not the same as this:

insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

when parallel DML is not enabled/possible.

I would always recommend to use both, the APPEND and PARALLEL hint for the INSERT part to make clear that direct-path mode and parallel DML are intended. In case parallel DML is not enabled/possible then you get at least direct-path inserts if possible.

Back to the main topic: So here is the test case that will be used across the different versions with slight variations:

set echo on timing on serveroutput off linesize 130 pagesize 999 tab off trimspool on

-- drop index t1_idx;

drop table t1;

drop table t2;

create table t1
as
select * from dba_objects;

exec dbms_stats.gather_table_stats(null, 't1')

-- create index t1_idx on t1(object_id);

create table t2
as
select * from dba_objects;

exec dbms_stats.gather_table_stats(null, 't2')

delete from t1;

commit;

-- After initial creation and delete
select
blocks
from
user_tables
where
table_name = 'T1';

alter session enable parallel dml;

variable n_parallel_dml_start number

begin
select
value
into :n_parallel_dml_start
from
v$mystat s
, v$statname n
where
n.name = 'DML statements parallelized'
and s.statistic# = n.statistic#;
end;
/

insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

select * from table(dbms_xplan.display_cursor);

set serveroutput on

declare
n_parallel_dml_end number;
begin
select
value
into n_parallel_dml_end
from
v$mystat s
, v$statname n
where
n.name = 'DML statements parallelized'
and s.statistic# = n.statistic#;
dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
end;
/

-- If it was direct-path and/or parallel DML you can't access the object within the same transaction
-- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
select count(*) from t1;

-- Active transaction(s)
select
used_ublk
, used_urec
, ptx
from
v$transaction
start with
xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
connect by
case when xid = ptx_xid then null else ptx_xid end = prior xid;

commit;

exec dbms_stats.gather_table_stats(null, 't1')

-- After insert
select
blocks
from
user_tables
where
table_name = 'T1';

And this is the result that I get from 10.2.0.5:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.19
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.60
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.65
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL>
SQL> delete from t1;

50756 rows deleted.

Elapsed: 00:00:01.18
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.23
SQL>
SQL> -- After initial creation and delete
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
697

Elapsed: 00:00:00.01
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

50757 rows created.

Elapsed: 00:00:00.57
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID ah9bvdhtcd38n, child number 0
-------------------------------------
insert /*+ append parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2

Plan hash value: 1251029007

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 22 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND ROUND-ROBIN| :TQ10000 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | P->P | RND-ROBIN |
| 6 | PX BLOCK ITERATOR | | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| T2 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------

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

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


24 rows selected.

Elapsed: 00:00:00.09
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.03
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES
1 1 YES

9 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.04
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.44
SQL>
SQL> -- After insert
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
1791

Elapsed: 00:00:00.01
SQL>

If you examine the execution plan above you'll notice two important things:

- LOAD AS SELECT denotes a direct-path insert operation
- The LOAD AS SELECT is marked as PCWP (Parallel Combined With Parent) which means that it is executed in parallel

Here is a short recap of direct-path parallel DML inserts:

- A lot of restrictions apply to Direct-Path / Parallel DML inserts - so they are only allowed if certain conditions are met

- The whole segment will be exclusively locked by this operation, which means that other sessions can only read, but not modify the segment at the same time. Note that you can restrict it to particular (sub-)partitions if used with the explicit (SUB)PARTITION syntax (insert into table_x partition (y)...), so it is possible to perform them concurrently into different partitions of the same segment

- Direct-path inserts (serial or parallel) write directly to the datafiles bypassing the buffer cache, so the blocks are written by the user process(es) and not by the DBWR process(es).

- By the default every parallel slave of an insert into a non-partitioned segment will use its own temporary segment for loading which will be merged afterwards into one. Note that this can lead to issues when loading smaller amounts of data with large UNIFORM extent sizes (no final extent trimming possible and therefore potentially much larger resulting segments than necessary) or system-managed extent sizes when extent trimming leads to extent sizes that won't be re-used by subsequent loads. Therefore you can optionally use the "brokered load" feature but only with clearance from Oracle Support (see e.g. fix control 6941515 ("use high watermark brokering for insert into single segment") in 11.2)

You can see from above output that the increase in segment size is more than 100% compared to the original segment size - a side-effect of the temp segment merge operation requiring more space for the same amount of data (see comment below by Greg Rahn, exaggerated by my low data volume and extent settings, by the way, you won't see such a significant "wastage" under more realistic conditions).

- As you can see from above output of the test case, direct-path (serial or parallel) loads do not re-use any free space in existing blocks. It will always "append" above the current high-water mark, making the segment grow.

- Another side-effect of this is that it doesn't have to write UNDO for rollback and read-consistency purposes, as for rollback it only needs to release the newly allocated blocks and for read-consistency nothing needs to be done since the high-water mark has not been moved yet and therefore the new blocks are not visible to other sessions. You can see that from the USED_UREC / USED_UBLK output above - no undo has been generated by the parallel DML direct-path insert

- Yet another side-effect of this is that this operation can optionally be a NOLOGGING operation, writing only minimal amount of redo, if the segment has been marked so. Of course you need to be aware of the implications of a NOLOGGING operation.

- Note that if there are usable indexes on the segment defined then the insert will maintain them. This index maintenance will generate undo and redo even if the data segment has been marked as and loaded using NOLOGGING. This holds true, by the way,even if the indexes are also marked as NOLOGGING, since that will only be applicable to index creation and rebuilds, but not index maintenance. If the amount of data loaded into the segment is significant it can be more efficient to set the index segments to unusable before loading the data and rebuild the index segments afterwards.

- You can not access/modify the segment within the same transaction after a serial direct-path or any parallel DML operation, you'll get "ORA-12838: cannot read/modify an object after modifying it in parallel"

- You can not perform parallel DML on an object within the same transaction if serial DML has already been applied to it. You'll get an error "ORA-12839: cannot modify an object in parallel after modifying it". This effectively means within a single transaction a single object can only be modified at most once if it is supposed to be a parallel DML operation

- The session/system statistic "DML statements parallelized" will be increased whenever a DML statement has been run in parallel

If I change now the APPEND hint in the INSERT statement to NOAPPEND I get the following result from 10.2.0.5:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.01
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.66
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.66
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
SQL>
SQL> delete from t1;

50756 rows deleted.

Elapsed: 00:00:01.06
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.03
SQL>
SQL> -- After initial creation and delete
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
697

Elapsed: 00:00:00.03
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

50757 rows created.

Elapsed: 00:00:00.34
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 22 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T2 | 50757 | 4609K| 22 (5)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------

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

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


21 rows selected.

Elapsed: 00:00:00.29
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;

COUNT(*)
----------
50757

Elapsed: 00:00:00.01
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
24 1326 NO

Elapsed: 00:00:00.00
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
SQL>
SQL> -- After insert
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
697

Elapsed: 00:00:00.00
SQL>

So indeed, the description quoted above seems to be correct: The usage of the NOAPPEND hint turned this into a serial, conventional insert as select.

- No LOAD AS SELECT operation, so no direct-path insert

- The query part of the select ran in parallel, but the insert was performed serially.

- Undo has been generated

- The parallel DML statistic hasn't been increased

- The free blocks of the table have been re-used by the insert (no segment growth after the insert)

So, let's repeat the same exercise on 11.1.0.7. The APPEND version will look the same (except for the slightly larger copy of DBA_OBJECTS) and is therefore not shown here, but what about the NOAPPEND version:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.35
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.03
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:01.59
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.59
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:01.06
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50
SQL>
SQL> delete from t1;

69441 rows deleted.

Elapsed: 00:00:01.99
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.04
SQL>
SQL> -- After initial creation and delete
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
1020

Elapsed: 00:00:00.01
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.79
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

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


23 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.03
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 264 YES
7 275 YES
7 293 YES
6 247 YES
6 215 YES
6 253 YES
6 240 YES
6 262 YES

9 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.05
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
SQL>
SQL> -- After insert
SQL> select
2 blocks
3 from
4 user_tables
5 where
6 table_name = 'T1';

BLOCKS
----------
1020

Elapsed: 00:00:00.01
SQL>

Now that is a surprising output. Let's see, we have:

- A new operation in the execution plan, called "LOAD TABLE CONVENTIONAL" (which will also be used for serial conventional insert as selects from 11.1 on)

- A parallel DML operation according to the plan, because the LOAD TABLE CONVENTIONAL is marked as PCWP

- Also the statistics tell us that the DML statement has been run in parallel

- We also get the "ORA-12838: cannot read/modify an object after modifying it in parallel" error message when attempting to access the object after modification indicating a parallel DML operation

- We also see the transactions of the parallel slaves

- But this time these have actually generated UNDO

- And the free space has been re-used

So this is an interesting mixture that obviously has been added in 11.1 - it seems not to be possible to get the same output in pre-11g versions.

Obviously the documentation is not up to date with changes introduced in 11.1 - the NOAPPEND hint now will no longer override the "parallel" DML hint of the insert - that part of the documentation seems to be wrong/outdated.

I got the same result from 11.2.0.1 and 11.2.0.2, by the way.

Update March 2011: Christian Antognini mentioned that this new feature is controlled by the parameter "_disable_parallel_conventional_load".

However, I also found an oddity with this new feature while testing. Look closely at the output of the following script:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> -- drop index t1_idx;
SQL>
SQL> drop table t1;

Table dropped.

Elapsed: 00:00:01.05
SQL>
SQL> drop table t2;

Table dropped.

Elapsed: 00:00:00.02
SQL>
SQL> create table t1
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:00.85
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.98
SQL>
SQL> -- create index t1_idx on t1(object_id);
SQL>
SQL> create table t2
2 as
3 select * from dba_objects;

Table created.

Elapsed: 00:00:01.15
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
SQL>
SQL> delete from t1;

69441 rows deleted.

Elapsed: 00:00:00.75
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.19
SQL>
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable n_parallel_dml_start number
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.29
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

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


23 rows selected.

Elapsed: 00:00:00.34
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> set serveroutput off
SQL>
SQL> -- PX Sessions overview
SQL> select
2 sid
3 , qcsid
4 , server_group
5 , server_set
6 , server#
7 from
8 v$px_session
9 where
10 qcsid = sys_context('userenv', 'sid');

SID QCSID SERVER_GROUP SERVER_SET SERVER#
---------- ---------- ------------ ---------- ----------
132 119 1 1 1
137 119 1 1 2
142 119 1 1 3
143 119 1 1 4
128 119 1 1 5
125 119 1 1 6
130 119 1 1 7
124 119 1 1 8
119 119

9 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.01
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 234 YES
6 259 YES
6 259 YES
6 251 YES
6 227 YES
6 251 YES
7 258 YES
6 268 YES

9 rows selected.

Elapsed: 00:00:00.06
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.80
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

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


23 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> set serveroutput off
SQL>
SQL> -- PX Sessions overview
SQL> select
2 sid
3 , qcsid
4 , server_group
5 , server_set
6 , server#
7 from
8 v$px_session
9 where
10 qcsid = sys_context('userenv', 'sid');

SID QCSID SERVER_GROUP SERVER_SET SERVER#
---------- ---------- ------------ ---------- ----------
132 119 1 1 1
137 119 1 1 2
142 119 1 1 3
143 119 1 1 4
128 119 1 1 5
125 119 1 1 6
130 119 1 1 7
124 119 1 1 8
141 119 2 1 1
135 119 2 1 2
127 119 2 1 3
138 119 2 1 4
116 119 2 1 5
109 119 2 1 6
146 119 2 1 7
144 119 2 1 8
119 119

17 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.00
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 289 YES
6 234 YES
7 284 YES
6 259 YES
6 287 YES
7 280 YES
6 259 YES
6 251 YES
6 227 YES
4 117 YES
3 20 YES
7 308 YES
7 341 YES
6 251 YES
7 258 YES
6 268 YES

17 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> begin
2 select
3 value
4 into :n_parallel_dml_start
5 from
6 v$mystat s
7 , v$statname n
8 where
9 n.name = 'DML statements parallelized'
10 and s.statistic# = n.statistic#;
11 end;
12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;

69442 rows created.

Elapsed: 00:00:00.55
SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zcudgsuwmdvm, child number 0
-------------------------------------
insert /*+ noappend parallel(t1) */ into t1 select /*+ parallel(t2) */
* from t2

Plan hash value: 1216610266

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 39 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD TABLE CONVENTIONAL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T2 | 69442 | 6849K| 39 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

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


23 rows selected.

Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 n_parallel_dml_end number;
3 begin
4 select
5 value
6 into n_parallel_dml_end
7 from
8 v$mystat s
9 , v$statname n
10 where
11 n.name = 'DML statements parallelized'
12 and s.statistic# = n.statistic#;
13 dbms_output.put_line('DML statements parallelized delta: ' || (n_parallel_dml_end - :n_parallel_dml_start));
14 end;
15 /
DML statements parallelized delta: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> set serveroutput off
SQL>
SQL> -- PX Sessions overview
SQL> select
2 sid
3 , qcsid
4 , server_group
5 , server_set
6 , server#
7 from
8 v$px_session
9 where
10 qcsid = sys_context('userenv', 'sid');

SID QCSID SERVER_GROUP SERVER_SET SERVER#
---------- ---------- ------------ ---------- ----------
132 119 1 1 1
137 119 1 1 2
142 119 1 1 3
143 119 1 1 4
128 119 1 1 5
125 119 1 1 6
130 119 1 1 7
124 119 1 1 8
141 119 2 1 1
135 119 2 1 2
127 119 2 1 3
138 119 2 1 4
116 119 2 1 5
109 119 2 1 6
146 119 2 1 7
144 119 2 1 8
145 119 3 1 1
133 119 3 1 2
121 119 3 1 3
110 119 3 1 4
139 119 3 1 5
131 119 3 1 6
115 119 3 1 7
120 119 3 1 8
119 119

25 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> -- If it was direct-path and/or parallel DML you can't access the object within the same transaction
SQL> -- You'll get ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.00
SQL>
SQL> -- Active transaction(s)
SQL> select
2 used_ublk
3 , used_urec
4 , ptx
5 from
6 v$transaction
7 start with
8 xidusn || '.' || xidslot || '.' || xidsqn = dbms_transaction.local_transaction_id
9 connect by
10 case when xid = ptx_xid then null else ptx_xid end = prior xid;

USED_UBLK USED_UREC PTX
---------- ---------- ---
1 1 YES
6 289 YES
6 234 YES
6 243 YES
6 211 YES
6 244 YES
7 284 YES
6 259 YES
6 287 YES
7 280 YES
6 259 YES
6 251 YES
6 241 YES
7 265 YES
6 251 YES
5 226 YES
6 250 YES
6 227 YES
4 117 YES
3 20 YES
7 308 YES
7 341 YES
6 251 YES
7 258 YES
6 268 YES

25 rows selected.

Elapsed: 00:00:00.02
SQL>

All I do is an attempt to repeat the same insert as select statement three times within the same transaction, additionally I show the parallel slave information from V$PX_SESSION.

The expected result was to get simply the "ORA-12838: cannot read/modify an object after modifying it in parallel" error that is indeed thrown when attempting to read from the object after the first parallel DML operation.

Instead, something odd happens: The subsequent insert operations succeed but they don't get marked as "parallel DML" according to the session statistics, and what is even more puzzling: Each execution adds another group of parallel slaves to my parallel execution coordinator.

Note that Oracle also allows other insert operations, like single row insert or other conventional insert as select executions into the same table within the same transaction.

Clearly this doesn't look like intended behaviour, and indeed, when running the same script but with the CREATE INDEX T1_IDX ON T1(OBJECT_ID) command uncommented it behaves as expected and doesn't allow the subsequent manipulation of the object and instead errors out with "ORA-12838: cannot read/modify an object after modifying it in parallel".

I get the same result from 11.2.0.1 and 11.2.0.2, by the way, so this problem is not fixed yet. The only difference in 11.2 is that creating an index on the segment adds another set of parallel slaves that take care of the "INDEX MAINTENANCE" operation that 11.2 shows in the execution plan. In 11.1.0.7 the additional operation does not appear and the same slaves that write in to the segment also take care of the index maintenance.

Summary

Oracle 11g obviously has added the capability to perform a conventional, non-direct-path insert in parallel. I haven't spent too much time yet searching for any official documentation of that new feature, so I can only speculate why it has been added.