Re: Re: coalesc will really cleans all the empty blocks and takes them out of the index structure?
- From: Thomas Roach <troach@xxxxxxxxx>
- To: maclean_007@xxxxxxx
- Date: Fri, 22 Jan 2010 09:57:00 -0500
What kind of indexes is the contention on? Do you know what statements cause
the contention?
You mention this one wait initially but now you mention others. Can you give
us a better idea of the waits you are seeing? Which are the most prevalent?
2010/1/22 清茶 <maclean_007@xxxxxxx>
> As in metalink *Bug 8286901 claimed that:
> *
>
> Issue encountered by customer and Oracle developer (Stefan Pommerenk).
> He describes is thus:
> "Space search performed by the index splitter can't find space in neighboring
> blocks, and then instead of allocating new space, we go and continue to
> search for space elsewhere, which manifests itself in block reads from disk,
> block cleanouts, and subsequent blocks written due to aggressive MTTR
> setting."
>
> "To clarify: the cleanouts are not the problem per se. The culprit seems to
> be that the space search performed by the index splitter can't find space in
> neighboring blocks, and then instead of allocating new space, we go and
> continue to search for space elsewhere, which manifests itself in block reads
> from disk, block cleanouts, and subsequent blocks written due to aggressive
> MTTR setting. This action has caused other sessions to get blocked on TX
> enqueue contention, blocked on the splitting session. Advice was to set 10224
> trace event for the splitter for a short time only in order to get
> diagnostics as to why the space search rejected most blocks.
> > A secondary symptom are the bitmap level 1 block updates, which may or may
> not be related to the space search; I've not seen them before, maybe because
> I didn't really pay attention :P , but the symptoms seen in the ASH trace
> indicate it's the same problem. Someone in space mgmt has to look at it to
> confirm it is the same problem."
>
>
> REDISCOVERY INFORMATION:
> *Excessive logical IO (on index segments), excessive "enq: TX - index
> contention" (in OLTP environments), and excessive "failed probes on
> index block reclamation" during index maintenance operations that cause
> an increase in the BLEVEL of the index (i.e. a root block split).
> WORKAROUND:
> Proactively coalesce said indexes.*
>
> in my env,the "failed probes on index block reclamation" event is frequent。
> And i can confirm that long delay is caused by endless "space search
> performed by the index splitter can't find space in neighboring blocks,
> and then instead of allocating new space, we go and continue to search for
> space elsewhere, which manifests itself in block reads from disk, block
> cleanouts, and subsequent blocks written due to aggressive MTTR setting"
> using logmnr ( i find a lots of delay block cleanout).
>
> And the support advised me to coalesce index (to cleans all the empty
> blocks and takes them out of the index structure.)
> or nerver delete data on those tables ( not allowed by customers ) .
> So i am absolutely confused.
>
>
> 在2010-01-22 22:21:10，"Stephane Faroult" <sfaroult@xxxxxxxxxxxx> 写道：
> >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?
> >>
> >>
> >>
> >>
> >> *
> >
> >
>
>
>
>
--
Thomas Roach
813-404-6066
troach@xxxxxxxxx
Other related posts:
