Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Hot index block split on a very busy table--what is the impact

Re: Hot index block split on a very busy table--what is the impact

From: Jonathan Lewis <>
Date: Wed, 30 Aug 2006 22:00:39 +0100
Message-ID: <00ae01c6cc77$5967b0a0$0300a8c0@Primary>

Disregard as much of my previous post as you think fit. My brain has just woken up to the latches being CBC, not shared pool and library cache.

The branch block split may still be relevant - and the increase in processes cause by the split; but you don't seem to have a consequential shared pool problem.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

> 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?

Received on Wed Aug 30 2006 - 16:00:39 CDT

Original text of this message