Re: ASSM and Indexes

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

As you know, you can relieve this problem by introducing freelist groups, but changing the number of freelist groups requires manually rebuilding the segments. Also freelist groups don't "share" their list of free blocks with other freelist groups, so you may end up "losing" free space in some circumstances (if no instance/process ID happens to hash a specific freelist group ID).

ASSM aims to reduce this trouble for all segments and eliminate any manual configuration and rebuilding needs.

So, ASSM in general reduces contention on segment's space management metadata blocks, without having to preconfigure/build freelist groups.

ASSM for table segments gives all above, plus an additional benefit of distributing new inserted rows to different datablocks based on the hash value of your Oracle PID.

--
Tanel Poder
http://blog.tanelpoder.com

On Tue, Jan 5, 2010 at 12:26 AM, Hemant K Chitale
<hkchital_at_singnet.com.sg>wrote:



>

> >>>>and I suppose Hemant's - is: "What, if any, is the benefit of ASSM for

> indexes?"

>

> YES.  What is the benefit of ASSM for Indexes  *is* my question.

>

> ASSM specifically relates to FreeLists -- ie replacing a FreeList(s) with a

> BitMap of available blocks.  For an Index, generally, a new entry can only

> go into a specific leaf block -- unless it is the rightmost entry and the

> rightmost block is "full" in which case any one free block is used.  If

> there are concurrent inserts with close key values, they contend on the same

> leaf blocks --- unlike tables where concurrent inserts can be distributed to

> separate blocks.  (Of course, Reverse Key indexes is one "solution"  but

> fraught with it's own issues !).

>

> So, what value add does ASSM provide for Indexes ?  If I create tables and

> indexes in the same tablespace, my indexes are in ASSM.  If I create a

> dedicated index tablespace, it still "defaults" to ASSM.  I couldn't find

> anything in the 10g and 11g concepts which explains how ASSM relates to

> Indexes.

>

> (Since ASSM is the default, it may become necessary for certain "features"

> --- eg the ALTER TABLE SHRINK with ROW MOVEMENT enabled works only in ASSM,

>  although I think it could have been written for MSSM as well).

>

>

> At 01:29 AM Monday, Wolfgang Breitling wrote:

>

>> Unless I'm totally mistaken, the only time Oracle needs to look for a free

>> block during index inserts is for block splits. Unlike for tables, there is

>> no choice of where to insert a new entry. So my question - and I suppose

>> Hemant's - is: "What, if any, is the benefit of ASSM for indexes?" Other

>> than consistency - having all tablespaces ASSM regardless of content. Is

>> there, for example, a benefit if you have massive deletes with a high

>> probability of ending up with completely empty blocks that can be re-used

>> followed by massive inserts of very similar data both in volume and content.

>>

>>

>> Wolfgang Breitling

>> Centrex Consulting Corporation

>> http://www.centrexcc.com

>>

>

>

> Hemant K Chitale

>

> http://hemantoracledba.blogspot.com

>

>

>

>

>

> --

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

>

>

>



-- 
Tanel Poder
http://blog.tanelpoder.com

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