You have a four-node cluster with instances RDBD1, RDBD2, RDBD3, and RDBD4. While evaluating statspack reports, you find enq: TX – index contention on page one under the category Top 5 Timed Events.
Further study finds an unusually high number of gc buffer busy waits in the Wait Events section.
Checking the Instance Activity Stats section, you see an unusually high value for Leaf Node Splits.
Using v$segment_statistics, you identify that the top segment with gc buffer busy waits is the index products_pk, which obtains the primary key from a sequence. You wish to present alternative solutions to the team to help reduce contention.
Which three actions would you recommend to reduce the overheads? (Choose three.)
A.
Encourage shorter transactions.
B.
Set _lm_msg_batch_size to a higher value.
C.
Set fast_start_mttr_target to a higher value.
D.
Re-create the index as a global hash partitioned index.
E.
Increase the sequence cache size if retaining the sequence.
F.
Re-create the table using a natural key instead of a surrogate key.