RE: Inserts gets slower and slower

From: Srinivasan Subramanian kandallu <srinivasan.kandallu_at_wipro.com>
Date: Fri, 28 Apr 2000 12:00:09 +0530
Message-Id: <10480.104377@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_002E_01BFB109.4D010E00 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

There is one concept of Batch inserts in Pro*c where you can hold record values in host arrays and insert into table all at once.
If your are doing any operations like "select 2*3/2 into :var1 from dual", avoid this and try to use 'C' functionality to get the result.
make your table storage parameters proper.
Use always c functionality(strtok,strcmp etc..) to get the result and use EXEC SQL only for database operations.

Srini

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Surjit Sharma Sent: Thursday, April 27, 2000 8:51 PM To: Multiple recipients of list ORACLE-L Subject: Inserts gets slower and slower

Oracle Gurus

I have a proram written in Pro*c. It open a cursor and then open and closes a few more cursor to extract the data and then inserts into a table. In the first hour the performance is quite good however the performance degraded exponentially as the the load program progresses. There is no change to the machine load. In fact there is no other jobs running. The data is all static expect for the table that is being populated. The table being populated has no indexes and is not being used in the program for any other purpose.

Any body got any ideas/experience in tackling an issue like this.

  Environment:
  Oracle V 7.3.4
  Pro*C 2.2.3
  OS : SUNOS 2.6
  Thanks in anticipation.

Surjit

------=_NextPart_000_002E_01BFB109.4D010E00 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D690511906-28042000>1.=20
There is one concept of Batch inserts in Pro*c where you can hold record = values=20
in host arrays and insert into table all at once.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D690511906-28042000>2. If=20
your are doing any operations like "select 2*3/2 into :var1 = from=20
dual", avoid this and try to use 'C' functionality to get the=20 result.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D690511906-28042000>3.=20
make  your table storage parameters proper.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D690511906-28042000>4. Use=20
always c functionality(strtok,strcmp etc..) to get the result and use = EXEC SQL=20
only for database operations.</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D690511906-28042000>Srini</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D690511906-28042000></SPAN></FONT> </DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
  <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT = face=3DTahoma=20
  size=3D2>-----Original Message-----<BR><B>From:</B> root_at_fatcity.com=20   [mailto:root_at_fatcity.com]<B>On Behalf Of </B>Surjit = Sharma<BR><B>Sent:</B>=20
  Thursday, April 27, 2000 8:51 PM<BR><B>To:</B> Multiple recipients of = list=20
  ORACLE-L<BR><B>Subject:</B> Inserts gets slower and slower=20

  <BR><BR></DIV></FONT>
  <DIV><FONT face=3DArial size=3D2>
  <DIV><FONT face=3DArial size=3D2>Oracle Gurus</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>I have a proram written in Pro*c. It =

open a=20
  cursor and then open and closes a few more cursor to extract the data = and then=20
  inserts into a table. In the first hour the performance is quite good = however=20
  the performance degraded exponentially as the the load program = progresses.=20
  There is no change to the machine load. In fact there is no = other jobs=20
  running. The data is all static expect for the table that is being = populated.=20
  The table being populated has no indexes and is not being used in the = program=20
  for any other purpose.</FONT></DIV>
  <DIV> </DIV>
  <DIV><FONT face=3DArial size=3D2>Any body got any ideas/experience in = tackling an=20
  issue like this.</FONT></DIV>
  <DIV> </DIV>
  <DIV><FONT face=3DArial size=3D2>Environment:<BR>Oracle V = 7.3.4<BR>Pro*C=20
  2.2.3<BR>OS : SUNOS 2.6<BR>Thanks in anticipation.</FONT></DIV>   <DIV> </DIV>
  <DIV><FONT face=3DArial=20
size=3D2>Surjit<BR></FONT></DIV></FONT></DIV></BLOCKQUOTE></BODY></HTML> Received on Fri Apr 28 2000 - 01:30:09 CDT