Re: SQL*Loader performance
- From: genegurevich@xxxxxxxxxxxx
- To: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- Date: Thu, 17 Jan 2008 10:42:57 -0600
Nigel,
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
<nigel_cl_thomas@
yahoo.com> To
genegurevich@xxxxxxxxxxxx, oracle-l
01/16/2008 04:52 <oracle-l@xxxxxxxxxxxxx>
PM cc
Subject
Re: SQL*Loader performance
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:
