Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Inserts gets slower and slower

From: Steve Adams <>
Date: Sat, 29 Apr 2000 09:30:00 +1000
Message-Id: <>

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.

Steve Adams

-----Original Message-----
From:	Surjit Sharma []
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.



    -----Original Message-----
    From: []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.

    Oracle V 7.3.4
    Pro*C 2.2.3
    OS : SUNOS 2.6 Received on Fri Apr 28 2000 - 18:30:00 CDT

Original text of this message