Re: ASSM and Indexes

At the risk of revealing that I've missed the point myself, I think it boils down to the fact that ASSM was really designed for optimizing "heap" structures such as heap tables, not organized structures such as indexes. With indexes, space management is a function of the indexing logic itself -- there is little say on the part of the underlying storage mechanism (i.e. tablespace), except in the "corner cases" pointed out by Tanel. The vast majority of performance problems involving indexes are just neither helped nor hindered by the segment-space management policy (i.e. manual or auto) of the underlying tablespace.

OK, back into my hole...

Tim Gorman

consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
twitter    => http://www.twitter.com/timothyjgorman
Lost Data? => http://www.ora600.be/

Hemant K Chitale wrote:
>
> So, it really boils down to frequently index block splits and/or
> complete deletion of all entries in a leaf block occur -- or more
> importantly, how frequently is concurrency an issue here.
>
> Since I expect index keys to be significantly small (generally, but
> not always) such that many more entries exist in a leaf block than
> rows in a table block, I would believe that block splits with
> concurrency shouldn't be as high as insert concurrency on table blocks.
>
> Since most of my "performance issues" have been with batch jobs,
> concurrency isn't really a factor -- it is the volume of inserts and
> deletes that matters more to me.
>
>
> At 12:59 AM Tuesday, Tanel Poder wrote:
>> Hemant,
>>
>> When index blocks split you still need to allocate space from the
>> free block pool in the segment and this means updates to some segment
>> metadata blocks.
>>
>> When index blocks are deleted empty, they need to be marked free,
>> which again means updates to some segment metadata blocks.
>>
>> With freelists this would mean updates to segment header block, which
>> may get hot and cause buffer busy waits especially in RAC (which
>> you'd see as gc buffer busy waits then).
>>
>> --
>> Tanel Poder
>> <http://blog.tanelpoder.com>http://blog.tanelpoder.com
>
>
> Hemant K Chitale
>
> http://hemantoracledba.blogspot.com
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l