Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance tuning

Re: Performance tuning

From: Stephen Andert <StephenAndert_at_firsthealth.com>
Date: Wed, 30 May 2001 07:46:16 -0700
Message-ID: <F001.00313934.20010530075128@fatcity.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US