Re: Index as hot block

  • From: Dion Cho <ukja.dion@xxxxxxxxx>
  • To: hrishys@xxxxxxxxxxx
  • Date: Tue, 11 May 2010 09:22:52 +0900

"library cache: mutex X" wait event would mean that you're suffering library
cache contention, not buffer cache contention.

There are tons of bugs reported about mutex contention on Metalink(MOS).
You'd like to search them.

In addition, V$MUTEX_SLEEP_HISTORY view gives very useful informations on
the mutex contention.

What makes me interested is the wait event of "SQL*Net break/reset to
client". It would mean that you have frequent parse failures. Not sure this
would have any relation with the current mutex contention, but it would be
worth trying a research.

Dion Cho - Oracle Performance Storyteller (english) (korean) (japanese) (q&a)

2010/5/11 hrishy <hrishys@xxxxxxxxxxx>

> Hi Tim
> You are most welcome to question my diagnosis.It would also help me to
> validate and assert that i am on the right path.
> The wait event that prompted me to look into the hot blocks is this
> library cache: mutex X
> Look at the top 5 wait events below
> Although the Avg Wait is zero there were significant as percent of the db
> time.
> I zeroed it down to the segment of type index using addm and awr reports
>   Event Waits Time(s) Avg wait (ms) % DB time Wait Class library cache:
> mutex X 365,794,168 62,171 0 42.83 Concurrency db file sequential read
> 2,740,870 24,882 9 17.14 User I/O DB CPU   11,901   8.20   SQL*Net
> break/reset to client 1,234 6,502 5269 4.48 Application latch: shared pool
> 265,128 5,536 21 3.81 Concurrency
> --- On *Mon, 10/5/10, Tim Gorman <tim@xxxxxxxxx>* wrote:
> From: Tim Gorman <tim@xxxxxxxxx>
> Subject: Re: Index as hot block
> To: hrishys@xxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Date: Monday, 10 May, 2010, 16:21
> Walid,
> Changing the order of the columns in a unique index is not likely to
> relieve the "hot block" situation.  By the way, which wait-event is
> indicating "hot block in index" to you?  Not doubting the diagnosis, but I'm
> alway curious...
> At any rate, the basic problem is that one (or more) of the columns A, B,
> and/or C are (probably) being populated by a monotonically-ascending data
> value, such as a sequence number or a timestamp. It is this fact that is
> causing your "hot block" in the index, and re-ordering the columns is
> unlikely to change that.
> I wrote a paper called "Understanding Indexes" earlier this century at
> <> and to
> make a long story short, perhaps you would consider making the index in
> question a "reverse-key" index (i.e. "REVERSE" clause in CREATE INDEX or
> ALTER INDEX ... REBUILD).  It would almost certainly eliminate your "hot
> block" problem, but it could cause other problems if there are INDEX RANGE
> SCAN operations (as opposed to INDEX UNIQUE SCAN) operations on the existing
> index.  I'd suggest first considering the nature of the unique constraint,
> whether the supporting index is used in queries and if so, how.  Then, I'd
> suggest looking for supporting evidence by querying DBA_HIST_SQL_PLAN where
> OBJECT_NAME = <index-name> and OPTIONS = 'RANGE SCAN' and operation =
> 'INDEX'.  If such a query returns any rows, I would backtrack by SQL_ID to
> look at the SQL statement in question, try to determine what part of your
> application is using that operation, and (in conjunction with developers or
> application owners) make a determination as to impact of forcing the
> execution plan of those statement(s) away from INDEX RANGE SCAN is
> worthwhile or not.
> In other words, using a REVERSE index can solve your immediate problem, but
> it might cause others.  If it does cause other problems, you can get some
> advance notice on what those problems might be and make a pro-active
> decision on whether the REVERSE index solution is worth the trade-off.
> Hope this helps....
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
> website    => <>
> email      => Tim@xxxxxxxxx 
> <>
> mobile     => +1-303-885-4526
> fax        => +1-303-484-3608
> Lost Data? => for info about DUDE...
> hrishy wrote:
>   Hi
> I have a partitioned table called mytable (a,b,c,d,e,f) partitioned by
> range on column a.
> I need to enforce a unique constraint
> currently the uniqueness is enforced by creating a locally partitioned
> index on
> myindx(c,b,a)
> and this has become a hot block.
> I was thinking of rebuilding that index and using myindx(a,b,c)
> can you guys let me know what your thoughts are ?
> regards

Other related posts: