Re: SQL*Loader performance

From: <>
Date: Thu, 17 Jan 2008 10:42:57 -0600
Message-ID: <>


Thank you for your thoughts.

I am looking at the date cache document. We did add couple of date columns, so this is certainly worth checking.
I am pretty sure that the indices were dropped, but - stupid idiot - did not confirm that yesterday during the run. This could be the issue, although I thought that direct load will fail if the indices are not dropped (may be I
am wrong)

Here is what I have done. If any of that makes you thing about any possible explanation. please let me know

  1. I don't have a dba_views from before the changes were made, but I did export the affected tables before recreating them. I took that export file and imported it into a dev instance. Then I have compared the data from the dba_tables, dba_part_tables and dba_tab_partitions for the tables I have imported from my backup file with the same data for the current tables in production. I see an increase in the row length for the largest table from 812 to 839 bytes and increase in the number of blocks from 3235581 to 3318141 - about 2.5% increase. I see even smaller increases in the number of blocks for the other tables. I do see a significant difference in the sample_Size. It went from 2000 to a significantly larger value for all the tables. I am not sure how to interpret that though.

Another difference that I see is that the logging on some of the tables has switched from Yes to No (not the other way around). This should have improved performance in my opinion. I have confirmed that all tables are set with
NOLOGGING in production.

2) I am loading to the same tablespace and same datafiles. There were no changes there.

3) I was watching the database during the load and saw the inserts being done in direct mode. There was an
insert with some odd hint in the sqlarea view.

6) The load is running in parallel. There are 4 tables and I saw 32 processes running against the database(each runs 8 ways). I was wondering though why out of these 32 processes no more than 7 were active at any given time while
the rest were inactive. There are no triggers and all foreign keys are disabled (I checked that)

thank you again

Gene Gurevich

             Nigel Thomas                                                  
   >                                                 To 
                             , oracle-l 
             01/16/2008 04:52          <>            
             PM                                                         cc 
                                       Re: SQL*Loader performance          


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
  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: "" <> To: oracle-l <> 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 The SQL*Loader is running in direct mode

thank you

Gene Gurevich


-- Received on Thu Jan 17 2008 - 10:42:57 CST

Original text of this message