sql loader performance experiences

Lots of the reporting databases has data loading scripts, using oracle SQL Loader to load mass amount of data from a text file. Loaded data is used for reporting purposes in day time. Commonly reported problem is long running loader scripts and solving this kind of problems has genarally similar approaches.

1- Dropping unnecessary indexes improves the performance of the batch loader operations hence every insert needs to update the indexes after updating the table. Fewer indexes means fewer operation for the DML statements. In my databases i examine v$sql, v$sqlarea, v$sqltext and dba_hist_sqltext views to examine the daily routine of the executed sql statements on the database to find out the index usage percentage of the statements. This gives a brief information about the index usage and to decide if i can drop any.

2- Using nologging option for the tables generates less redo logs. If the database in archive mode then less redo means less work for the archiver. log writer related wait events can also be improved. Using this option causes database not to log the operation and database admin should be cautious about the backup and restore operations. Bacause a recover operation may not recover your nologging tables data. I prefer full and incremental backups with rman in this kind of databases.

3- By setting Direct=true option of the sqlloader direct can be enabled, which loads data directly to datafiles by bypassing most of the database operations.

4- Sometimes table data is deleted periodically. This delete operation fragments the segments of the table and indexes of the table. Moving the tables and the indexes defragments the segments. If the table is fragmented, i prefer dropping the indexes first and move the table and then recreate the indexes again. One of my early postings covers this topic.

There are some other parameters; rows (row size to be commited) and unrecoverable (if used with direct=true option, redo writes are bypassed) parameters for further enhancement for loader performance. But i frequently use the mentioned parameters and they worked more than enough for most cases.