Re: SQL*Loader performance
Date: Wed, 16 Jan 2008 14:52:21 -0800 (PST)
Possible causes (guesses, with diagnostic test) in no particular order
- 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
- 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?
- 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)
- 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
- you've got additional (or different) indexes or constraints to enable/validate
- 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...
- 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
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 18.104.22.168. The SQL*Loader is running in direct mode