Mohamed Houri’s Oracle Notes

March 14, 2014

parallel insert

Filed under: direct path — hourim @ 2:28 pm

Do you know that a direct path insert can be silently ignored when the inserted table has an insert trigger?

SQL> create table t1 as select rownum n1 from dual connect by level <=1e5;

SQL> create table t2 as select * from t1 where 0 = 1;

SQL> create or replace trigger t2_trg
      before insert on t2
      for each row
    begin
       null;
    end;
   /

SQL> insert /*+ append */ into t2 select * from t1;

100000 rows created.

SQL> select count(1) from t2;

COUNT(1)
---------
100000

SQL> alter trigger t2_trg disable;

SQL> insert /*+ append */ into t2 select * from t1;

100000 rows created.

SQL> select count(1) from t2;

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

It is only when the trigger has been disabled that the insert has been done via a direct path load.

But are you aware that a parallel insert can also be silently ignored in presence of an insert trigger?

SQL> alter session enable parallel dml;

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

100000 rows created.

SQL_ID  5npb49pus3wtr, child number 0
-------------------------------------
insert /*+ parallel(t2) */ into t2 select * from t1

Plan hash value: 2315600204
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          |       |       |    47 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       |          |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 |   100K|  1269K|    47   (7)| 00:00:01 |        | S->P | RND-ROBIN  |
|   6 |       TABLE ACCESS FULL | T1       |   100K|  1269K|    47   (7)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> select count(1) from t2;

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

SQL> rollback;

SQL> alter trigger t2_trg enable;

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

100000 rows created.

SQL_ID  5npb49pus3wtr, child number 0
-------------------------------------
insert /*+ parallel(t2) */ into t2 select * from t1

Plan hash value: 3617692013
---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |       |    47 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T1   |   100K|  1269K|    47   (7)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> select count(1) from t2;

COUNT(1)
----------
300000

When the trigger has been disabled the insert went through a parallel load path in contrast to when the same trigger has been enabled where the insert went through to a classical conventional load.

A classical conventional load have I said?

Yes. If you look closely to the case where the parallel insert has been honored you will realize that not only the insert run in parallel but it has also been done via a direct path mode.

Tell me: does this means that a parallel insert (when honored) is always accomplished via a direct path load?

Yes it is.

So is the append hint in the following instruction redundant?


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

No it is not always redundant.

Simply because a parallel run and a direct path load share several common “impeachment” reasons like the presence of insert triggers or foreign integrity constraints((yes I have also tested this case also)  but they also have their proper source of impeachment. While the direct path is not a CBO decision, the parallel run is. So, what do you think will happen when the CBO decides that the parallel insert is not the best path to follow and you have already get rid of the append hint from your insert statement? The direct path, when possible, will not be followed.

If I simulate a parallel run impeachment by disabling the parallel dml and get rid of the append hint from my insert statement then the direct path load will not happen

SQL> alter session disable parallel dml;

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

100000 rows created.

SQL> select count(1) from t2;

COUNT(1)
----------
100000

However if, in the same situation,  I will add the append hint to my insert statement then the insert will follow a direct path load path as demonstrated below:

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

100000 rows created.

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

This is the otn thread that prompted this blog post

Bottom line: I learnt yesterday that a parallel insert when followed by the CBO will use a direct path load which is not always the best solution. I learnt also in this case that a parallel insert suffers from the same impeachment raisons as the direct path load does: presence of insert triggers and foreign integrity constraint. And I learnt that to secure your direct path load add the append hint  to your insert statement even when this statement contains a parallel hint

2 Comments »

  1. Hello,
    It’s not just the triggers. The direct path insert have its own limitations:
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#SQLRF55049
    check: Direct-path INSERT is subject to a number of restrictions…

    So if we have IOT,cluster,trigger,FK and so on we are using conventional insert instead🙂

    Comment by Nikolay Kovachev — April 3, 2014 @ 5:54 pm | Reply

  2. Nikolay,

    I have mentioned trigger and Foreign Key because they are the most likely feature to be met when inserting into a table. The other reasons you’ve mentioned of course are limiting the direct path load.and to be honest I have never tested them.

    Best regards

    Comment by hourim — April 3, 2014 @ 6:43 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: