Home -> Mailing Lists -> Oracle-L -> Re: SQL*Loader performance

Re: SQL*Loader performance

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Wed, 16 Jan 2008 14:52:21 -0800 (PST)
Message-ID: <444179.13969.qm@web58804.mail.re1.yahoo.com> 





Gene



Possible causes (guesses, with diagnostic test) in no particular order



    
  
  1.   table storage characteristics have changed to the extent that you are writing nearly twice as many blocks as before: you should be able to see this easily from comparing statistics / DBA_ views before and after

  
  2.   the distribution of the blocks you are writing in parallel has changed, causing increased i/o contention: you will be seeing much higher wait for i/o (per i/o operation). Are you writing to a different tablespace? or to a different datafile that is striped in a different way than before?

  
  3.   something has changed in the loader script (eg use of a SQL function) to cause you to fall back to SQL*Loader conventional mode (does that happen, or do you just get an error? my mind has gone blank)

  
  4.   you have added DATE columns, and/or increased the number of unique date conversions, causing the date cache size to be exceeded a nd therefore disabled. See http://www.oracle.com/technology/pub/notes/technote_datecache.html

  
  5.   you've got additional (or different) indexes or constraints to enable/validate

  
  6.   you implied you are loading in parallel - so you have N sql*loaders running with DIRECT=TRUE PARALLEL=TRUE, yes? It's up to you to deal with disabling constraints and triggers beforehand, and re-enabling later...






That's all I can think of at this time of night... 



Regards Nigel




    
  
  •   Original Message ----
From: "genegurevich_at_discover.com" <genegurevich_at_discover.com>
To: oracle-l <oracle-l_at_freelists.org>
Sent: Wednesday, January 16, 2008 9:39:23 PM
Subject: SQL*Loader performance






Hi everybody:



I am seeing a degradation in SQL*Loader performance after making a
seemingly benign changes to couple


of tables - the tables were dropped and recreated with some columns being
removed and other being added


or moved around. After that the load that used to take about an 80 jumped
to 140 min. I can't think of what could


have caused it. The degree of parallelism on the tables did not changed. I
am not sure what else could explain


that. Any ideas? The database is 9.2.0.8. The SQL*Loader is running in
direct mode



thank you



Gene Gurevich




--



http://www.freelists.org/webpage/oracle-l


--



http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 16 2008 - 16:52:21 CST












Original text of this message