Re: coalesc will really cleans all the empty blocks and takes them out of the index structure?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: maclean_007@xxxxxxx
- Date: Fri, 22 Jan 2010 15:21:10 +0100
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?
>
>
>
>
> *
--
//www.freelists.org/webpage/oracle-l
Other related posts:
