Dealing with technical questions about #Oracle

During the OCM Preparation Workshop today in Bucharest, we got into a discussion about parallel inserts vs. direct load with append hint, where I said something like: “I think a parallel insert is always* also a direct load.” Most attendees were satisfied with that, but some still looked a bit sceptical. And I was also not 100% sure about it. While the others practiced, I did a brief research and came up with this answer from Tom Kyte and quoted him: “Parallel is always a direct path, if you go parallel, you will be appending.” Everybody in the room was immediately convinced – only I felt a bit uncomfortable with the outcome myself. After all, I don’t want to teach people to blindly follow my or any other guys claims without checking it if possible. And as with so many other claims about Oracle, it is possible to prove (or falsify) it with little effort:

 

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.

SQL> create table t as select 'TEST' as col1 from dual connect by level<=1e6; 
Table created. 

SQL> select bytes/1024/1024 from user_segments;

BYTES/1024/1024
---------------
             13

SQL> delete from t;

1000000 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into t select 'TEST' as col1 from dual connect by level<=1e6; 
1000000 rows created. 

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from user_segments;

BYTES/1024/1024
---------------
             13

The above shows that a conventional insert reuses the empty space before the high water mark of the table and does not allocate additional extents. Now I’m going to do a parallel insert:

SQL> delete from t;

1000000 rows deleted.

SQL> commit;

Commit complete.
SQL> alter table t parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t select 'TEST' as col1 from dual connect by level<=1e6; 
1000000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from user_segments;

BYTES/1024/1024
---------------
         24.375

QED! Obviously, the parallel insert appended rows after the high water mark – it was a direct load, in other words. That is in itself not so spectacular, but that little anecdote illustrates the in my opinion proper way to deal with technical questions about Oracle: Always try to back up your assumptions respectively claims with proof instead of relying only on (your or others) authority.

In other words: Don’t believe it, test it! :-)

*Addendum: Never say always, I always say: If you include the NOAPPEND hint to the parallel insert, it will not be done as a direct load. Thank you guys for pointing that out!

About these ads

  1. #1 by Ivica Arsov on April 2, 2014 - 19:33

    Nice post.

    Direct-path is default in parallel mode.

    From the docs:
    “Direct-path INSERT is the default in parallel mode. In parallel mode, conventional insert can be used only if you specify the NOAPPEND hint.”

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50901

  2. #2 by hourim on April 2, 2014 - 19:57

    Uwe,

    A recent discussion on a similar question raised via an otn thread prompted me to write the following blog article

    http://hourim.wordpress.com/2014/03/14/parallel-insert/

    Best regards
    Mohamed Houri
    http://www.hourim.wordpress.com

  3. #3 by Jure Bratina on April 2, 2014 - 22:58

    Hi Uwe,

    just a thought, another quick way to verify this could probably be a parallel insert (without commit) followed by a select on the same table and if an “ORA-12838: cannot read/modify an object after modifying it in parallel” error is returned it can be implied that direct path was used.

    Regards

  4. #4 by Uwe Hesse on April 3, 2014 - 09:50

    Ivica, thanks for your comment! Another reason why you should never say always – which is what I always say ;-)

  5. #5 by Uwe Hesse on April 3, 2014 - 09:54

    Jure, I do this indeed after a parallel DML to show that it was actually done in parallel – which requires ALTER SESSION ENABLE PARALLEL. On the other hand, this message doesn’t say it was a direct load. And it is even a bit odd, in my opinion, that you get if after a serial direct load also.

  6. #6 by Uwe Hesse on April 3, 2014 - 11:54

    Mohammed Houri, your comment was for some reason captured by the anti-spam mechanism of WordPress, sorry for that. Thank you for pointing to your researches :-)

  7. #7 by Bruno Nascimento on April 3, 2014 - 21:08

    I’m going to Bucharest to get the next OCM Preparation Workshop. My class will start 07/04.

  8. #8 by Uwe Hesse on April 4, 2014 - 09:05

    Bruno Nascimento, I will deliver that one too – so see you next week :-)

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

Follow

Get every new post delivered to your Inbox.

Join 2,802 other followers

%d bloggers like this: