Re:Re: coalesc will really cleans all the empty blocks and takes them out of the index structure?
- From: 清茶 <maclean_007@xxxxxxx>
- To: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
- Date: Fri, 22 Jan 2010 22:51:01 +0800 (CST)
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 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@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?
>>
>>
>>
>>
>>?*
>
>
Other related posts:
