Here’s a fuzzy memory of mine: I thought I remembered reading where Tom Kyte once said “all parallel inserts are direct-path inserts.”

I don’t remember where I read it (I can’t even remember if that’s what was said, or if Tom said it).  But today I wanted to see if that was true by playing around with APPEND and PARALLEL hints.

We have two different “places” we can hint.

  • We can hint the insert.
    insert /*+ hint goes here */ into ...
  • We can hint the select.
    select /*+ hint_goes_here */ ...

There are 4 different hints that can be placed in the insert (append, noappend, parallel, no_parallel).

There are 2 different hints that can be placed in the select clause (parallel, no_parallel).

Additionally, you can alter your session 3 different ways  (enable parallel DML, disable parallel DML, force parallel DML).

So the number of possible scenarios we can make with all this mess:

  • 3 possible ways to alter session
  • 3 possible ways to hint the select (parallel, no_parallel, or no hint at all)
  • 8 intelligent ways to hint the insert (no hint, append, parallel, noappend, noparallel, append + parallel, noappend + parallel, noappend + noparallel).
    I specify “intelligent” because /*+ append noappend */  doesn’t make sense; neither does /*+ parallel noparallel */

8 x 3 x 3 = 72 different possible combinations.  I don’t know that it makes sense to try them all, but I’ll highlight some points of interest.

Setup for the experiment :

Database I’m using: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

 

ALTER SESSION DISABLE PARALLEL DML;

All three of these looked identical– the select is being done in parallel, but the insert is not.
All three of these looked identical– nothing is done in parallel.

…no surprise here.  We went from a conventional path insert to a direct path (LOAD AS SELECT) insert.

These three do a direct-path insert, but again, the insert itself is not being done in parallel…just the select.

 

 

ALTER SESSION ENABLE PARALLEL DML;


 
All of these yielded the same explain plan, which surprised me!  I’m so used to seeing insert /*+ append parallel(16) */ into … select /*+ parallel(16) */… but from the looks of it, 2 out of 3 of those hints are unnecessary.  Putting parallel either in the select or the insert clause appears to do the same thing when parallel DML is enabled.

These three appeared to yield the same plan as when we used ALTER SESSION DISABLE PARALLEL DML;

 

No parallelism here.  Again, I was surprised by the second and third one on the list…I might’ve expected parallel inserts and serial selections or serial inserts and parallel selections.

 

You *can* have a parallel insert that’s conventional.

This is proof that either:
a.) Tom Kyte was wrong
b.) My memory was wrong
c.) My memory was right, and Tom Kyte was right when he said it, but it’s since changed.

I’d wager “B” or “C”

 

ALTER SESSION FORCE PARALLEL DML;


 
Here, the optimizer really wants you to use parallel inserts with direct-path loads…

Previously, in the previous experiments, if you had both a parallel hint in the insert and a
no_parallel hint in the select (or vice-versa) Oracle would favor the no_parallel hint,
whereas it looks like here, Oracle favors the parallel hint if both are specified.

 

No real surprises from the other tests.

 

Looks like you can force-disable the parallelism with the no_parallel  hint.

 

You can still have direct path, non-parallelized inserts.

Summary

  • Yes, Virginia, there is a parallel conventional path insert, but it’s not the default.  The only way to get to it looks like hints (or maybe some other hint-ish solution like profiles, etc).
  • If you’re doing a parallel insert, it appears (at least in my experiments) that Oracle won’t let you do a non-parallel select.  Makes sense I guess.
  • On the surface, a parallel hint in both the insert and the select appears to be superfluous  (I’m even running timing tests with the hint on the insert vs. the hint on the select vs the hint on the both, and I’m not noticing a difference).  I’ll potentially do so more digging here.

 


Update:

I just emailed a friend/acquaintance of mine who works for Oracle, asking whether or not having two parallel hints (one in the insert portion, and one in the select portion) is superfluous.

He came back with this response:

Hints on both the insert and the select were used before statement-level hints were introduced. You will see a difference if you try with parallel(my_other_table,16), for example.
parallel(16) is a statement-level hint and parallelizes the whole statement. So, if you are using statement-level hints you do not need two hints.