Hypercharge SQL*Loader load speed performance

Is there any limit to the speed of Oracle? With Oracle announcing
a new record one million transactions per minute, many believe that
there is nothing that Oracle cannot do. However, what if we have a requirement for a system that must
accept high-volume data loads into a single table:

500,000 rows per second

50 megabytes per second

Is this possible? Using the right tricks you can make Oracle load
data at unbelievable speed. However, special knowledge and tricks are
required.

Oracle provides us with many choices for online data loading:

SQL insert and merge statements

PL/SQL bulk loads using the forall operator

If we can load in batch mode, we also have more options:

SQL*Loader

Oracle Data Pump

Oracle import utility

However there are vast differences in load speed (Figure 1).

Figure 1: Sample data load speeds

Batch Data Loading

If you are loading your data from flat files there are many
products and Oracle tools to improve your load speed:

Oracle Data load tools:

Oracle10g Data Pump - With Data
Pump Import, a single stream of data load is about 15-45 times
faster than original Import. This is because original Import uses
only conventional mode inserts, whereas Data Pump Import uses the
direct path method of loading.

Don't use standard SQL inserts as they are far slower than other
approaches. If you must use SQL inserts, make sure to use the APPEND
hint to bypass the freelists and raise the high-water mark for the
table. You are way better off using PL/SQL with the bulk insert
features (up to 100x faster).

Other things to ensure:

Use
parallel DML - Parallelize the data loads according to the
number of processors and disk layout. Try to saturate your
processors with parallel processes.

Disable constraints and indexes ? Disable RI during load and
re-enable (in parallel) following the load.

Use
SAME RAID - Avoid RAID5 and use Oracle Stripe and Mirror
Everywhere approach (RAID 0+1, RAID10). However, this doesn't mean
one large array smeared with everything, you will see performance
gains from separating temp, data and index, redo and undo segments
onto separate RAID areas.

Use
a small db_cache_size - This will minimize DBWR work. In
Oracle9i you can use the alter system set db_cache_size command to
temporarily reduce the data buffer cache size.

You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.

��

Burleson is the American Team

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.