Author: Burleson

In this article I will discuss the techniques for enhancing performance of oracle data load.

Oracle Import Tuning:

There is no direct tuning option available for imports and hence it has more tuning limitations than other utilities. The DBA should consider the following when trying to optimize import performance

• Set analyze=n and analyze with dbms_stats after the load has completed.


• Many large companies use partitioned tables, and keep the current partition on SSD for fast imports.


• The recordlength needs to be a multiple of your I/O chunk size and db_block_size (or dbnnk_block_size).


• commit=n should be set for tables that can afford not to commit until the end of the load. However larger tables may not be suitable for this option due to the required rollback/undo space.


• A single large rollback segment can be created that take all others offline during the import.


• Index creation can be postponed until after import completes, by specifying indexes=n .  Setting indexes=n eliminates this maintenance overhead.   You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.


• By using a larger buffer setting, import can do more work before disk access is performed. 


• You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.

Oracle Exact Performance Gains:

Exact performance gain can be achieved depending upon the following factors

• DB_BLOCK_SIZE


• The types of columns in your table


• The I/O layout

Oracle SQL*Loader Tuning:

Oracle SQL*Loader  is flexible and offers many options that should be considered to maximize the speed of data loads.  These include:

• The direct path loader ( direct=true ) loads directly into the Oracle data files and creates blocks in Oracle database block format.  To prepare the database for direct path loads, the script $ORACLE_HOME/rdbms/admin/catldr.sql .sql must be executed.


• Disabling of indexes and constraints for conventional data loads can greatly enhance the performance of SQL*Loader.  


• Larger bind arrays limit the number of calls to the database and increase performance for conventional data loads only. 


• rows specifies the number of rows per commit.  If you issue fewer commits for rows then it will enhance performance for conventional data loads. 


• Parallel Loads option allows multiple SQL*Loader jobs to execute concurrently.


• Fixed width data format saves Oracle some processing when parsing the data. 


• Disabling Archiving During Load option may not be feasible in certain environments; however disabling database archiving can increase performance considerably.


• The unrecoverable load data option disables the writing of the data to the redo logs.  This option is available for direct path loads only.

Oracle INSERT performance Tuning:

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

• Manage segment header contention for parallel inserts.


• Make sure to define multiple freelist (or freelist groups) to remove contention for the table header.


• You can invoke parallel DML to have multiple inserts into the same table.


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


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


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


• For databases that require high-speed loads, some shops define the insert table partition on solid-state disk later moving it to platter disk. If the Oracle level processing for insert and update activities could be optimized for SSD, significant performance gains might be realized during these activities.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Secrets of Truly Dropping a Table in Oracle 10g!!
   Oracle Recovery Manager - RMAN
   Great Tips for Removing Jobs from Job Queue Automatically!!
   Great Tips on Recovering Controlfile without Resetlogs!