Azhar,
I agree with the other responses (more RAM, don't use OPTIMAL on your
RBS's) but would also suggest dropping all indexes except PK until the load
is done, the rebuild them. Also, does this table have any
triggers? If so, if can you disable them during the load, that will help
speed up the load.
For help in setting the ROWS and BINDSIZE parameters, there is an article
of mine that O'Reilly published on their web site (<A
href="http://oracle.oreilly.com/news/oraclesqlload_0401.html">http://oracle.oreilly.com/news/oraclesqlload_0401.html)
which details my experience which resulted in some big performance gains.
HTH,
Stephen>>> azhar_at_mathtech-pk.com 05/30/01 02:19AM
>>>HI ALL,We have to load almost 3 millions records of
average row size of 150 bytes.We are importing data using sqloader with
ROWS=4000 and bindsize=8450000 .We have adjusted the rollback segment to
almost 10 m with 8 extents enoughfor single transaction size and
considering 30% rollback overhead. Weadjusted the OPTIMAL TO 10 M to have
avoid rollback extensionRollback segment, databuffer cache have hit ratio of
100%.The loading was fast only for first 10 commits but then it slowed
likesnail. LOADING TOOK 22 hours in the first run on ORACLE8i NT4 128 megs
RAM.SGA figures in M
:NAME
VALUE-------------------- ---------Fixed
Size
.0676384Variable Size
239.02734Database Buffers
39.0625Redo Buffers
7.8203125
---------sum
285.97779( we can't use direct path due to functions in sqlldr controlfile).
.Couldn't figure out the bottleneck yet.Any ideas.TIAAzhar
Siddiq,DBALMK Resources-- Please see the official ORACLE-L
FAQ:
http://www.orafaq.com-- Author:
INET: azhar_at_mathtech-pk.comFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Wed May 30 2001 - 09:46:16 CDT
