Insider tricks for improving Oracle data load speed
 

These notes document techniques for improving the speed of Oracle import (imp or impdp) and Oracle SQL*Loader (sqlldr) jobs.  Also see my notes on tuning and improving Oracle export (expdp) performance speed.

For professionals only

Of all of the performance techniques for database loading, the most important is the nologging operation, and the undocumented parameter _disable_logging=TRUE

Note this discussion where Oracle expert Mark Bobak notes that you cannot re-start a failed instance when using _disable_logging=true, and Don Burleson notes that _disable_logging should only be used after taking a full, recoverable backup:

"The main things to consider about NOLOGGING:

- Supported feature of Oracle
- Works only with direct load insert
- Still maintains minimal logging, i.e., space transactions, extent invalidation records for loaded extents, etc.

Contrast with _disable_logging=TRUE:

- Undocumented, unsupported parameter
- all redo writes to log buffer still happen
- writes of redo buffer down to disk are disabled.
- Not limited to direct load insert. All operations are no longer protected by redo on disk.

The big difference is, if your instance dies, (crash or shutdown abort) there's no data in the online redo logs to even do instance recovery. This is very bad. Your database could suffer a corruption from which you can't recover."

All experienced DBA's know that You MUST get approval from Oracle Technical Support for all usage of undocumented parameters, but in the hands of a well-trained DBA, the hidden parameters are often a Godsend, especially for speeding-up rebuilds during tight maintenance windows with limited downtime.

Read more about improving Oracle data load speed here:

http://dba-oracle.com/oracle_tips_load_speed.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf