(unknown charset) Re: coalesc will really cleans all the empty blocks and takes them out of the index structure?
Date: Fri, 22 Jan 2010 15:21:10 +0100
Message-ID: <4B59B456.4040201_at_roughsea.com>
Contention means several processes accessing the same block simultaneously and having to wait because they can't write what they have to write at the same place. Coalescing can only make things worse (as I think of it, look for Richard Foote's blog - it's a great resource for Oracle index internals). Coalescing means squeezing everything in fewer blocks.
What you want is to have the processes access different parts of the
index, so that each one can write quietly in its own block. It is very
likely that your problem stems from a sequence-based primary key (if
not, consider partitioning). Good questions to ask yourself are:
1) is this column necessary? (if it doesn't appear as a foreign key
somewhere, the answer is probably no)
2) is the fact that numbers are sequential important ? (if not, think of
SYS_GUID() - bigger, but it will spread everything over the index)
3) will I need to access the index through a range scan? (if not,
consider building it REVERSE)
Hope that helps,
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Çå²è wrote:
> Hi ,
> My customer has a problem with wait event TX:index cotention. Oracle
> support suggest we should coalesce or reuild the index.
> Coalesce is less resource sensitive ,So i'd like to using coalesce.
> But as flow test:* [snip]
> *
> *
>
> w hat i want to ask, why the free blocks increased after coalesce? Can
> coalesce really resolve tx:index contention? If coalesce will lock table?
>
>
>
>
> *
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 22 2010 - 08:21:10 CST