Re: SQL*Loader performance
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Jan 2008 14:52:21 -0800 (PST)
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@xxxxxxxxxxxx" <genegurevich@xxxxxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
