Oracle 11G - How to Optimize Slow Parallel Insert Select

Oracle 11g - How to optimize slow parallel insert select?

Try using more bind variables, especially where nested loops might happen. I've noticed that you can use it in cases like

CREATE_DT >= :YOUR_DATE instead of CREATE_DT >= sysdate - 60 

I think this would explain why you have 180 million executions in the lowest part of your execution plan even though the whole other part of the update query is still at 8 million out of your 79 million.

about Oracle parallel insert performance

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.)

Oracle 12 - Improve Insert performance

Happy to receive different approaches however we will be refining our approach (as we already have working solution). Being said that this might not "BEST" way for sure but it addresses our concerns in this specific case.

So the steps we finally followed are;

1. Disable all Constraints on Destination Table (and Child tables if any)
2. Disable all Indexes on Destination Table
3. Increase Cache of Sequences if used
4. Perform INSERT (hint APPEND & NOLOGGING) INTO SELECT (hint PARALLEL)
5. Rebuild all Indexes on Destination Table with PARALLEL NOLOGGING
6. Alter Indexes to change to NOPARALLEL & LOGGING
7. Enable all Constraints using NOVALIDATE (and Child tables if any)
8. ALTER TABLE TABLE_NAME PARALLEL
9. Enable all Constraints using VALIDATE
10. ALTER TABLE TABLE_NAME NOPARALLEL

Above steps are repeated for all tables in a script which starts with

ALTER SESSION ENABLE PARALLEL DDL

Tables: 99

Records: 593,960,688

Time taken: 01:23:44 Hrs

Oracle 11gR2: slow insert performance, then fast insert performance

The difference between the two appears to be primarily due to a difference in physical reads. That implies that when you ran the first INSERT statement, the blocks you needed to SELECT were not in cache and had to be read from disk. When you ran the statement again, the blocks you needed were still in cache so you only had to read them from RAM. Reading from RAM is, obviously, much faster than reading from disk.

That being said, it's hard to imagine that it should really take 76 seconds to read 1000 rows of data (a total of ~16 MB) from disk (file io wait time went from just north of 76 seconds to just north of 1 second and physical read bytes goes from 16 MB to 150 kb). That's a rather insanely slow I/O speed that implies that there is some sort of issue in the underlying disk subsystem.

---

Related Topics