Re: Slowness on insert statemente

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1092753065.997815_at_yasure>...
> mario wrote:
>
> > Il Fri, 13 Aug 2004 05:33:06 -0700, Pete's ha scritto:
> >
> >
> >>"mario" <xnazsco_at_tiscali.it> wrote in message news:<pan.2004.08.12.08.20.00.26204_at_tiscali.it>...
> >>
> >>>Hi all,
> >>>a simple question:
> >>>the dimension of a table can directly be responsible
> >>>of the slowness of an insertion?
> >>>in other words: if I have two identical tables that only
> >>>differ for the number of records, it is the insert of one record
> >>>slower in the big table regarding the small?
> >>>
> >>>thank's
> >>
> >>The table that is experiencing the slow insert, have there been many
> >>deletes on this table? You may be getting some free list contention.
> >>Also, how many indexes on this table are there vs. the one that
> >>inserts fast? If you have many indexes on many different columns,
> >>then this will slow the insert down as Oracle will have to insert the
> >>data into all the indexes.
> >>
> >>HTH,
> >>Pete's
> >
> >
> >
> > Hi,
> > the two databases are identical except for the number of records.
> > The smallest in fact, has been created from a cold backup of the largest
> > one deleting some million of records in order to run some tests faster.
> > The physical path of the datafiles are not the same, but I think that
> > this is not a problem, because the datafiles are on a netapp filer and
> > are mounted via NFS in both the db.
> >
> > cheers
> > M.
>
> Deleting records makes nothing smaller. Truncate table followed by
> inserting a smaller number of records does but not a delete. My guess is
> that the high-water mark is exactly where it was and Oracle is still
> reading all the way to the top.
>
> Run Explain Plan wiht DBMS_XPLAN.

Also, there is the possibility that there are many more extents being in one than the other, and the extents are too small, and every time there is an insert those dictionary tables that track the extents are thrashing. Use larger extents, or LMT.

jg

--
@home.com is bogus.
Benevolence:  http://www.businessweek.com/technology/content/aug2004/tc20040818_1593.htm