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: <CHUCK_HAMILTON_at_qvc.com>
Date: Mon, 1 May 2000 11:51:31 -0400
Message-Id: <10484.104556@fatcity.com>


I thought I read something earlier in this thread (maybe it was a different thread) that he was committing frequently. If not, wouldn't doing this help the situation?
--

Chuck Hamilton
Oracle DBA
Enterprise Technical Services

                                                                                                                      
                    Steve Adams                                                                                       
                    <steve.adams_at_ixo        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    ra.com.au>              cc:     (bcc: CHUCK HAMILTON/QVC)                                         
                    Ext: NA                 Subject:     RE: Inserts gets slower and slower                           
                    Sent by:                                                                                          
                    root_at_fatcity.com                                                                                  
                                                                                                                      
                                                                                                                      
                    04/28/00 08:36                                                                                    
                    PM                                                                                                
                    Please respond                                                                                    
                    to ORACLE-L                                                                                       
                                                                                                                      
                                                                                                                      




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

  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

 << File: ATT00058.htm >>
--

Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Mon May 01 2000 - 10:51:31 CDT

Original text of this message

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