RAC Current Block Receive Analysis (gvcrrec.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes RAC Global Cache Service (GCS) performance by:

• Monitoring current block receive times across all instances
• Calculating average receive time per current block transfer
• Identifying potential cluster interconnect performance issues
• Providing instance-by-instance cluster communication analysis

Script

rem  gvcrrec.sql
column "AVG RECEIVE TIME (ms)" format 9999999.9
col inst_id for 9999
prompt GCS CURRENT BLOCKS
select b1.inst_id, b2.value "RECEIVED",
b1.value "RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'gc current block receive time' and
b2.name = 'gc current blocks received' and b1.inst_id = b2.inst_id
/

Usage

SQL> @gvcrrec.sql

Required Privileges

• SELECT on GV$SYSSTAT

Sample Output

GCS CURRENT BLOCKS
INST_ID   RECEIVED RECEIVE TIME AVG RECEIVE TIME (ms)
------- ---------- ------------ --------------------
      1  1,234,567   12,345,670                100.0
      2  1,456,789   13,567,890                 93.1
      3  1,678,901   15,789,010                 94.0
      4  1,890,123   17,901,230                 94.7

Key Output Columns

• INST_ID: RAC instance number
• RECEIVED: Total number of current blocks received from other instances
• RECEIVE TIME: Total time spent receiving current blocks (in centiseconds)
• AVG RECEIVE TIME (ms): Average time per current block receive operation in milliseconds

Understanding RAC Global Cache Service

Current Blocks vs. Consistent Read Blocks

• Current Blocks: Most recent version of data blocks for DML operations
• Usage: Required for INSERT, UPDATE, DELETE operations
• Transfer: Moved between instances when data modification needed

Performance Implications

• Low Receive Times: Efficient cluster interconnect
• High Receive Times: Network latency or bandwidth issues
• Variance Between Instances: Potential configuration problems

Performance Baselines

Acceptable Performance Ranges

• Excellent: < 5ms average receive time
• Good: 5-15ms average receive time
• Warning: 15-30ms average receive time
• Critical: > 30ms average receive time

Network Type Expectations

• InfiniBand: 1-5ms typical
• Gigabit Ethernet: 5-15ms typical
• Fast Ethernet: 15-30ms typical

Analysis and Troubleshooting

High Receive Times Diagnosis

-- Check interconnect configuration
SELECT inst_id, name, value
FROM gv$parameter
WHERE name IN ('cluster_interconnects', 'cluster_interconnect_retries')
ORDER BY inst_id;

-- Compare with consistent read block times
SELECT b1.inst_id,
       ROUND((b1.value / b2.value) * 10, 2) "CR AVG TIME (ms)",
       ROUND((c1.value / c2.value) * 10, 2) "CURRENT AVG TIME (ms)"
FROM gv$sysstat b1, gv$sysstat b2, gv$sysstat c1, gv$sysstat c2
WHERE b1.name = 'gc cr block receive time'
AND b2.name = 'gc cr blocks received'
AND c1.name = 'gc current block receive time'
AND c2.name = 'gc current blocks received'
AND b1.inst_id = b2.inst_id
AND c1.inst_id = c2.inst_id
AND b1.inst_id = c1.inst_id
ORDER BY b1.inst_id;

Instance Imbalance Analysis

-- Check for uneven workload distribution
SELECT inst_id,
       ROUND(value/1024/1024, 2) current_blocks_mb
FROM gv$sysstat
WHERE name = 'gc current blocks received'
ORDER BY value DESC;

Common Performance Issues

Network Bottlenecks

• Symptoms: Consistently high receive times across instances
• Causes: Insufficient bandwidth, network congestion
• Solutions: Upgrade network hardware, optimize interconnect

Interconnect Configuration

• Symptoms: Variable performance between instances
• Causes: Asymmetric network paths, configuration mismatches
• Solutions: Review network topology, balance connections

Application Design

• Symptoms: Excessive current block transfers
• Causes: Poor data locality, frequent cross-instance DML
• Solutions: Partition data, optimize application design

Cluster Interconnect Optimization

Network Configuration

-- Check current interconnect settings
SELECT name, value, description
FROM v$parameter
WHERE name LIKE '%interconnect%'
OR name LIKE '%cluster%';

Monitoring Commands

-- Monitor interconnect utilization
SELECT inst_id, name, value
FROM gv$sysstat
WHERE name LIKE 'gc%time%'
OR name LIKE 'gc%received'
ORDER BY inst_id, name;

Historical Analysis

Trending Over Time

-- Create monitoring table for trends
CREATE TABLE gc_performance_history (
    check_time DATE,
    inst_id NUMBER,
    blocks_received NUMBER,
    total_time_cs NUMBER,
    avg_time_ms NUMBER
);

-- Insert current values
INSERT INTO gc_performance_history
SELECT SYSDATE, b1.inst_id, b2.value,
       b1.value, ((b1.value / b2.value) * 10)
FROM gv$sysstat b1, gv$sysstat b2
WHERE b1.name = 'gc current block receive time'
AND b2.name = 'gc current blocks received'
AND b1.inst_id = b2.inst_id;

Performance Comparison

-- Compare current vs. previous day
SELECT inst_id,
       current_avg_ms,
       previous_avg_ms,
       ROUND(((current_avg_ms - previous_avg_ms) / previous_avg_ms) * 100, 2) pct_change
FROM (
    SELECT inst_id,
           AVG(CASE WHEN check_time >= TRUNC(SYSDATE) THEN avg_time_ms END) current_avg_ms,
           AVG(CASE WHEN check_time >= TRUNC(SYSDATE-1) AND check_time < TRUNC(SYSDATE) THEN avg_time_ms END) previous_avg_ms
    FROM gc_performance_history
    WHERE check_time >= TRUNC(SYSDATE-1)
    GROUP BY inst_id
);

RAC-Specific Monitoring

Global Cache Statistics

-- Comprehensive RAC cache statistics
SELECT name, SUM(value) total_value
FROM gv$sysstat
WHERE name LIKE 'gc %block%'
GROUP BY name
ORDER BY name;

Wait Event Analysis

-- Check RAC-related wait events
SELECT event, total_waits, time_waited,
       ROUND(time_waited/total_waits, 2) avg_wait_ms
FROM v$system_event
WHERE event LIKE 'gc %'
AND total_waits > 0
ORDER BY time_waited DESC;

Alerting and Thresholds

Performance Alerts

-- Create alert for high receive times
SELECT inst_id, avg_receive_time_ms,
       CASE
         WHEN avg_receive_time_ms > 30 THEN 'CRITICAL'
         WHEN avg_receive_time_ms > 15 THEN 'WARNING'
         ELSE 'OK'
       END alert_level
FROM (
    SELECT b1.inst_id, ((b1.value / b2.value) * 10) avg_receive_time_ms
    FROM gv$sysstat b1, gv$sysstat b2
    WHERE b1.name = 'gc current block receive time'
    AND b2.name = 'gc current blocks received'
    AND b1.inst_id = b2.inst_id
    AND b2.value > 0
);

Integration with Other Metrics

Complete RAC Health Check

-- Combine with other RAC metrics
SELECT m.inst_id,
       ROUND(m.avg_receive_time_ms, 2) current_block_ms,
       ROUND(c.avg_receive_time_ms, 2) cr_block_ms,
       ROUND(s.cpu_used_by_this_session/100, 2) cpu_seconds
FROM (
    -- Current block times
    SELECT b1.inst_id, ((b1.value / b2.value) * 10) avg_receive_time_ms
    FROM gv$sysstat b1, gv$sysstat b2
    WHERE b1.name = 'gc current block receive time'
    AND b2.name = 'gc current blocks received'
    AND b1.inst_id = b2.inst_id
) m,
(
    -- CR block times
    SELECT b1.inst_id, ((b1.value / b2.value) * 10) avg_receive_time_ms
    FROM gv$sysstat b1, gv$sysstat b2
    WHERE b1.name = 'gc cr block receive time'
    AND b2.name = 'gc cr blocks received'
    AND b1.inst_id = b2.inst_id
) c,
(
    -- CPU usage
    SELECT inst_id, value cpu_used_by_this_session
    FROM gv$sysstat
    WHERE name = 'CPU used by this session'
) s
WHERE m.inst_id = c.inst_id
AND m.inst_id = s.inst_id
ORDER BY m.inst_id;

Best Practices

1. Regular Monitoring

   • Check during peak hours
   • Establish baseline performance
   • Monitor trends over time

2. Threshold Management

   • Set environment-specific alerts
   • Account for network hardware differences
   • Document performance expectations

3. Proactive Analysis

   • Correlate with application performance
   • Monitor during maintenance windows
   • Track after configuration changes

Related Scripts

• RAC Session Analysis
• RAC Lock Analysis
• Interconnect Health