Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Inserts gets slower and slower

Re: Inserts gets slower and slower

From: Surjit Sharma <surjits_at_ozemail.com.au>
Date: Fri, 28 Apr 2000 20:57:26 +1000
Message-Id: <10481.104389@fatcity.com> 





This is a multi-part message in MIME format.



------=_NextPart_000_0033_01BFB154.5B942D30
Content-Type: text/plain;


        charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable



I am using an array size of 32765 as it comes up with an UPI error if =
this number is more than this.



First 10-15 batches are inserted at an average speed of 5-8 minutes (per =
32765 records) but subsequently it takes 20 minutes, 1hr , 2 hours and =
so on. I am really lost at what is the problem. I have tried to run =
statistics and standard utilites like utlbstat/utlestat. But there is =
nothing that really stands out as an obvious problem.



There are no calculations done using EXEC SQL etc.



The system has 4 CPUs (1 Gig Memory) and initially the CPU's seem to be =
utiliised 15-20% but as the inserts gets slower you can see that the CPU =
utililization decreases to 1-2%.



Someone tells me that SGA could be fragmented. I really don't know what =
that means and to how DEFRAG SGA.



Your comments are highly appreciated.



Surjit





  Srini


  =20


    -----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=20




    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_0033_01BFB154.5B942D30
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 face=3DArial size=3D2>I am using an array size of 32765 as it =

comes up=20


with an UPI error if this number is more than this.</FONT></DIV>


<DIV>&nbsp;</DIV>

<DIV><FONT face=3DArial size=3D2>First 10-15 batches are inserted at an =

average=20


speed of 5-8 minutes (per 32765 records) but subsequently it takes 20 =
minutes,=20


1hr , 2 hours and so on. I am really lost at what is the problem. I have =
tried=20


to run statistics and standard utilites like utlbstat/utlestat. But =
there is=20


nothing that really stands out as an obvious problem.</FONT></DIV>


<DIV>&nbsp;</DIV>

<DIV><FONT face=3DArial size=3D2>There are no calculations done using =

EXEC SQL=20



etc.</FONT></DIV>


<DIV>&nbsp;</DIV>

<DIV><FONT face=3DArial size=3D2>The system has 4 CPUs (1 Gig Memory) =

and initially=20


the CPU's seem to be utiliised 15-20% but as the inserts gets slower you =
can see=20


that the CPU utililization decreases to 1-2%.</FONT></DIV>


<DIV>&nbsp;</DIV>

<DIV><FONT face=3DArial size=3D2>Someone tells me that SGA could be =

fragmented. I=20


really don't know what that means and to how DEFRAG SGA.</FONT></DIV>


<DIV>&nbsp;</DIV>

<DIV><FONT face=3DArial size=3D2>Your comments are highly =

appreciated.</FONT></DIV>


<DIV>&nbsp;</DIV>

<DIV><FONT face=3DArial size=3D2>Surjit</FONT></DIV>

<BLOCKQUOTE=20



style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: =
0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV=20


  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20


  <A href=3D"mailto:srinivasan.kandallu_at_wipro.com"=20
  title=3Dsrinivasan.kandallu_at_wipro.com>Srinivasan Subramanian =
kandallu</A> </DIV>


  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
href=3D"mailto:ORACLE-L_at_fatcity.com"=20


  title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> =


</DIV>



  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Friday, April 28, 2000 =
5:44=20


PM</DIV>


  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> RE: Inserts gets =
slower and=20


  slower </DIV>


  <DIV><BR></DIV>


  <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=20


  values 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.=20


  If your are doing any operations&nbsp;like "select&nbsp;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&nbsp; your table storage parameters proper.</SPAN></FONT></DIV>
  <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D690511906-28042000>4.=20


  Use always c functionality(strtok,strcmp etc..) to get the result and =
use EXEC=20


  SQL only for database operations.</SPAN></FONT></DIV>
  <DIV>&nbsp;</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>&nbsp;</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 =



    [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=20


    then inserts into a table. In the first hour the performance is =
quite good=20


    however the performance degraded exponentially as the the load =
program=20


    progresses. There is no change to the machine load. In fact there is =
no=20


    other&nbsp;jobs running. The data is all static expect for the table =
that is=20


    being populated. The table being populated has no indexes and is not =
being=20


    used in the program for any other purpose.</FONT></DIV>
    <DIV>&nbsp;</DIV>


    <DIV><FONT face=3DArial size=3D2>Any body got any ideas/experience =
in tackling=20


    an issue like this.</FONT></DIV>

    <DIV>&nbsp;</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>
Received on Fri Apr 28 2000 - 05:57:26 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US