|
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: -- //www.freelists.org/webpage/oracle-l |
