RE: Inserts gets slower and slower

Hi All,

This reads for all the world like a consistent read issue to me. The more you insert, the more work you have to do get a consistent image on the next fetch.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/


-----Original Message-----
From:	Surjit Sharma [SMTP:surjits_at_ozemail.com.au]
Sent:	Friday, April 28, 2000 10:04 PM
To:	Multiple recipients of list ORACLE-L
Subject:	Re: Inserts gets slower and slower

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 ----- From: Srinivasan Subramanian kandallu To: Multiple recipients of list ORACLE-L Sent: Friday, April 28, 2000 5:44 PM Subject: RE: Inserts gets slower and slower
  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

    -----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 Received on Fri Apr 28 2000 - 18:30:00 CDT