How to load data faster in Oracle sqlldr.

Pre-process Tips : Below tips can be very helpful when we are doing the bulk data upload using the data files.

Some of tips can be ignored when we are doing the small amount of data or the destination table is having less records and No of index/foreign keys are less.

 

1)  Initiate the data load(sqlldr ) on the server rather than executing it from desktop. This will make the load much faster.

2) Create Temporary table for load purpose and  load to temp table with sqlldr on parallel and direct option 

2.1) create the temp table for each file

               *:  read header line from data file -- which gives the columns are present in the data file.

              comment :  head 5 filename.csv

             -- copy the column and do the selct in sql plus

           -- copy the same column order into any control file

           -- change the infile , bad filem, discard file to the same location of where ur dat file present

           -- change the table name

          --any timestamp column should be modified like below.

              --NULLIF UPDATEDTTM=BLANKS "to_timestamp(:UPDATEDTTM,'RRRR-MM-DD HH24:MI:SS.FF')", 

create table TEST_LOAD
as
select 
* from TEST where 1=2;

Note : Instead of loading the Data into the destination table , We will suggest to load it to the intermediary table for the large loads.  

       : this will help to increase the performance of multiple file for a same table , so this load can be run parrallel

       : this will help to validate the records before inserting into the actual tables      

2.2) create ctl file for each data file or use the same table for multiple files with append Option.    

2.3) Load Data

open multiple sqlplus session and call the sqlldr.

sqlldr schema/password@connection_string TEST_DATA.ctl ERRORS=100 parallel=true

3) Change destination table to No-logging mode.

             alter table TEST_LOAD NOLOGGING ;

             Note : It bypasses the writing of the redo log, significantly improving performance

4) Force the session to use  parallel DDL/DML.

Parallel session can be calculated by using

http://docs.oracle.com/cd/E18283_01/server.112/e16541/parallel002.htm#CACBDJAF

select * from v$parameter

PARALLEL_DEGREE_POLICY

select * from v$parameter where name like '%cpu%'
select * from v$parameter where name like 'para%'
select * from v$parameter where name like 'para%' or name like '%cpu%'

For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

       ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
       ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

       Note : this will force the DDL/DML operation to run in parallel.

5) Drop All Index and disable foreign Key on the table.

To avoid the tm locks on foreign key and index and child tables. Take the Index creation script before dropping it.

Note : If its data load then the above is enough.

In case of delete and restore ( like colombian template load table vfminstr)

All child table fks which is referring the test table should be disabled
and delete the child table record manually and enable fk after the delete and data load.

 

7)  insert data with append hint

insert /*+ append */ into TEST
select * from TEST_DATA ;

Commit;

Note : this will directly insert the record above High-level water mark.

8) enable constraint / recreate Index which are dropped in step 5

     Note : create missing fk index which makes the constraint enable much faster

     Enable all disabled constraints

     Drop the index which is created temporally for faster constraints enable.

     Re create dropped index

9) alter table to logging mode

10) Drop Temp / external table

11) Do the table analysis