Re: Slowness on insert statemente

On 18 Aug 2004 18:07:52 -0700, joel-garry_at_home.com (Joel Garry) wrote:

>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

So, are you saying that the number of extents is a performance impact? I thought that had been put to bed.

Components of rowid do not reference the extent, so I can' t see where number or size of extents has any impact at all on access to existing rows. It appears to me that the performance impact of 'improper' extent sizing -- many small vs. few large extents -- would come from the overhead incurred at the time a new extent is allocated. True enough, if that were the case and extents were severely undersized, an insert heavy app would perform slower from have to stop and acquire new extents more often. Whch, since the op was asking about insert performance might be something to look at.

All of which, perhaps, you were inferring and I just felt like clarifying. Or maybe my understanding is still way off base and I need to be set straight -- always a distinct possibility that I try to stay aware of. Received on Thu Aug 19 2004 - 08:17:43 CDT