SQL Loader / Multithreading option not working?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL Loader / Multithreading option not working?

Hybrid View

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    SQL Loader / Multithreading option not working?

    Hello,

    I'm trying to speed up some loads on some rather large flat files that are produced off a mainframe and sent down to a unix box for loading.

    These machines are typically AIX boxes with SMT turned on. Oracle Version is 10g.

    All these loads are using direct path / with truncate option and we are seeing ~36 hours to load one file that is approximately 260 gigs in size. (This data file is fixed width)

    Since these machines are running ~16 CPU's I wanted to test SQL loader with the multithreading=true option.

    I'm invoking these loads from a script:

    sqlldr / control=${CNAME} errors=0 bad=${BADNAME} log=${LOG}_load data=${DATAFILE} direct=true rows=5000 streamsize=1048576 readsize=1048576 columnarrayrows=8000 multithreading=true

    I ran a couple tests (on some much smaller files) but I'm still getting logs that look like:


    Total stream buffers loaded by SQL*Loader main thread: 2245
    Total stream buffers loaded by SQL*Loader load thread: 0

    Why is nothing showing being loaded into the load thread? Is there some option in ora.init / something I am missing here?

    In addition to this I am also looking at trying Parallel Direct Path w/ one or multiple files.

    Any help is appreciated!

    -Kevin
    Last edited by chistik; 02-22-2010 at 03:42 PM.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    As far as I remember multithreading=true is the default value for a multi-processor host computer. I'm also pretty sure rows= parameter is only valid when loading in conventional mode a.k.a. direct=false

    Have you tried "unrecoverable"?

    Fixed lenght data, Direct path, Unrecoverable should give you max performance.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    After switching to parallel, I started seeing this working.

    Here's my sqlldr now:

    sqlldr / control=${CNAME} errors=0 bad=${BADNAME} log=${LOG}_load data=${DATAFILE} direct=true parallel=true multithreading=true skip_index_maintenance=true

    Total stream buffers loaded by SQL*Loader main thread: 206
    Total stream buffers loaded by SQL*Loader load thread: 616

    Is there some correlation between needing to have parallel / multithreading for this to work?

    In addition by adding this option, in preliminary testing, I've loaded some small data sets that were taking ~ 2 mins, to now taking 20 secs using these options. I'm excited about the speed, however in full scale testing i'm nervous on how i/o cpu intensive doing this can be.

    Anybody have any insight?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by chistik View Post
    Is there some correlation between needing to have parallel / multithreading for this to work?
    In a multi-processor computer multithreading defaults to true.

    Quote Originally Posted by chistik View Post
    I'm excited about the speed, however in full scale testing i'm nervous on how i/o cpu intensive doing this can be
    Check topas.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    Now that it seems to be working, I've ran into one small issue. Turning on Parallel=true will cause SQL Loader to not rebuild indexes (hence the skip_index_maintenance=true option to get this to work).

    Does anybody have a good method / script for indentifying all indexes for a particular schema and then rebuilding indexes?

    The only thing I can think of is spooling off some command like:

    select 'alter index ' || index_name || ' rebuild; ' from user_indexes where table_name='MYTABLE';

    That will create my sql list.. but it also spools off the select / spool off commands.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Code:
    spool yourfile.sql
    set time off head off timing off feedback off linesize 180 pagesize 0
    select 'alter index ' || index_name || ' rebuild; ' from user_indexes where table_name='MYTABLE';
    spool off
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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