|
 |
|
Optimize Oracle SQL INSERT performance
Don Burleson
Updated 1 September 2010
|
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: Prior to 11g r2, the append
hint supports only the
subquery syntax of the INSERT statement, not the VALUES clause, where
you can use the append_values hint. For
more information on the subquery syntax of INSERT statements see
Oracle nologging tips.
Important note: Staring in Oracle 11g release 2, we see
the new
append_values hint., which is 1./3 faster than a vanilla
forall bulk operator!
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.
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
My related notes on insert tuning:
|
|
