Re: Index vs. table scans in statspack reports

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Sun, 30 Nov 2003 23:32:45 +0100
Message-ID: <7lrksv8nkusa8u0735vajp5iqj12lgote2@4ax.com>

Yes of course, it is a block level contention.

The easiest way would be to reorganize the object, that is quite straigforward, if it happens to work (I mean records could end up in the same block again).

I found an interesting hint at
http://otn.oracle.com/oramag/oracle/03-mar/o23expert.html

"If the hot block is the index root block, a reverse-key index won't help. Setting _DB_BLOCK_HASH_BUCKETS to the prime number just larger than twice the number of buffers (DB_CACHE_SIZE/DB_BLOCK_SIZE) will usually eliminate this problem. Prior to Oracle9i, this parameter had a default that caused tremendous contention for this latch; the default is correctly set to a prime number in Oracle9i."

I don't like using underscore parameters in a production DB, but if Oracle itself endorses it...

Bye
Rick Denoire Received on Sun Nov 30 2003 - 16:32:45 CST