0 Replies Latest reply: Sep 29, 2013 11:29 PM by user13294259 RSS

    SQL Loader Parallel Mode

    user13294259

      Hi,

           I have similar issue where i have requirement to load 270 million record per day into single table(having No constraints & Indexes), where every CTL file contain 37000 records.

       

      I have machine having 16 CPU and 2 thread per CPU is set.I am using the PARALLEL=TRUE, MULTITHREADING=TRUE,DIRECT=TRUE option in sql loader.

      E.g:- OPTIONS(  ERRORS=100000, SILENT=all, MULTITHREADING=TRUE, DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=TRUE,streamsize=1048576, readsize=1048576, columnarrayrows=8000 )

       

      Also enable the PARALLEL degree  and set the value to 32.

       

      When i am running sqlloader 4 session with above configuration it was taking total 4-5 seconds to load 4 CTL files having 37000 records per file. Well for initial 50 million records sql loader behave normally to load the CTL files in 4-5 seconds, but after 50 million records in table, time taken to process the 4 CTL files was gradually increasing to 40 to 70 seconds  and it was still increasing as the number of records more and more in table.

       

      I don't know why sqlloader behave like this after 50 million record in table.

       

      Below is the parallel parameter set on the machine

       

      SQL> show parameter parallel;

       

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      fast_start_parallel_rollback         string      LOW

      parallel_adaptive_multi_user         boolean     TRUE

      parallel_automatic_tuning            boolean     FALSE

      parallel_degree_limit                string      CPU

      parallel_degree_policy               string      MANUAL

      parallel_execution_message_size      integer     16384

      parallel_force_local                 boolean     FALSE

      parallel_instance_group              string

      parallel_io_cap_enabled              boolean     FALSE

      parallel_max_servers                 integer     80

      parallel_min_percent                 integer     0

       

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      parallel_min_servers                 integer     0

      parallel_min_time_threshold          string      AUTO

      parallel_server                      boolean     FALSE

      parallel_server_instances            integer     1

      parallel_servers_target              integer     32

      parallel_threads_per_cpu             integer     2

      recovery_parallelism                 integer     0

       

       

      Kindly repl on the above query