转 Index Leaf Block contention Tuning

原址如下：

http://www.dba-oracle.com/t_index_leaf_block_contention_tuning.htm

 

Index Leaf Block contention Tuning

Oracle Tips by Burleson Consulting

March 22, 2010

Index block contention is very common in busy databases and it’s especially common on tables that have monotonically increasing key values.

In a high-DML system, index management activities are constantly taking place (b-tree splitting and spawning) and these events can cause transient waits, but far and away, the greatest cause of index contention in an Oracle environment is the “high key” issue.  Oracle b-tree indexes are “right-handed” and the right-hand leafs of the b-tree contain the highest key in the lowest tree level.

Index leaf node contention happens when rows are inserted based on a user generated key (i.e. a sequence) and because the sequence key is always the high order key, each insert of a low-level index tree node must propagate upwards to the high-key indicators in the b-tree index. 

Detecting index leaf block contention

Index block contention can be tricky to diagnose and repair, especially since Oracle does not have many direct tuning knobs for tuning for contention.

The wait “enq: TX – index contention” indicates a wait on an index, and in a RAC environment you might also see “gc” (global cache) waits on indexes:

• gc buffer busy waits on Index Branch Blocks
• gc buffer busy waits on Index Leaf Blocks
• gc current block busy on Remote Undo Headers
• gc current split
• gcs ast xid
• gcs refuse xid

There are many ways to monitor for enqueue contention, including STATSPACK or AWR reports, v$session, dba_hist_enqueue_stat and the stats$enqueuestat table.  Here is a very simple script to detect index enqueue waits:

select 
   sid, 
   sql_text 
from 
   v$session s, 
   v$sql q 
where 
   sid in (
      select sid from v$session where state in ('WAITING') 
   and 
      wait_class != 'Idle' 
   and 
      event='enq: TX - index contention' 
   and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)
);

The details enqueue waits scripts are quite complex, but you can run scripts to detect enqueue waits.  Once you have seen index leaf block contention, there are several techniques to reduce this contention.  Let’s look at various approaches to relieve index block contention.

Fixing Oracle index contention

There are three techniques that are used to relieve this index contention issue:

·         Reverse key indexes

·         Sequences with the cache and noorder options

·         Using hash partitioned global indexes

·         Adjusting the index block size

Let’s look at each approach to reducing index leaf block contention.

Reverse Key indexes to reduce index contention

Using reverse-key indexes will speed-up Oracle insert statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table).  For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed. 

In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.  With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed. 

An Oracle reverse key index does not change the functionality of the index.  It's not the same as actually reversing the index key values and it only reverses the internal entries in the index to relieve index block contention.

In Oracle RAC, reverse-key indexes are especially important because they also reduce "index hot spots" because they reverse the bytes within the index leaf block, removing inter-node contention for a shared leaf block.

Sequences with the noorder and cache options to reduce contention

To reduce index contention for RAC environments you may also want to make sure that all sequences that appear in indexes is using the default “noorder” option and they you are using the cache option. 

If there is insufficient caching of sequences, contention can result which will show up as an increase in service times for DML. If there are performance problems due to sequence cache waits, examine the row cache locks statistics in the v$system_eventview to determine whether the problem is due to the use of Oracle sequences.

When creating sequences for a RAC environment, DBAs should use the noorder keyword to avoid an additional cause of SQ enqueue contention that is forced ordering of queued sequence values.

In RAC, you can see sequence-related index enqueue delays in the eq_type column of the gv$enqueue_stat view.  A value of "SQ Enqueue" indicates that there is contention for sequences. In almost all cases, executing an alter sequence command can increase the cache size of sequences used by the application.

Hash partitioned global indexes to relieve index contention

Starting in Oracle 10g we got the hash partitioned global index, the idea that an index tree can be partitioned in the same fashion as a partitioned table. 

In a hash-partitioned global index, each partition contains the values determined by Oracle’s hashing algorithm, a software function that take a index symbolic key and generates unique values with blistering fast speed.

When an index is monotonically growing because of a sequence or date key, global hash-partitioned indexes improve performance by spreading out the contention. Thus, hash-partitioned global indexes can improve the performance of indexes in which a small number of leaf blocks in the index have high contention in multi-user OLTP environments.

Adjusting the index block size to relieve index contention

The physical block size for an index matters because Oracle chose the block size boundaries to be the same as the b-tree node size.  Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace.

As a general rule, RAC likes smaller block sizes, and a database with 2k blocksizes will have less overhead at the cache fusion layer when blocks are transported to other RAC nodes.

For indexes, a smaller blocksize means that there will be fewer keys per block, and fewer keys per block can reduce index leaf block contention by reducing the likelihood that any two tasks will be waiting for the same block.

Note:  Adjusting the index block size may only produce a small effect (1%-20% improvement) and changing the index block size should never be your first choice for reducing index block contention, and it should only be done after the other approaches have been examined.

Conclusions on tuning for index leaf block contention

In a table that have a generated or a sequential key, insert DML can cause contention in the index leaf blocks, especially when the primary key is based on a monotonically increasing value such as a date or a sequence.  There are many ways to relieve index leaf block contention, but you should only undertake these tuning approaches when you see wait events that are directly related to index block contention.

 

 还有一个连接：

http://orachat.com/tuning-indexes-rac-2/

Tuning indexes on RAC environments-2

 

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 Oracle9iBlock contention waits in Oracle10g /11g
buffer busy global cachegc buffer busyglobal cache busygc current block busybuffer busy global CRgc current splitenqueue, with further investigation leading to TX enqueue waits in share modeenq: 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 Oracle9iBlock transfer waits in Oracle10g / 11gglobal cache null to xgc current block 2-wayglobal cache s to xgc current block 3-wayglobal cache cr requestgc cr block 2-waygc 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 andV$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