about Oracle parallel insert performance

Question

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!

Answer 1

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.

Answer 2

You do not need DBA privileges to run an explain plan. I believe that SELECT_CATALOG is the correct privilege.