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.

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>>>>