Buffer Busy Waits on Primary Keys - RKI
11.2.03 - HPUX
Hi All.
We're seeing significant Buffer Busy Waits (around 15% of non idle waits during bad times) and most of the contention is on the following;
SYS.SEQ$, and two primary keys..
The primary key one seems easier to fix in the first instance, with most of the expert thinking seeming to be either Reverse Key Indexes, or hash partitions for index. Thinking this through, wouldn't moving from normal key order to reverse key order cause a lot more I/O - if the assumption is that the rows being queried are likely to be the newer rows being inserted?? If we scatter them evenly among the index segment - won't that have a deleterious effect on I/O for that segment? In effect we're swapping Buffer Busy Waits for much worse (?) I/O?