Increasing DML speed and throughput

Posted by David Yahalom under Oracle

1. Parallelism of the insert operation - invoke parallel DML (using the “PARALLEL” hint) to perform concurrent inserts on the same table.

Be advised that in order to avoid contention on the segment header, multiple freelists are required, or using ASSM which generates bitmap-based freelists.

In addition to INSERT, the PARALLEL hint also applies to - SELECT, MERGE, UPDATE, and DELETE statements.

2. Use the “APPEND” hint - causes the optimizer to use direct-path insert.

• Conventional INSERT is the default in serial mode. In serial mode, direct path can be used only if you include the APPEND hint.

• Direct-path INSERT is the default in parallel mode. In parallel mode, conventional insert can be used only if you specify the NOAPPEND hint.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT. Basically this tells Oracle to “grab” free blocks for the inserted data ABOVE the table HWM.

A major benefit of direct-load INSERT is that you can load data without logging redo or undo entries, which improves the insert performance significantly.

3. Use a larger block size - By using larger block sizes (such as 16K or 32K) you can reduce the amount of I/O as more rows fit into a single block before a “full block” condition is reached and causes the block to get unlisted from the Freelist (which is a latch that during multiple concurrent inserts can slow the database).

4. Disable indexes - It’s faster, considerably, to rebuild indexes after a bulk-data load all at once compared to having the indexes update after each insert statement.

5. Use Reverse key indexes - can help reduce insert contention on sequence-generated primary keys as data will spread across multiple blocks.