About Oracle Parallel Insertion Performance

Question

I have sql like this:

Insert into A Select * from B; 

Now I want it to work in parallel. My question is to parallelize the insert, or to select either one and the other? See the following sqls, can you tell me which one is correct, or which has the best performance. I do not have dba permission, so I can not check its execution 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;

Thanks!

Answer 1

Parallelizing both INSERT and SELECT is the fastest.

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

You will definitely want to test it yourself, especially to find the optimal degree of parallelism. There are many myths surrounding the parallel execution of Oracle, and even leadership is sometimes terribly wrong .

In 11gR2, I would recommend you run your expression like this:

 alter session enable parallel dml; insert /*+ append parallel(6) */ into A select * from B; 

• You always want to enable parallel dml.
• parallel(6) uses the parallelism instruction level instead of the parallelism object level. This is an 11gR2 function that allows you to easily run everything in parallel without fear of object aliases or access methods. For 10G you will have to use some tips.
• Usually the append prompt is not required. If your DML works in parallel, it will automatically use direct path inserts. However, if your operator is 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 explanation plan. I believe SELECT_CATALOG is the right privilege.