4 Replies Latest reply on Sep 24, 2019 5:58 AM by teits

    uploading more than 5M records per minute with SQLLDR

    3987328

      Hi Experts,

       

      Oracle Database 12C Enterprise Edition,

       

      We are capturing SYSLOG with an average 5M records each Minute, and we were trying to upload these flat files by using SQLLDR to a table with Partition per hour, with no index and no sequence,

      The problem now, the SQLLDR is working but not able to upload this amount of data in a timely manner, as the file generated each minute with a 5M records while the SQLLDR takes more than 2 minutes to upload one file  .

      Thus any suggestion please to speed up the SQLLDR uploading ? is there any thing we can do to make this done ?

      please HELP !

       

       

       

      SQL Loader,

       

      sqlldr $user11/$pass11@servername:port/sid control=$j.ctl      multithreading=true     skip_index_maintenance=true    parallel=TRUE    log=$basetablog$j.log   data=$tempsource$j     bad=$basetablog$j.bad

       

       

       

      Control File ,

      OPTIONS(DIRECT=TRUE,PARALLEL=TRUE,BINDSIZE=8000000,ROWS=5000)

      UNRECOVERABLE

      LOAD DATA

      INTO TABLE SYSLOG.SYSLOG_DETAILS

      APPEND

      REENABLE DISABLED_CONSTRAINTS

      EXCEPTIONS SYSLOG.SYSLOG_DETAILS

      FIELDS TERMINATED BY ' '

      TRAILING NULLCOLS

      (SRCIP,

      STARTDATE "to_date('19700101','yyyymmdd')+(1/24/60/60)*(:STARTDATE+10800)",

      SRCPORT,

      DSTIP,

      DSTPORT,

      SESSIONID,

      ACTION,

      POLICYID,

      SERVICE,

      DURATION,

      FILENAME CONSTANT ":FILE",

      SENTBYTE,

      RCVDBYTE)

        • 1. Re: uploading more than 5M records per minute with SQLLDR
          Gaz in Oz

          What is the network latency?

          What does the sqlldr log say about the load?

          As you are doing date conversion then perhaps take a look at "date_cache=n" directive.

          Sql*Loader Command-Line Reference

          • 2. Re: uploading more than 5M records per minute with SQLLDR
            Khalid Ur Rehman OCP 12c DBA

            Dear,

             

            If you upload big transactions, I would like suggest you, upload data via SQLLDR split  / multiple-files data upload.

            I think you know, how to create a split PAR-FILE control file (SQLLDR).

             

            You try to use this solution in test environment and check performance and data uploading time.

             

            Thanks,

            • 3. Re: uploading more than 5M records per minute with SQLLDR
              3987328

              thanks for reply,

              i'm not sure about what you meaning , but let me give you an example , so please check does that what you mean ?

               

               

              #!/bin/ksh
              date
              ## 512KB = <524288>
              ## 1 MB =<1048576>
              ## 2 MB = <2097152>
              ## 4 MB = <4194304>
              ## 8 MB = <8388608>
              sqlldr / control=load_sales.ctl bad=bad_1.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_1.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data01.dbf \
              multithreading=true direct=true parallel=true
              load=2000000 &

                sqlldr / control=load_sales.ctl bad=bad_2.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_2.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data02.dbf \
              multithreading=true direct=true parallel=true

              skip=2000000 load=2000000 &
              sqlldr / control=load_sales.ctl bad=bad_3.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_3.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data03.dbf \
              multithreading=true direct=true parallel=true

              skip=4000000 load=2000000  &
              sqlldr / control=load_sales.ctl bad=bad_4.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_4.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data04.dbf \
              multithreading=true direct=true parallel=true

              skip=6000000 load=2000000 &
              sqlldr / control=load_sales.ctl bad=bad_5.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_5.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data05.dbf \
              multithreading=true direct=true parallel=true

              skip=8000000 load=2000000 &
              sqlldr / control=load_sales.ctl bad=bad_6.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_6.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data06.dbf \
              multithreading=true direct=true parallel=true

              skip=10000000 load=2000000 &
              sqlldr / control=load_sales.ctl bad=bad_7.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_7.log columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data07.dbf \
              multithreading=true direct=true parallel=true

              skip=12000000 load=2000000 &
              sqlldr / control=load_sales.ctl bad=bad_8.dat streamsize=1048576 readsize=1048576 \
              data=sales_data.dat log=load_sales_pll_8.log  columnarrayrows=8000 \
              file=/u01/oratest/sbltest_large_data08.dbf \
              multithreading=true direct=true parallel=true

              skip=14000000 &
              wait
              date
              exit 0

              • 4. Re: uploading more than 5M records per minute with SQLLDR
                teits

                Hi,

                consider using table partitioning and loading data  to each SYSLOG.SYSLOG_DETAILS partitions separately using  similar control.

                ...

                load data

                infile path_to_5M_sys_logXXX

                append into table SYSLOG.SYSLOG_DETAILS partition ($partition_name$)

                ....

                try to partition SYSLOG.SYSLOG_DETAILS in such a way you have one to one mapping between $tempsource$j  and  SYSLOG.SYSLOG_DETAILS partition ($partition_name$)

                 

                this will lessen concurrency issues such as table locks.

                also, you can spawn multiple sqlldr without waiting.

                consider increase ROWS(or use default) with direct=true

                 

                Tobi

                https://tobinotes.blogspot.com/