Optimize Oracle INSERT performance Don Burleson

When loading large-volumes of data, you have several choices of tools, each with their own costs and performance benefits.  A standard SQL insert may not be the fastest way to load table data:

While my complete notes are found in my book "Oracle Tuning: The Definitive Reference", I have my main notes on tuning inserts here, but here are some general guidelines

1 - Tune SQL Insert DML - Parallelized insert programs, each doing concurrent INSERT statements (with enough freelists), can speed up insert performance.

2 - Tune SQL*Loader - Using sqlldr Direct Load, and adjusting parameters improves INSERT performance.

3 - Tune imports - Use Oracle Data Pump (Formally Oracle import utility) - Here are tips for hypercharging Oracle import.

4 - Use PL/SQL bulking - PL/SQL often out-performs standard SQL inserts because of the array processing and bulking in the "forall" statement.  Kent Crotty shows examples where forall provides a 30x performance improvement on inserts, making PL/SQL as fast as SQL*Loader, one of the the fastest ways to load Oracle data.

5 - Use solid-state media - Oracle with SSD can support over 500,000 rows per second for inserts, making it a great solution for shops that must "drink from the garden hose" during ETL insert feeds.

Optimizing Oracle INSERT performance

When using standard SQL statements to load Oracle data tables, there are several tuning approaches:

a - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck.  You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.

b - Parallelize the load - You can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL "APPEND" option.  Mark Bobak notes that if you submit parallel jobs to insert against the table at the same time, using the APPEND hint may cause serialization, removing the benefit of parallel jobstreams.
 

c - APPEND into tables - By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint.  Mark Bobak notes "Also, if you're going w/ APPEND, consider putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging."

insert /*+ append */ into customer values ('hello',';there');
 

d - Use a large blocksize - By defining large (i.e. 32k) blocksizes for the target table, you reduce I/O because more rows fit onto a block before a "block full" condition (as set by PCTFREE) unlinks the block from the freelist.
 

e - Disable/drop indexes - It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

.

My related notes on insert tuning:

Need Oracle training? Get Oracle training from a practicing Oracle expert Get custom training designed to fit your needs Conveniently offered at your workplace, anywhere in the USA BC Oracle training offers some of the USA's most respected Oracle experts and authors.  Why spend thousands on cookie cutter Oracle classes when you can have the personalized attention of a real Oracle guru? Just call now: