9

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!

Gary
  • 4,495
  • 13
  • 36
  • 49
  • Parallel probably won't help you in this situation anyway. IO is going to be the bottleneck as there's little processing needed (unless you've over simplified). Look at `INSERT /*+APPEND*/ ....` if it's an option – Phil May 02 '12 at 21:20
  • @Phil Parallel execution can significantly improve IO. – Jon Heller May 03 '12 at 04:05

2 Answers2

15

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. (This suggestion to use the append hint assumes you only care about maximum performance. If you can't use direct-path writes, perhaps because you need the table to be immediately recoverable or modifiable during the insert, then you may want to avoid the append hint or even use noappend.)
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • 3
    Worth noting that parallel insert, or direct path serial insert (append), takes an exclusive lock on the table (or the named partitions into which the insert takes place). – David Aldridge May 28 '13 at 19:29
  • Looks like this link suggest not to be use APPEND with parallel, please refer last INSERT example and its explanation. I am not a database expert so I might have understood it other way round, maybe if you could please elaborate in your answer taking in account what is mentioned over here. https://docs.oracle.com/database/121/VLDBG/GUID-2B327BD6-3191-493A-AE5B-EB08325A664C.htm – hagrawal7777 Oct 11 '22 at 06:27
  • @hagrawal I updated my answer with a note about the append hint. Your link is pointing out that there are situations where we don't want to use append. This question is primarily about performance, so I assumed the user would want to use append, but it's possible that they can't use direct-path writes. – Jon Heller Oct 11 '22 at 14:33
  • Thank you, Jon. I know we shouldn't reach out directly, but in case if you have a little time and you can check this question I posted yesterday - https://stackoverflow.com/q/74011000/4691279 – hagrawal7777 Oct 12 '22 at 05:37
1

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

David Aldridge
  • 51,479
  • 8
  • 68
  • 96