Re: index rebuilding...

Hi Nuno,

The reason for this "not entirely weird" behaviour is to avoid contention and the buffer busy waits that could result in a heavily inserted, poorly freelisted segment. By effectively making the block accessed for inserts "random" (providing there is sufficient space), you reduce this problem. Note that multiple freelists kinda have this effect as well (that is increased numbers of empty blocks below the HWM)

The price you pay is the "nth" block being selected rather than the "next" block.

BTW, I have actually found ASSM tables to have as good (and in some cases ) better performance with regard to FTS than with non ASSM tablespaces. These are well populated tables, each with something like 500-600 64K extents.

Cheers

Richard
"Noons" <nsouto_at_optusnet.com.au.nospam> wrote in message news:Xns9319E5D9D8AE8mineminemine_at_210.49.20.254...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in
> news:b1oene$qt0$1$8302bc10_at_news.demon.co.uk and I quote:
>
> >
> > Not entirely weird.
>
>
> Ya reckon? :D
>
>
> >
> > It sounds like you have gone in with the
> > semi-default 'ASS management' feature,
> > which is not a feature of the LMT as such,
> > but a feature to replace freelist management.
>
>
> Straight forward "extent management uniform size 64K".
>
>
>
> > space available per block. Oracle then uses
> > the process ID to decide which space-management
> > block you should use to find some space, and which
>
>
> the "process ID"? Why on Earth?
> I mean: fine, if it is a matter of finding a free extent,
> it can use whatever algorithm. But once a free extent
> of 16 blocks is allocated, why would it then have to
> allocate anywhere else other than after the segment header?
>
>
> > block referenced in the space management block you
> > should first try. Consequently, a single row in a
> > newly created 16block table COULD end up in the
> > last block of the table.
>
>
> Similar to my case. I just created a table with 4 rows,
> in tablespace USERS. It ended up in an extent of 16 blocks
> (as expected), with a single data block in block 9 of the
> 16 blocks (totally flabbergasted by this one!).
> 50:50 split?
>
>
> > session in turn. I think in your case you will see
> > either the first 8 or second 8 blocks formatted (if you
> > don't get something so weird as an unformatted gap,
> > go for a 32 block table), and the block used then
> > varying from session to session.
>
>
> Is this to avoid the problem solved before by the FREELIST
> stuff? I mean, a "hash" using the process ID to decide
> which block in the extent the session will write to?
>
>
> >
> > In the case of the index - the root block is traditionally
> > the block after the segment header block - so I think a
> > special case is followed to format that block when
> > the index is created, whatever the process id of the
> > session that creates the index.
>
>
> Yup, it goes to the first block after segment header.
>
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optusnet.com.au.nospam
Received on Wed Feb 05 2003 - 07:33:02 CST