Re: Performance tuning

[Stephen Andert]

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 (http://oracle.oreilly.com/news/oraclesqlload_0401.html) which details my experience which resulted in some big performance gains.   HTH,   Stephen >>> [EMAIL PROTECTED] 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 enough for single transaction size and considering 30% rollback overhead. We adjusted the OPTIMAL TO 10 M to have avoid rollback extension Rollback segment, databuffer cache have hit ratio of 100%. The loading was fast only for first 10 commits but then it slowed like snail. LOADING TOOK 22 hours in the first run on ORACLE8i NT4 128 megs RAM . SGA figures in M : NAME                     VALUE -------------------- --------- Fixed Size            .0676384 Variable Size        239.02734 Database Buffers       39.0625 Redo 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. TIA Azhar Siddiq, DBA LMK Resources -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author:   INET: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).