Your RAC database has a high volume of inserts into the SALES table. Sequence SALES_SEQ is used to generate primary key values. Examine the following query output: SQL> SELECT sequence_name…

Question

Exhibit

Options

• AIncreasing size of undo tablespaces
• Bincreasing sequence cache sizes
• Cpending
• Dpending

• A
  6% (2)
• B
  71% (25)
• C
  6% (2)
• D
  17% (6)

Explanation

Oracle Sequences and Index Contention 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. One of the simplest techniques that can be used to limit this overhead is to increase the sequence cache, if you are using Oracle sequences. Because the difference between sequence values generated by different instances increases, successive index block splits tend to create instance affinity to index leaf blocks. For example, suppose that 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 50000, while instance 1 inserts values 1, 2, 3, and so on, instance 2 concurrently inserts 50001, 50002, and so on. After some block splits, each instance writes to a different part of the So, what is the ideal value for a 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 must be the sequence cache. However, creating a simulation to evaluate the gains for a specific configuration is recommended. Note: By default, the cache value is 20. Typically, 20 is too small for the preceding example. Oracle 11g: RAC and Grid Infrastructure Administration Accelerated 14 ?24

No community discussion yet for this question.