SQL Loader / Multithreading option not working?

[chistik]

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

[PAVB]

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.

[chistik]

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?

[PAVB]

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

In a multi-processor computer multithreading defaults to true.

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.

[chistik]

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.

[PAVB]

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