RE: Hot index block split on a very busy table--what is the impact
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <zhuchao@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Aug 2006 11:02:33 -0400
If the load spike is very short - so short that you cannot catch the actual
wait event, I'm curious as to why this is perceived as an incident and
problem to be solved.
How long a period of "during that time nothing can be done" are we talking
about?
If you are *not* getting a waits on Index Block Split during that period,
then that is probably *not* your problem, so allowing the knowledge that
that you have a busy index distract you from finding what you are waiting
for is probably not the best pursuit. Likewise, I would not search for all
waits that generate a CPU spike, but rather try to discover what you are
actually waiting for. Spinning like crazy for any latch wait by many
sessions will generate a CPU spike.
So pursue the real lead you have: waiting for latch free on cache buffer
chains.
Good luck!
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Zhu,Chao
Sent: Wednesday, August 30, 2006 9:53 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Hot index block split on a very busy table--what is the impact
hi, all,
We have a few database with very high concurrent access busy tables,
that some indexes of the busy tables could be accessed 1.5K-4K times per
second. (PK lookup)
I am wondering , when such index got block split (at the root, or branch
level), what will be the impact on the system.
Index block split is said to be an expensive operation, during the block
split on branch/root block, what the other sessions that is doing select
based on this index, be waiting on? There is a wait event named: Index
Block Split with p1,p2,p3 pointing to block address, level.
Document "Description of oracle 7 Wait event and enqueue" says it will
yield CPU.
We have a few production incident when load suddenly jump from 10, to
several hundred, and during that time nothing can be done. From our
monitoring tools, it is mainly "latch free" contention inside oracle, and it
is cache buffer chains. The load spike matches the oracle description of
block split, but the wait event does not match. And because the load spike
time is very short, we can't capture the actual wait event/p1,p2,p3 during
the exact time of the load spike time.
Anyone have similar problem/insight into this issue?
Is there some v$ view to track, (v$segstat does not) have log about which
index go through how many time block split? Or any other effecitve way?
--
Regards
Zhu Chao
www.cnoug.org
Other related posts:
