Re:Re: coalesc will really cleans all the empty blocks and takes them out of the index structure?

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 isfrequent¡£ 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_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-l