about Oracle parallel insert performance

I have an sql like this:

Insert into A
Select * from B;

Now I want it to run in parallel. My question is to parallelize the insert or select or both? See the following sqls, can you tell me which one is correct or which one has best performance. I don't have dba permission, so I cann't check its execute plan.

1) Insert /*+ parallel(A 6) */ into A select * from B;

2) Insert into A select/*+ parallel(B 6) */ * from B;

3) Insert /*+ parallel(A 6) */ into A select /*+ parallel(B 6) */ * from B;

Thank you!

Answers


Parallelizing both the INSERT and the SELECT is the fastest.

(If you have a large enough amount of data, you have a decent server, everything is configured sanely, etc.)

You'll definitely want to test it yourself, especially to find the optimal degree of parallelism. There are a lot of myths surrounding Oracle parallel execution, and even the manual is sometimes horribly wrong.

On 11gR2, I would recommend you run your statement like this:

alter session enable parallel dml;
insert /*+ append parallel(6) */ into A select * from B;
  1. You always want to enable parallel dml first.
  2. parallel(6) uses statement-level parallelism, instead of object-level parallelism. This is an 11gR2 feature that allows you to easily run everything in parallel witout having to worry about object aliases or access methods. For 10G you'll have to use multiple hints.
  3. Normally the append hint isn't necessary. If your DML runs in parallel, it will automatically use direct-path inserts. However, if your statement gets downgraded to serial, for example if there are no parallel servers available, then the append hint can make a big difference.

Need Your Help

Catching application crash events

vb.net winforms crash crash-reports

I made a application in VB.Net. But some users face crash upon startup. That is "A problem caused this program from working correctly" with just one button "Close the program". Since there are lot of

Else If Does Not Run Correctly on PHP

php

I have very simple PHP script, but it make me confuses. This is my script