Message-Id: <10481.104389@fatcity.com> From: "Surjit Sharma" Date: Fri, 28 Apr 2000 20:57:26 +1000 Subject: Re: Inserts gets slower and slower 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 ----- Original Message -----=20 From: Srinivasan Subramanian kandallu=20 To: Multiple recipients of list ORACLE-L=20 Sent: Friday, April 28, 2000 5:44 PM Subject: RE: Inserts gets slower and slower=20 1. 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. 2. 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. 3. make your table storage parameters proper. 4. Use always c functionality(strtok,strcmp etc..) to get the result = and use EXEC SQL only for database operations. Srini =20 -----Original Message----- From: root@fatcity.com [mailto:root@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
I am using an array size of 32765 as it = comes up=20 with an UPI error if this number is more than this.
 
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.
 
There are no calculations done using = EXEC SQL=20 etc.
 
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%.
 
Someone tells me that SGA could be = fragmented. I=20 really don't know what that means and to how DEFRAG SGA.
 
Your comments are highly = appreciated.
 
Surjit
----- Original Message -----
From:=20 Srinivasan Subramanian = kandallu
To: Multiple recipients of list ORACLE-L =
Sent: Friday, April 28, 2000 = 5:44=20 PM
Subject: RE: Inserts gets = slower and=20 slower

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.
2.=20 If 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.
3.=20 make  your table storage parameters proper.
4.=20 Use always c functionality(strtok,strcmp etc..) to get the result and = use EXEC=20 SQL only for database operations.
 
Srini
 
-----Original Message-----
From: root@fatcity.com = [mailto:root@fatcity.com]On Behalf Of Surjit = Sharma
Sent:=20 Thursday, April 27, 2000 8:51 PM
To: Multiple recipients = of list=20 ORACLE-L
Subject: Inserts gets slower and slower=20

Oracle Gurus
 
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 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.
 
Any body got any ideas/experience = in tackling=20 an issue like this.
 
Environment:
Oracle V = 7.3.4
Pro*C=20 2.2.3
OS : SUNOS 2.6
Thanks in anticipation.