Re: Performance Degradation as table grows...Insert slows down

Hi,

 I tried recreating the indexes. I am using PL/SQL to load data which i receive as files from a c tree database. i am not using SQL loader. Also the sort_area_size = 65536 is the value set in the init file. should i increase it further?

Thanks
kasthuri.J

In article <71t69r$fev$1_at_nnrp1.dejanews.com>,   mpir_at_compuserve.com wrote:
> Two things to consider:
>
> 1-Drop the indexes, load the data, rebuild the indexes. In SQL*LOADER cases,
> I have seen this reduce througput time by 50%. It might work okay for your
> load.
>
> 2-Increase the sort area size and related parms in the SGA/init.ora.
>
> Oracle rebalances it's index b-tree automatically when a given number of
> records are inserted (mileage varies by key size and cardinality). It will
> help some if you use a large sort area because fewer sorts will be needed on
> the rebalance.
>
> In article <71t08g$642$1_at_nnrp1.dejanews.com>,
> satar_at_my-dejanews.com wrote:
> > Could you be sufferring from Free List Contention, Row Chaining, or too many
> > table extents? Also, check your Indexes, Are they sized and configuired
> > properly? Are the right columns being Indexed?
> >
> > These are the things that will slow down your enviroment. We have a database
> > which is being updated 8 hours a day. By re-sizing the tables, increasing
the
> > free list, and creating Indexes on certain columns, we were able to increase
> > performance.
> >
> > You're right, Increasing the SGA will not help your situation
> > significally...but you are only using 62MB of 1GB of RAM for your SGA! Don't
> > waste RAM, if you have it, then use it.
> >
> > Anyway...Good Luck,
> > Satar
> >
> > In article <71skg2$ink$1_at_nnrp1.dejanews.com>,
> > kasthuri_at_viisage.com wrote:
> > > hi,
> > >
> > > I have a database of size around 132 GB. I have
> > > a table with two Image columns.The primary key
> > > includes 4 fields (char(4), char(8), char(9) and
> > > char(14)) The data, index and image columns are
> > > placed in different tablespaces. the physical
> > > memory of the system is 1GB and has 4
> > > processors.It is a Dell Server 6100.
> > >
> > > Insertion of a record into this table which now has 4.3 million records
> takes
> > > around 350 millisecs. Most of the time is spent in blob data write only. I
> > > need to insert another 6 million records into this table. With this
> > > calculation it will take me another 32 days to insert these records into
the
> > > table. It will further slow down and will take around 2 secs to insert a
> > > single record as the table grows to 6 million records. (i had this table
> > > dropped and recreated because of some other problems too)
> > >
> > > I have some statistics too
> > >
> > > total sga : 62089900 bytes
> > >
> > > lib cache: .999998198
> > > row cache: .991865393
> > > free memory: 8921196 bytes.
> > > The ratio of execution:misses::4995938:9
> > > I tried increasing the SGA too. It didnt help
> > > improve the performance.
> > >
> > > The insertion was instantaneous if i create another table. My requirement
is
> > > to have a single table.
> > >
> > > Please suggest me as to what i should to do to
> > > improve performance.The Index tablespace is rapidly growing.
> > >
> > > I did contact oracle support also - and they are trying to help too. If
any
> > > of you have come across this problem and have a solution for it, please do
> > > suggest.
> > >
> > > Thanks
> > > kasthuri
> > >
> > > --
> > > Kasthuri Jegatheesan
> > >
> > > -----------== Posted via Deja News, The Discussion Network ==----------
> > > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> > >
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

--
Kasthuri Jegatheesan

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Nov 14 1998 - 19:03:13 CST