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.
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.
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
(...
)
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).
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.
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?