Oracle data loading performance

Oracle  choices for data loading

• SQL insert and merge statements
• PL/SQL bulk loads for the forall PL/SQL operator
• SQL*Loader
• Oracle10 Data Pump
• Oracle import utility

TIPS FOR DATA LOADING

1. Use a large blocksize:Data loads onto large  blocksize  (e.x 32k ) will run faster becuse more rows will be written in  an empty block
2. Use a small db_cache_size:If loading with DML a small data cache will minimize DBWR work during async buffer cleanouts.You can reduce  it  with alter  system  temporarily
3. Size your log_buffer properly:If you have waits associated to log_buffer size “db log sync wait”, try increasing to to 10m.
4. Watch your commit frequency:At each commit, Oracle releases locks and undo segments.Benchmarks suggest that you should commit as infrequently as possible
5. Use large undo segments:In order to avoid a ORA-1555 (snapshot too old) error use  large undo segments.
6. Use append hint:If you must use SQL inserts 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.  Note:  Prior to 11g r2, the append hint supports only the subquery syntax of the INSERT statement, not the VALUES clause, where you can use the append_values hint
   
   insert /*+ append */ into customer (select  ....);
7. Use  nologging if possible:NOLOGGING mode (for sql level,table level,database level)  will allow Oracle to avoid almost all redo logging. but the operaions will be unrecoverable
8. Disable archiving if possible:
9. Use PL/SQL bulking:PL/SQL often out-performs standard SQL inserts because of the array processing and bulking in the "forall" statement.   (see also  : PL/SQL forall operator speeds for table inserts )
10. Partition :Load the data in a separate partition, using transportable tablespaces.
11. Use multiple freelists or freelist groups for target tables:Avoid using bitmap freelists ASS management (automatic segment space management) for super high-volume loads.
12. Preallocate  space  for target  tables:Pre allocate  space for  tables and index  with "allocate extent"  clause  in order  to  gain performance on  allocation of segments.
13. Pre-sort the data in index key order:This will make subsequent SQL run far faster for index range scans. 
14. Use parallel DML:Parallelize the data loads according to the number of processors and disk layout.Try to saturate your processors with parallel processes.
15. Disable constraints :Disable during load and re-enable in parallel following the load
16. Disable/drop indexes - 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. If you choose to keep the indexes during inserts, consider creating a reverse key index to minimize insert contention.
17. Disable/drop  triggers if possible:
18. Use RAM Disk:Place undo tablespace and online redo logs on Solid-state disk (RAM SAN),
19. Use SSD RAM Disk:Especially for the insert partition, undo and redo.  You can move the partition to standard disk later.
20. Use SAME RAID:Avoid RAID5 and use Oracle Stripe and Mirror Everywhere approach (RAID 1+0, RAID10).