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


If a session is waiting on another session to complete
an index block split, the wait will probably be a TX
wait - probably mode 6, but possibly mode 4.

The worst case scenarios arise when another (empty)
leaf block has to be taken off the free list, and
out of the index structure at the same time - if the leaf
block is then recognised as inappropriate (for reasons
that I don't fully understand) the split is rolled back and
another empty leaf block tested.  If you are unlucky you
may have many leaf blocks tested and rejected in rapid
sequence - leading to lots of start/stops on the TX waits.

The problem arises if you have a system that regularly
empties a number of leaf blocks (few hundred rows
deleted in one hit, say), and then rapidly inserts more
data.


The fact that you are seeing latch activity as the threat
is possible because of the nature of your client - I'd
guess it's something like a web app using .net. When
the database "slows down" it starts more sessions -
which hit the shared pool kicking out load so objects,
so the system slows down more, so the client starts
up more sessions, which hit the shared pool ...

Checks:
   v$sysstat shows branch block and leaf block splits -
   but you problem may be a single attempt at a branch
   block split that keeps failing - so the stats won't be
   high.

   v$library_cache, v$sgastat - around the event, you
   should see lots of reloads, and lots of free memory
   appearing.

Damage limitation:
   If my description of the table/index activity is correct,
   do an index coalesce after each big delete.  Cleans up
   the mess and takes empty leaf blocks out of the structure

   If my description of the client is correct, configure it to
   start with more processes, and launch new processes
   at a slower rate - then increase the shared_pool_reserved
   to be able to hold about 25KB for every process you
   might start, with a few MB spare.



Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


----- Original Message ----- From: "Zhu,Chao" <zhuchao@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, August 30, 2006 2:53 PM
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



--------------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/432 - Release Date: 29/08/2006

--
http://www.freelists.org/webpage/oracle-l


Other related posts: