Author: Don Burleson

Page: 1 2

Sometimes I wonder if there is any limit to the speed of Oracle. With the passage of time Oracle has brought something new or updated to ease the life of database administrators.  Hearing the record transactions of one million per minute makes one believe that there is nothing that Oracle cannot do. However there are some concerns that should be kept in mind for speeding up oracle data loading.

Can Oracle meet your requirements?

Suppose we have a requirement for a system that must accept high-volume data loads into a single table: Say 500,000 rows per second or 50 megabytes per second. Is this possible? Using the right tricks you can make Oracle load data at unbelievable speed. 

Oracle Data Loading:

Oracle provides us with many choices for data loading. These include SQL insert and merge statements, PL/SQL bulk loads for the forall PL/SQL operator, SQL*Loader, Oracle10 Data Pump and Oracle import utility.

Oracle tools to speed up Oracle Data Loading:

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

Oracle10g Data Pump:

• Oracle 10g Data Pump Import about 15-45 times faster single stream of data load than the original Import.
• Data Pump Import uses the direct path method of loading. 

Oracle SQL*Loader:

• Oracle SQL*Loader provides direct-path loads, unrecoverable, etc and get super-fast loads.   

Oracle import Utility:

• Oracle import utility provides numerous options to improve the data load speed.

Oracle BMC:

• Oracle BMC is claimed to be 2 to 5x faster than Oracle import utility.

CoSORT FAst extraCT (FACT) for Oracle:

• FACT claims to get Bulk loads up to 90% faster when CoSORT pre-sorts the load file on the table's index key. 
• FACT also improves clustering_factor and improves run-time SQL access speeds by reducing logical I/O.

Standard SQL Inserts:

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

Partition:

• Load the data in a separate partition, using transportable tablespaces.

SSD RAM Disk:

• Use SSD RAM Disk  for the insert partition, undo and redo. 
• You can move the partition to standard disk later.

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Some Exciting Oracle Advisory Utilities you should not miss!!
   DBA Tips: Handling Recordsets in Oracle Procedures!!
   Common Oracle Recovery mistakes
   Quick & Efficient Switchover in a RAC Environment!!