Saturday, February 26, 2011

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.

15 comments:

  1. "By the default every parallel slave of the operation will use its own temporary segment for loading"

    This is only true for inserts into non-partitioned tables. Partitioned table inserts use HWM brokered loads.

    Also, I think your example uses too few rows in combination with too high of DOP (looks like DOP=8) and as a result you see a large space bloat with PDML inserts. If you used more rows you would see less space bloat. With only ~50k rows/~700 blocks, assuming 8k blocks, thats less than 1MB per slave. Not only that, but your noparallel CTAS is already sub-second, so it's a poor case for Parallel Execution to start with.

    ReplyDelete
  2. Greg,

    thanks for the comment regarding my inaccurate description of the temp segment merge operation and the exaggerated space wastage of my particular demo setup, much appreciated.

    By the way, the point of this post is not about performance of a parallel DML insert but about demonstrating the principle of conventional parallel DML inserts, which seem to be new in Oracle 11g.

    May be you can shed some light on the question why and for what purpose it has been added?

    Randolf

    ReplyDelete
  3. Parallel conventional (NOAPPEND) insert was an 11g new feature, though it seems to have escaped the new features list in the docs.

    It was added to support cases where parallel insert as select was desired, but the restrictions associated with direct path inserts were not desired.

    ReplyDelete
  4. Hello Randolf

    Have you ever noticed that PDML was serialized (no Parallel Insert) when there was one LOB column to be inserted.

    I spent the whole day to try to find why my INSERT was not done in parallel..!!

    So after spotting that, i did a small test and without the LOB my insert is done in parallel.
    With the LOB column it is serialized.

    Good to know..

    ReplyDelete
  5. > Have you ever noticed that PDML was serialized

    Hi JC,

    there are a couple of officially documented restrictions to parallel DML, in particular here and here.

    LOBs are one of those mentioned there for intra-partition parallel DML.

    Randolf

    ReplyDelete
  6. Thanks Randolf, after mentionning this.. I Read The FM .. so it is now clear why I encounter this behaviour.

    In the documentation, they say it is possible to do INSERT or CTAS in parallel for Partitioned Tables, i guess it is for the destination table ? I will test tomorrow..

    By the way, i found a test case about my problem but I am not sure to understand why the NOCACHE was problematic :
    http://oraclequest.wordpress.com/2011/03/02/high-performance-batch-lob-insert/

    http://oraclequest.wordpress.com/2011/03/02/high-performance-batch-lob-insert-part-2-subpartitions/

    I'll leave my result tomorrow..
    Thansk again.
    Christophe

    ReplyDelete
  7. > By the way, i found a test case about my problem but I am not sure to understand why the NOCACHE was problematic

    There are a couple of things to consider:

    1. If you want to scale parallel DML/DDL with LOBs you need to have multiple partitions involved - you can only have a single parallel slave working on each partition (so the parallel granule is partition not block ranges)

    2. According to the post the problem with NOCACHE was not the NOCACHE per se but the NOLOGGING option. By default every NOLOGGING operation required to update the controlfile to set the unrecoverable SCN information.

    You can prevent that using event 10359 or in the latest releases using the DB_UNRECOVERABLE_SCN_TRACKING parameter (but look at bug 12360160)

    3. Even if you consider all that NOCACHE LOBs perform direct writes, so depending on your I/O configuration (async/direct I/O etc.) and I/O subsystem this might still significantly slow down your operation

    4. Also check the potential wastage caused by out-of-line LOBs and the CHUNK/BLOCK SIZE size used

    Randolf

    ReplyDelete
  8. Hi,
    after first example of parallel dml with append hint you say:

    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.

    but v$transaction shows 1 as used_ublk for all the sessions involved.

    Can you explain it to me?
    thanks.

    ReplyDelete
  9. Hi Marco,

    > but v$transaction shows 1 as used_ublk for all the sessions involved.

    if you refer to the initial output shown in the post from 10.2.0.5 with parallel direct-path insert: The CREATE INDEX command was commented out, so when the direct-path insert was performed there was no index to maintain, hence no UNDO was generated.

    The text quoted doesn't correspond to the output - it was merely meant as a heads-up to remind of this fact.

    If you refer to some experiment you've done yourself with indexes enabled you probably would need to show the steps that you've performed.

    Randolf

    ReplyDelete
  10. Hi,

    Good day. Hope you are fine and doing well.

    As per my knowledge parallel write is similar to APPEND i.e Direct path write. And after direct path write if we want to select we should get the ora-12838 error.

    Can you please explain me the below strange behavior of ORA-12838.

    SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into empl EMP
    2 select 2,owner from all_objects where rownum < 30;

    29 rows created.

    Elapsed: 00:00:00.26
    SNEHASISH @ sdat >select * from empl;

    EMPID|ENAME
    ----------|----------------------------------------------------------------
    123|Tuna
    231|Abhimaniu
    342|Chandru
    435|Bibhu
    1|SYS
    1|SYS
    1|PUBLIC
    1|SYS

    SNEHASISH @ sdat > ROLLBACK;

    SNEHASISH @ sdat >insert /*+ PARALLEL(EMP,4) */ into empl EMP
    2 select * from empl;

    46 rows created.

    Elapsed: 00:00:00.01
    SNEHASISH @ sdat >select * from empl;
    select * from empl
    *
    ERROR at line 1:
    ORA-12838: cannot read/modify an object after modifying it in parallel


    Elapsed: 00:00:00.01
    SNEHASISH @ sdat >



    Why is it not giving error in the first scenario.

    Thanks and Regards,
    Snehasish Das

    ReplyDelete
  11. As per my knowledge parallel write is similar to APPEND i.e Direct path write. And after direct path write if we want to select we should get the ora-12838 error.

    Hm, have you read above article? Starting from 11g on a parallel insert doesn't have to be direct path - pointing this out is the whole purpose of the article you've commented on.

    So if you follow above article it looks like your first INSERT was serial, not parallel, as otherwise you should have got the ORA-12838 error, even in case of conventional parallel DML.

    You haven't mentioned a version, nor have you shown what you've did to create your objects / previously in your session, so a reproducible test case would be great to understand why you get the ORA-12838 on second attempt after the rollback.

    At least you could show us the real execution plans (using DBMS_XPLAN.DISPLAY_CURSOR) of those two INSERTs - another possibility is that the usage of ROWNUM in the first query somehow lead to a (partial) serial execution plan.

    Randolf

    ReplyDelete
  12. The same behaviour is still observed in 12.1.0.2 - you can perform multiple inserts with PARALLEL NOAPPEND without getting ORA-12838.

    Another thing to note, although the session statistic 'DML statements parallelized' does not increment for subsequent executions, the statistic 'DML Parallelized' in V$PQ_SESSTAT does increment. This view must be counting its PDML from a different source.


    Dan

    ReplyDelete
  13. Hello, my name is Marek and would like to find out whether it is a way to make PARALLEL MULTI-TABLE insert to use conventional instead of direct-path with NOAPPEND hint?

    I do see it works fine with single table insert, however I can't make do the same for multi-table insert. I would be very grateful for any advice. Here is sample code I used:

    drop table t1;
    drop table t2;

    create table t1 (owner varchar2(50), table_name varchar2(50));
    create table t2 (owner varchar2(50), table_name varchar2(50));

    alter session force parallel dml parallel 8;

    -- multi table insert doesn't seem to run parallel anymore when NOAPPEND hint is added
    insert /*+ parallel(t2 8) noappend */ all
    when owner = 'SYS' then into t1 (owner, table_name) values(owner, table_name)
    when owner= 'SYSTEM' then into t2 (owner, table_name) values(owner, table_name)
    select /*+ parallel(8) */ owner, table_name
    from all_tables;

    -- regular insert insert still inserts in parallel when NOAPPEND hint is added
    insert /*+ parallel(8) noappend */ into t1
    select /*+ parallel(8) */ owner, table_name
    from all_tables
    where owner = 'SYS';

    ReplyDelete
  14. Hi Marek,

    I think you will be out of luck with the multi-table insert. Obviously the "parallel conventional load" code path has only been added to the single table insert, but not the multi-table insert.

    You haven't mentioned a version, but I've checked both 11.2.0.4 and 12.1.0.2, and both show the same behaviour - add the NOAPPEND hint to the multi-table insert and Parallel DML is gone.

    Thanks for pointing this out.

    Note that I've simplified your test case by creating a table T3 as source, in order to rule out that the parallel execution of the ALL_TABLES view has any influence on the result.

    By the way, the multi-table insert comes with other oddities: Its parallel plan when using Parallel DML still uses an unnecessary PX SEND ROUND-ROBIN distribution (something that was already addressed for the single-table insert in 11.2), and in previous versions the optimizer didn't provide an "Outline" hint set for plan stability (seems to be fixed at least in 11.2.0.4 and 12.1.0.2).

    Randolf

    ReplyDelete
  15. Thank you very much for answer and explanation. Marek

    ReplyDelete