Buffer busy waits & reverse index - Performance improvement tips & tricks

Buffer busy waits & reverse index - Performance improvement tips & tricks
-- What is Buffer busy waits?
-- How will reduce Buffer busy waits?
-- Reduce Buffer busy waits with implementing reverse index ( one factor)

From mail, I received one query "How can I decrease the buffer busy waits?". This query usually comes from new DBAs. Some cases it is most important question for all DBAs. We discuss how we gain performance using reverse index.

Buffer busy waits is normally due to waiting to get a clean buffer when the block buffer is dirty (and requires DBWR process to clean up) Increasing the size of buffer cache will help, until the cache is full of dirty buffers again. It will fix the problem only if the extra size of the cache, enables free buffers to be found long enough for the DBWR to clean up others.

Make sure there isn't any bad SQL running which is doing too much loading of data. i.e full table scans to update a single row etc.

If db block hit ratio is good and still u see high buffer busy waits , u need to dig little further:


1) Catch sqls at the same time when u see high buffer busy waits.

2) Also see in v$latch_children which of the cache buffer chains are highly used then others
Then try to locate the object/s which is/are highly used
thru x$bh.addr and v$latch_children.addr and then getting dba block address.

3) Also u should check if the number of waits on cache buffer chains is high, u may need to increase their number but generally is not reqd.

4) After u get the object , try to tune that object, by finding if storage parameters are ok or u may want to relocate it on to another datafile .

5) Also see any other waits which could relate to DBWR ,db block buffers like write complete waits, or DBWR dirty buffer inspected which may point to some I/O issue or less number of DBWRs .

Find following query outputs and analyse them:

SQL>
select * from v$waitstat where class ='data block';

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block            1991536    2307343

SQL>
select substr(event,1,25)"Event",Total_waits, Total_timeouts,Time_waited,Average_wait from v$system_event where event='buffer busy waits';

Event                   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
----------------------------------------------------------------------------
buffer busy waits       1745        0             2355597       1349.91

Using reverse key indexes to solve buffer busy wait problems:

Buffer busy wait and related events can cripple performance of concurrent inserts. Bad in a single instance database, far worse in a RAC (think "gc buffer busy"). Often the problem is because of a primary key populated from a sequence. Reversing the index can fix this problem.

Contention for index blocks when inserting grows can cause an application to hang up completely. This is because with a b-tree index on a monotonically increasing key, even though there will never be row lock all the inserted keys are going onto the same block at the edge of the index. A reverse key index will fix this. If you index, for example, 19, 20 and 21 as themselves, all three keys will probably be in the same block of the index. Instead, you would index them as 91, 02, and 12. So consecutive values will not be adjacent in the index: they will be distributed across the whole width of the index. You could do this programmatically, but Oracle provides the reverse key index for exactly this purpose. Here's an example:

create table t1 (c1 number);
create index idx_normal on t1(c1);

create table t2 (c1 number);
create index idx_reverse on t2(c1) reverse;

Do some inserts with loop and check the following query and you can see the difference.

SQL>
select object_name,value 
from v$segment_statistics
where owner='APP' and object_type='INDEX' 
and statistic_name='buffer busy waits';


See sample output. ( I have tested in my database)

OBJECT_NAME                         VALUE
------------------------------ ----------
IDX_NORMAL                         161347
IDX_REVERSE                          8983

The reverse key index has reduced the buffer busy waits by around 95%. Impressed? I hope you are. This is even more significant in a RAC environment, where buffer busy wait is globalized.
I am not saying that all indexes should be reversed. You do need to understand your data and how it is being accessed. For example, a non-equality predicate on the key cannot use the index. But when would you use a non-equality predicate on a primary key? Probably, never. It is hard to find a reason for not reversing all your monotonically increasing keys.

Hope this will help !!!