SQL Loader - Parallel Load with Muiltiple input files
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SQL Loader - Parallel Load with Muiltiple input files

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    SQL Loader - Parallel Load with Muiltiple input files

    Hi All,

    I have an SQL Loader job which loads data into my target table using direct path (Control file has 100 input files).

    Now, I am planning to ADD Parallel Load option to that.

    I will have 100 input files. How the PARALLEL option work here?

    Will it try to load all the files PARALLELY or will load file by file PARALLELY?

    Thanks
    Cheers!
    OraKid.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by balajiyes
    Hi All,

    I have an SQL Loader job which loads data into my target table using direct path (Control file has 100 input files).

    Now, I am planning to ADD Parallel Load option to that.

    I will have 100 input files. How the PARALLEL option work here?

    Will it try to load all the files PARALLELY or will load file by file PARALLELY?

    Thanks
    Group 100 files into 10 files (concatenate 10 files into 1) , run 10 loader processes for 10 files in parallel.

    Tamil

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I believe that the PARALLEL option just modifies the way that the data is inserted to the table. Instead of being inserted above the high water mark in space already allocated to the data segment, a new extent is created. This allows multiple SQL*Loader "parallel" sessions to be run at the same time, each allocating its own extents. So you actually would run multiple SQL*Loader sessions, and the PARALLEL option is just an enabling mechanism for this. If you have a lot of free space above the HWM then this is less space efficient of course.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi,

    This is my SQL Loader parameter.

    sqlldr control=JJ_EDWLoadCDR.ctl direct=true log=JJ_EDWLoadCDR_BIND_READSIZE_ARRAY.log READSIZE=20000000 COLUMNARRAYROWS=5000

    This is my LOG...

    Date conversion cache disabled due to overflow (default size: 1000)

    Bind array size not used in direct path.
    Column array rows : 5000
    Stream buffer bytes: 256000
    Read buffer bytes:20000000

    Total logical records skipped: 0
    Total logical records read: 77829
    Total logical records rejected: 0
    Total logical records discarded: 0
    Total stream buffers loaded by SQL*Loader main thread: 16
    Total stream buffers loaded by SQL*Loader load thread: 124

    Run began on Tue Nov 15 18:31:48 2005
    Run ended on Tue Nov 15 18:32:31 2005

    Elapsed time was: 00:00:43.13
    CPU time was: 00:00:36.93Date conversion cache disabled due to overflow (default size: 1000)

    Bind array size not used in direct path.
    Column array rows : 5000
    Stream buffer bytes: 256000
    Read buffer bytes:20000000

    Total logical records skipped: 0
    Total logical records read: 77829
    Total logical records rejected: 0
    Total logical records discarded: 0
    Total stream buffers loaded by SQL*Loader main thread: 16
    Total stream buffers loaded by SQL*Loader load thread: 124

    Run began on Tue Nov 15 18:31:48 2005
    Run ended on Tue Nov 15 18:32:31 2005

    Elapsed time was: 00:00:43.13
    CPU time was: 00:00:36.93

    I want this loader job to complete under 20 seconds.

    Which is the best approach?

    Please guide.

    Thanks,
    Jegan
    Cheers!
    OraKid.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Where is the control file?

    Tamil

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi Tamil,

    I have 300 columns.

    Please find the attached ctl file.

    LOAD DATA
    INFILE "/var/opt/ascential/data/jj/CDRNOK_MSCHEMCHU1_20051106010701_2547.dat.bman.1219332"
    APPEND
    INTO TABLE CDR_TMPJJ
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (...
    )


    Thanks
    Attached Files Attached Files
    Cheers!
    OraKid.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think READSIZE=20000000 is killing the performance.

    For direct load option, I use the following three parameters carefully.
    streamsize=1048576 readsize=1048576 columnarrayrows=8000

    Those 3 values are good for my system. They may not be good for your environment.

    You need to find out after experimenting with many values.

    But one thing you must remember:
    You must ensure that the loader process seemlessly reads records and writes them into table blocks. Since, the number of columns are more than 300, I recommend to a low value for columnarrayrows, (may be 1000).

    When you are in doubt, decrease the values.

    Tamil

  8. #8
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    The parallel option is documented here.

  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thank You guys.

    Tamilselvan: "For direct load option, I use the following three parameters carefully.
    streamsize=1048576 readsize=1048576 columnarrayrows=8000"
    How to decide which is the best option?

    William: I did raise parallel mode, but our DBAs don't want to tamper the current applications.

    I dont hv any other option other than tunning this job in DIRECT MODE.

    Cheers
    Cheers!
    OraKid.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by balajiyes
    William: I did raise parallel mode, but our DBAs don't want to tamper the current applications.
    They'd have to come up with a better reason than that for me. Under what conditions would they be prepared to "tamper" ... how about if you could load the data five times faster?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width