Globally busy buffers due to contention for index leaf and branch blocks

As we discussed in previous sessions, because of batch INSERT high concurrency could occur for a relatively small set of index leaf and branch blocks, where index keys are populated with monotonically ascending values.The symptoms are increased frequencies and wait times for a certain set of wait events, which we will call “block contention waits” … you can look for bellow wait events in AWR or Statspack (9i) reports.

Block contention waits in Oracle9i Block contention waits in Oracle10g /11g
buffer busy global cache gc buffer busy
global cache busy gc current block busy
buffer busy global CR gc current split
enqueue, with further investigation leading to TX enqueue waits in share mode enq: TX – index contention

There are some other global cache wait events which may occur because of this kind of workload. They called as “block transfer waits”:

Block transfer waits in Oracle9i Block transfer waits in Oracle10g / 11g
global cache null to x gc current block 2-way
global cache s to x gc current block 3-way
global cache cr request gc cr block 2-way
gc cr block 3-way
  • From Oracle9i Release 2 onwards, V$SEGMENT_STATISTICS (collected in Statspack snapshots level 7 and above) contains buffer busy and current block transfers information per segment. In Oracle10g, V$SEGMENT_STATISTICS further qualifies busy buffers, with ‘gc buffer busy’ (global) separated from ‘buffer busy waits’ (local). Note that V$SEGMENT_STATISTICS does not include undo buffer statistics. For that reason, V$CR_BLOCK_SERVER and V$WAITSTAT should also be analyzed to distinguish between index block contention and undo block contention. We will discuss about undo block contention in coming sessions of same topic.
  • A SQL trace (event 10046) level 8 will display wait information. All buffer busy and global cache events can be mapped to the segments by P1 (file number) and P2 (block number).

Once we correlate contention to particular index segments, here are some common techniques DBAs can use to fine tune this issue.

  • Increasing sequence cache sizes
  • Reversing the index key
  • Partitioning the table (or only the index) by hash
  • Partitioning the table by list on INSTANCE_NUMBER
  • Disabling global cache defers

1 ) Increasing sequence cache sizes

Indexes with key values generated by sequences tend to be subject to leaf block contention when the insert rate is high. That is because the index leaf block holding the highest key value is changed for every row inserted, as the values are monotonically ascending. In RAC, this may lead to a high rate of current and CR blocks transferred between nodes.

Reducing inter-instance concurrent access to index leaf blocks can improve performance in those cases. One of the simplest techniques that can be used for that purpose is to increase the sequence cache.  As the difference between sequence values generated by different instances increase, successive index block splits tend to create instance affinity to index leaf blocks.

For example, let’s suppose an index key value is generated by a CACHE NOORDER sequence, and each index leaf block can hold 500 rows:

  • If the sequence cache is set to the default (20): while instance 1 inserts values like 1, 2, 3, etc., instance 2 concurrently inserts 20, 21, 22, etc. Since the difference between the values is a lot smaller than the number of rows in the block, the two instances will keep changing the same index block as sequence values increase.
  • If the sequence cache is set to 50000: while instance 1 inserts values 1, 2, 3, etc., instance 2 concurrently inserts 50001, 50002, etc. In the beginning, both instances will be writing to the same leaf block, but after some block splits this trend will change, as the difference is now equivalent to about 100 leaf blocks.

So what is the ideal value to set sequence cache to avoid inter-instance leaf index block contention, yet minimizing possible gaps? One of the main variables to consider is the insert rate; the higher it is, the higher the sequence cache must be.  In lab tests, I have seen the benefits of this technique diminish significantly with cache sizes above 50,000 for an insert rate of 2,000 rows per second. However, other factors such as the key size and the amount of concurrent query activity may also play a role, so results may vary widely for different systems. Creating a simulation to evaluate the gains for a specific configuration is recommended.

For optimal performance and to reduce gaps in sequences, it is a good practice to keep them in the shared pool, using DBMS_SHARED_POOL.KEEP(‘<sequence_name>’,’Q’).

Advantages

  • Ease of configuration, availability and manageability. No need to rebuild any segment.
  • Reduces global cache traffic as well as global buffer busy contention, since it creates block to instance affinity.

Drawbacks

  • Only applicable to indexes deriving their values from Oracle sequences.
  • Only applicable if ordering is not an issue, i.e., sequences must be configured as CACHE NOORDER.
  • Huge gaps in sequence values will happen when instances are stopped and restarted. That should not be an issue if the only purpose of the sequence is to generate a unique identifier, since the maximum possible sequence value is 10E27.

We will discuss other techniques to fine tune index contention in following sessions…

Other Sessions for this Training can be found @

Tuning indexes on RAC environments-1

Tuning indexes on RAC environments-2

Tuning indexes on RAC environments-3

Tuning indexes on RAC environments-4

Watch this place and become a tuning expert….. Subscribe by Email to get each day sessions in your inbox. Everything an Oralce Professional Needs to Cram for a Performance Optimization Job

Enter your email address:

Delivered by FeedBurner

Readers who are interested on doing hands on practice, please feel free to contact us

PrintFriendly

Viewers of this post also viewed...