Introduction and Significance of tuning indexes in RAC environments.

Oracle E-Business environment with financial modules like AP and GL having large scale payment invoices transaction. Let me take example of one my public sector clients, they do process of 3 million payment instructions every night. Such most insert intensive applications running on RAC the frequency of current and CR block transfers for indexes is higher than for tables. That happens because automatic segment space management and freelist groups guarantee instance affinity to table blocks for INSERT statements.Traditional B-Tree index segments cannot benefit from that kind of instance affinity because the keys must be stored in order. As a consequence, two identical or similar index keys inserted by sessions connected to different instances are likely to be stored in the same index leaf block. In contrast, the rows addressed by those index keys are likely to be stored in different table blocks.

For most workloads, index block transfers should not affect scalability significantly, so that there is no need for tuning. On the other hand, in some extreme cases, high concurrency for index blocks may lead to significant RAC related waits, impacting service requirements for response times or batch processing like above example, and requiring action.

Here we are discussing tuning alternatives for those extreme cases where performance and scalability may be impacted, let me classified that in two scenario groups.

1) Globally busy buffers due to contention for index leaf and branch blocks :-
Globally busy buffers may become one of the main bottlenecks if the insert frequency and degree of concurrency in a multi-instance is high.

2) Excessive undo block shipment and contention for undo buffers :-
That usually happens when index blocks containing active transactions from multiple instances are read frequently.Such cases undo information from multiple instances may need to be collected to create consistent read versions.

These two are real practical cases usually DBAs ignore. In any kind of performance tuning choosing appropriate tuning techniques depend on workload characteristics and system constraints. Again experience matters, these criteria are based on our experience with several production systems, benchmarks and smaller scale tests. Some of our guidelines may not be equally effective for all kinds of applications, since different workloads may behave differently.

Reference: NoteID 760300.1

In the next sections we will discuss tuning alternatives for above mentioned scenarios

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.

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...