Block transfers between RAC instances

In RAC, each instance has its own buffer cache. When the session needs a block whose version is in another instance, it waits for the transfer over the interconnect: these are the gc events. A lot of time in them means the instances are contending for the same blocks.

Symptoms

In the Diagnosis, the finding Block transfers between RAC instances (CLUSTER) appears. The text says how much of the total time went to the Cluster wait class, as a percentage and in seconds, lists the events of the class that weighed the most and ends with View profile by class.

The finding appears when the Cluster class weighs heavily on the total time: in RAC, some block transfer is normal. The severity is always medium. The gc events belong to the class, such as gc cr block 2-way, gc current block busy and gc buffer busy acquire.

The card of each gc event, which opens when you click its name in the profile, explains the names: cr is a consistent read and current is the current version of the block, to change it; 2-way and 3-way count the instances involved; grant is the permission to read from disk; busy means the block was in use on the other node.

Cause

• The same part of the application runs on more than one instance and changes the same blocks: each change takes the block from one instance to the other.
• Hot blocks: an index with an increasing key (sequence, date), a sequence with order or a small cache, a small control table that every session updates.
• Slow interconnect or overloaded remote instance: each transfer takes long, and the events with busy and congested in the name weigh the most.

Resolution

See which events weigh and how long each transfer takes

In the profile, compare Occurrences and Avg (s) of the gc events. The event card considers little time per block normal, under 1 ms with a good interconnect. Many fast transfers point to contention for the same blocks; few slow transfers, to the interconnect or to the load on the other instance.

Keep the service on one instance

The Session and environment card, in the Summary, shows the trace Instance and Service. See which instances hold the sessions of each service:

select service_name, inst_id, count(*) as sessoes
  from gv$session
 where type = 'USER'
 group by service_name, inst_id
 order by service_name, inst_id;

If the same service runs on all instances and its sessions change the same tables, configure the service with one preferred instance and the others as available: the load stays on one instance, and the others take over only on failure. The Oracle documentation on services in RAC describes the configuration.

Break up the hot blocks

• Sequence: larger cache and noorder, so that each instance uses its own range of numbers (alter sequence exemplo.pedido_seq cache 1000 noorder;).
• Index with an increasing key: reverse key index or hash-partitioned index, as in concurrent inserts into the same block.
• Small table that everyone updates: split the rows by instance or by session.

Interconnect and remote instance

With a high average per transfer, the problem is outside the session: the interconnect network or the CPU of the other instance, where the LMS processes serve the block requests. Take the average of the gc events and the trace Period to the DBA.

How to confirm

1. Apply one change at a time: service with a preferred instance, sequence with cache or new index.
2. Turn on tracing for the same operation with waits, on the instance where it now runs (turn on tracing with DBMS_MONITOR).
3. Upload the new file and open the report.
4. Compare the Cluster row in By wait class and the gc events in the profile.

Result: the Cluster class no longer weighs on the total time and the finding disappears from the Diagnosis. With the load on a single instance, the Occurrences of the gc events drop; with the interconnect fixed, the Avg (s) drops.

In the report

The sample report has no Cluster class waits; the links open the same sections in it.

• Summary: the Session and environment card, with Instance and Service (summary).
• Diagnosis: the finding, with the View profile by class link.
• By wait class: the Cluster row, with the gc events in Top components (wait classes).
• Response time profile: each gc event, with Occurrences and Avg (s), and the Block from another instance (RAC) card (profile).
• Objects with the most wait time: the tables and indexes transferred between the instances (objects).
• Top statements by component and by class: the statements that waited the most for blocks from another instance (statements by component).

In a collection with sessions from different instances, the Collection sessions table shows the instance of each session (collection).