Semi DBA-type question
Tim Cummings
tim.cummings@frequencymarketing.com
Tue, 1 Jun 2004 17:56:48 -0400
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C44823.56BFC480
Content-Type: text/plain;
charset="iso-8859-1"
Guy,
I'm sorry I read the times wrong. SQLLDR loads 1.1 million rows in 4
minutes. yours should take about 60 seconds.
Tim Cummings
Cummings Consulting
Cincinnati, OH
-----Original Message-----
From: Tim Cummings
Sent: Tuesday, June 01, 2004 4:39 PM
To: 'Guy Werry'; PH Mailing List (E-mail)
Subject: RE: Semi DBA-type question
Guy,
I routinely add millions of rows of data to our oracle database on a Windows
platform. None of them take anywhere near 10 hours. My guess is that some
of the table space parms(next extent size for one or your SGA)are not set
properly.
Also Harold's suggestion to remove or disable indexes/foreign keys and
re-enable them after the load is another good time-saving technique.
If this table is being maintained by doing "ADD UPDATE"s or all of the rows,
it may be faster to TRUNCATE the table and use SQLLDR to re-create it from
your source data. Oracle's SQLLDR is pretty fast. I can load 1.1 million
rows of 684byte fixed length records in around 4 hours.
Loading 69,000 rows should take < 10 minutes (depending on record size).
Tim Cummings
Cummings Consulting
Cincinnati, OH
-----Original Message-----
From: Guy Werry [mailto:guy.werry@hbms.ca]
Sent: Tuesday, June 01, 2004 2:25 PM
To: PH Mailing List (E-mail)
Subject: Semi DBA-type question
I'm looking at trying to optimize a 'C' program (ick!). Part of its
processing is the insertion of approximately 69 000 records into one table.
This program typically runs for about 10 hours, unless the inserts have been
done, in which case it runs in < 1 hour.
I realize that inserting a bunch of records will take more time, but one of
my co-workers thinks that someone mentioned once that the overhead required
just to allocate table space for that volume of inserts would be
significant.
Unfortunately, our DBA has retired and so I don't have anyone to ask this
question of. We are running an older version of Oracle (7.3 something) on
HP-Ux 10.2.
So, if any of you folks with DBA-type experience has words of wisdom, I
welcome them. And, as a Powerhouse "guy", I'd really rather be writing this
in Qtp instead of "C"!
Thanks,
Guy L. Werry
Senior Systems Analyst
Hudson Bay Mining & Smelting
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request@lists.sowder.com
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
------_=_NextPart_001_01C44823.56BFC480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2654.45">
<TITLE>RE: Semi DBA-type question</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>Guy,</FONT>
</P>
<P><FONT SIZE=3D2>I'm sorry I read the times wrong. SQLLDR loads =
1.1 million rows in 4 minutes. yours should take about 60 =
seconds.</FONT>
</P>
<P><FONT SIZE=3D2>Tim Cummings</FONT>
<BR><FONT SIZE=3D2>Cummings Consulting</FONT>
<BR><FONT SIZE=3D2>Cincinnati, OH</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Tim Cummings </FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, June 01, 2004 4:39 PM</FONT>
<BR><FONT SIZE=3D2>To: 'Guy Werry'; PH Mailing List (E-mail)</FONT>
<BR><FONT SIZE=3D2>Subject: RE: Semi DBA-type question</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>Guy,</FONT>
</P>
<P><FONT SIZE=3D2>I routinely add millions of rows of data to our =
oracle database on a Windows platform. None of them take anywhere =
near 10 hours. My guess is that some of the table space =
parms(next extent size for one or your SGA)are not set =
properly.</FONT></P>
<P><FONT SIZE=3D2>Also Harold's suggestion to remove or disable =
indexes/foreign keys and re-enable them after the load is another good =
time-saving technique.</FONT></P>
<P><FONT SIZE=3D2>If this table is being maintained by doing "ADD =
UPDATE"s or all of the rows, it may be faster to TRUNCATE the =
table and use SQLLDR to re-create it from your source data. =
Oracle's SQLLDR is pretty fast. I can load 1.1 million rows of =
684byte fixed length records in around 4 hours.</FONT></P>
<P><FONT SIZE=3D2>Loading 69,000 rows should take < 10 minutes =
(depending on record size).</FONT>
</P>
<P><FONT SIZE=3D2>Tim Cummings</FONT>
<BR><FONT SIZE=3D2>Cummings Consulting</FONT>
<BR><FONT SIZE=3D2>Cincinnati, OH</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Guy Werry [<A =
HREF=3D"mailto:guy.werry@hbms.ca">mailto:guy.werry@hbms.ca</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, June 01, 2004 2:25 PM</FONT>
<BR><FONT SIZE=3D2>To: PH Mailing List (E-mail)</FONT>
<BR><FONT SIZE=3D2>Subject: Semi DBA-type question</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>I'm looking at trying to optimize a 'C' program =
(ick!). Part of its</FONT>
<BR><FONT SIZE=3D2>processing is the insertion of approximately 69 000 =
records into one table.</FONT>
<BR><FONT SIZE=3D2>This program typically runs for about 10 hours, =
unless the inserts have been</FONT>
<BR><FONT SIZE=3D2>done, in which case it runs in < 1 hour.</FONT>
</P>
<P><FONT SIZE=3D2>I realize that inserting a bunch of records will take =
more time, but one of</FONT>
<BR><FONT SIZE=3D2>my co-workers thinks that someone mentioned once =
that the overhead required</FONT>
<BR><FONT SIZE=3D2>just to allocate table space for that volume of =
inserts would be</FONT>
<BR><FONT SIZE=3D2>significant.</FONT>
</P>
<P><FONT SIZE=3D2>Unfortunately, our DBA has retired and so I don't =
have anyone to ask this</FONT>
<BR><FONT SIZE=3D2>question of. We are running an older version =
of Oracle (7.3 something) on</FONT>
<BR><FONT SIZE=3D2>HP-Ux 10.2.</FONT>
</P>
<P><FONT SIZE=3D2>So, if any of you folks with DBA-type experience has =
words of wisdom, I</FONT>
<BR><FONT SIZE=3D2>welcome them. And, as a Powerhouse =
"guy", I'd really rather be writing this</FONT>
<BR><FONT SIZE=3D2>in Qtp instead of "C"!</FONT>
</P>
<P><FONT SIZE=3D2>Thanks,</FONT>
<BR> <FONT SIZE=3D2>Guy L. =
Werry</FONT>
<BR> <FONT SIZE=3D2>Senior =
Systems Analyst </FONT>
<BR> <FONT SIZE=3D2>Hudson =
Bay Mining & Smelting</FONT>
<BR><FONT SIZE=3D2>=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =
=3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D =3D</FONT>
<BR><FONT SIZE=3D2>Mailing list: powerh-l@lists.sowder.com</FONT>
<BR><FONT SIZE=3D2>Subscribe: "subscribe" in message body to =
powerh-l-request@lists.sowder.com</FONT>
<BR><FONT SIZE=3D2>Unsubscribe: "unsubscribe =
<password>" in message body to =
powerh-l-request@lists.sowder.com</FONT>
<BR><FONT SIZE=3D2><A =
HREF=3D"http://lists.sowder.com/mailman/listinfo/powerh-l" =
TARGET=3D"_blank">http://lists.sowder.com/mailman/listinfo/powerh-l</A><=
/FONT>
<BR><FONT SIZE=3D2>This list is closed, thus to post to the list you =
must be a subscriber.</FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C44823.56BFC480--