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. Every shop is different, but there are some insert tuning techniques that can work in a multitude of workloads. Remember, you can have choices for doing table inserts including the Data Pump and SQL*Loader utilities, as well as PL/SQL bulk load tools such as the forall operator

As we see below, a standard SQL insert may not be the fastest way to load table data:

As we see above, the fastest way to slam rows into an Oracle table is to use bulk inserts like the PL/SQL forall operator, inserting with super-fast solid-state media. Using these techniques you can insert over 500,000 rows per second.

In the chart above we also see Oracle Utilities like SQL*Loader and Oracle Data Pump, alternative options for inserting rows into Oracle tables.

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

The fastest Oracle table insert rate I've ever seen was 400,000 rows per second, about 24 millions rows per minute, using super-fast RAM disk (SSD), but Greg Rahn of Oracle notes SQL insert rates of upwards of 6 million rows per second using the Exadata firmware:

"One of the faster bulk (parallel nologging direct path from external table using direct path compression) load rates I've seen is just over 7.7 billion rows in under 20 minutes which equates to around 385,000,000 per minute or about 6,416,666 per second.

All the CPUs are running at around 99% user CPU during that load. That was loading to spinning rust (Exadata Storage). It would be even faster had compression not been used. That was on a HP Oracle DB Machine (64 Intel Harpertown CPU cores). "

Speed of inserts is primarily a function of device speed, but NOLOGGING, maximum parallel DML (which, in turn, a function of the number of CPU's and the layout of the disks) also factor into the equation. 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 with nologging - 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. Note: INSERT APPEND supports only the subquery syntax of the INSERT statement, not the VALUES clause. For more information on the subquery syntax of INSERT statements see Oracle nologging tips

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 (select xxx);

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. If you choose to keep the indexes during inserts, consider creating a reverse key index to minimize insert contention.