Re: Index as hot block
- From: hrishy <hrishys@xxxxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Mon, 10 May 2010 15:42:50 +0000 (GMT)
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
http://www.EvDBT.com/papers.htm 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 => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ 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:
