Re: ASSM and Indexes

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