Auto DOP And Direct-Path Inserts

This is just a short note about one of the potential side-effects of the new Auto Degree Of Parallelism (DOP) feature introduced in 11.2.

If you happen to have Parallel DML enabled in your session along with Auto DOP (and here I refer to the PARALLEL_DEGREE_POLICY = AUTO setting, not LIMITED) then it might take you by surprise that INSERT statements that are neither decorated with a parallel hint nor use any parallel enabled objects can be turned into direct-path inserts.

Now don't get me wrong - I think this is reasonable and in-line with the behaviour so far because you have enabled parallel DML and Auto DOP therefore is eligible to make use of that feature. According to the documentation the default mode of parallel inserts is direct-path, so Auto DOP simply follows the documented behaviour when deciding to use parallel DML. Note that depending on the data volume to insert you can even end up with a serial direct-path insert combined with a parallel query part.

It is just that you need to be aware of the fact that a simple INSERT INTO ... SELECT FROM on serial objects might turn into a direct-path insert.

The main caveat to watch out for is that the direct-path insert won't re-use any space available in the existing blocks of the segment but will always allocate blocks above the current High Water Mark (HWM).

So if you use this feature along with some application logic that deletes rows from a segment then by enabling Auto DOP you might end up with an unreasonable segment growth that can have all kinds of nasty side effects.

Another side effect of this is more obvious: An existing application logic might break because it attempts to re-access the object after the now direct-path insert within the the same transaction which will end up with an "ORA-12838: cannot read/modify an object after modifying it in parallel".

If you still want to make use of parallel DML but need to be able to re-use available space in existing blocks you can try to explicitly specify the NOAPPEND hint that still allows parallel AUTO to be used but will prevent the direct-path insert mode for both serial and parallel inserts - 11g introduced the parallel conventional insert, by the way.

Here is a small test case to demonstrate the behaviour:

set echo on linesize 200 pagesize 0 trimspool on tab off

drop table t;

purge table t;

create table t
as
select
rownum as id
, rpad('x', 100) as vc1
from
dual
connect by level <= 1000000
;

exec dbms_stats.gather_table_stats(null, 'T')

alter session enable parallel dml;

alter session set parallel_degree_policy = manual;

insert into t select * from t where rownum <= 1000;

select * from table(dbms_xplan.display_cursor(null, null));

select count(*) from t;

commit;

alter session set parallel_degree_policy = auto;

insert into t select * from t where rownum <= 1000;

select * from table(dbms_xplan.display_cursor(null, null));

select count(*) from t;

commit;

insert /*+ noappend */ into t select * from t where rownum <= 1000;

select * from table(dbms_xplan.display_cursor(null, null));

select count(*) from t;

commit;

And here is the output I get from 11.2.0.1:

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

SQL>
SQL> drop table t;

Table dropped.

SQL>
SQL> purge table t;

Table purged.

SQL>
SQL> create table t
2 as
3 select
4 rownum as id
5 , rpad('x', 100) as vc1
6 from
7 dual
8 connect by level <= 1000000
9 ;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'T')

PL/SQL procedure successfully completed.

SQL>
SQL> alter session enable parallel dml;

Session altered.

SQL>
SQL> alter session set parallel_degree_policy = manual;

Session altered.

SQL>
SQL> insert into t select * from t where rownum <= 1000;

1000 rows created.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));
SQL_ID 95x60r5k6mhka, child number 0
-------------------------------------
insert into t select * from t where rownum <= 1000

Plan hash value: 508354683

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4200 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL | T | 1000K| 101M| 4200 (1)| 00:00:51 |
---------------------------------------------------------------------------------

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

2 - filter(ROWNUM<=1000)


20 rows selected.

SQL>
SQL> select count(*) from t;
1001000

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter session set parallel_degree_policy = auto;

Session altered.

SQL>
SQL> insert into t select * from t where rownum <= 1000;

1000 rows created.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));
SQL_ID 95x60r5k6mhka, child number 2
-------------------------------------
insert into t select * from t where rownum <= 1000

Plan hash value: 482288532

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 2326 (100)| | | | |
| 1 | LOAD AS SELECT | | | | | | | | |
|* 2 | COUNT STOPKEY | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1000K| 101M| 2326 (1)| 00:00:28 | Q1,00 | P->S | QC (RAND) |
|* 5 | COUNT STOPKEY | | | | | | Q1,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 1000K| 101M| 2326 (1)| 00:00:28 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL| T | 1000K| 101M| 2326 (1)| 00:00:28 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------

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

2 - filter(ROWNUM<=1000)
5 - filter(ROWNUM<=1000)
7 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


30 rows selected.

SQL>
SQL> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert /*+ noappend */ into t select * from t where rownum <= 1000;

1000 rows created.

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null));
SQL_ID dv79tpggm6q4k, child number 1
-------------------------------------
insert /*+ noappend */ into t select * from t where rownum <= 1000

Plan hash value: 2717876046

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 2326 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
|* 2 | COUNT STOPKEY | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 1000K| 101M| 2326 (1)| 00:00:28 | Q1,00 | P->S | QC (RAND) |
|* 5 | COUNT STOPKEY | | | | | | Q1,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 1000K| 101M| 2326 (1)| 00:00:28 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | T | 1000K| 101M| 2326 (1)| 00:00:28 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

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

2 - filter(ROWNUM<=1000)
5 - filter(ROWNUM<=1000)
7 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


30 rows selected.

SQL>
SQL> select count(*) from t;
1003000

SQL>
SQL> commit;

Commit complete.

SQL>

Notice how the insert turns into a direct-path insert with Auto DOP and how the subsequent query fails.

As already mentioned, the automatic conversion to direct-path insert with Auto DOP can only been seen when Parallel DML is enabled in the session.