Performance tuning in Oracle Real Application Clusters (RAC) is essential for ensuring optimal performance across nodes, reducing bottlenecks, and maintaining scalability. This chapter provides a detailed guide with commands and examples to help you identify and resolve performance bottlenecks, tune cache fusion, optimize interconnect performance, and apply best practices for RAC and Clusterware tuning.

---

1. Identifying Performance Bottlenecks in RAC Environments

Identifying performance bottlenecks in RAC typically involves analyzing system-wide reports, monitoring sessions, and checking critical wait events. AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor), and dynamic performance views are essential tools.

Commands to Generate AWR Reports:

• Generating an AWR Report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Follow the prompts to specify the beginning and ending snapshots for the report.

Checking Session Wait Events: Use the gv$session_wait view to identify session-level waits:

SELECT event, wait_time, wait_class, session_id
FROM gv$session_wait
WHERE wait_time > 0;

Identifying Bottlenecks by Load:

SELECT inst_id, event, total_waits, time_waited
FROM gv$system_event
WHERE event LIKE 'gc%' OR event LIKE 'db file%' OR event LIKE 'log file%';

2. Understanding Wait Events in Oracle RAC

Wait events help pinpoint areas where the system experiences delays. In RAC environments, critical wait events include Global Cache waits and enqueues.

• Global Cache Wait Events: These occur when an instance waits for data blocks from another node.

SELECT inst_id, event, total_waits, time_waited
FROM gv$system_event
WHERE event LIKE 'gc%';

Key wait events to look for include:

• gc current block busy: Indicates contention for current blocks.
• gc cr block busy: Indicates contention for consistent read blocks.

Enqueue Waits: Enqueues are locks that synchronize access to shared resources.

SELECT event, total_waits, time_waited
FROM gv$enqueue_stats;

3. Tuning Cache Fusion and Global Cache Latency

Cache Fusion is the mechanism used by Oracle RAC to transfer data blocks between instances. Reducing Cache Fusion latency is critical for optimal RAC performance.

Steps to Tune Cache Fusion:

• Monitor Cache Fusion Waits:

SELECT inst_id, event, total_waits, time_waited
FROM gv$session_event
WHERE event LIKE 'gc%';

Example to Adjust Cache Fusion Settings:

• Increase LMS processes if inter-instance block transfers are high.

ALTER SYSTEM SET LMS_PROCESSES=4 SCOPE=BOTH;

Balance Workloads: Reduce inter-instance communication by using services to balance workloads.

srvctl add service -d racdb -s oltp_service -r racnode1,racnode2
srvctl start service -d racdb -s oltp_service

Example to Monitor Cache Transfers:

SELECT inst_id, block_transfers, waits
FROM gv$cache_transfer;

4. Optimizing Interconnect Performance for RAC

The interconnect network is critical for RAC performance as it facilitates Cache Fusion and node-to-node communication. Optimizing the interconnect ensures that data transfers between RAC nodes are fast and efficient.

Best Practices:

• Use a high-speed, low-latency interconnect such as InfiniBand or 10/25 Gb Ethernet.
• Configure private interconnects dedicated to RAC traffic to avoid congestion.

Commands to Check Interconnect Performance:

• View Interconnect Latency:

SELECT inst_id, interconnect_latency, interconnect_blocks_received
FROM gv$cache_transfer;

Checking Network Statistics: Use the oswatcher tool to monitor network performance over time.

oswatcher.sh

Set TCP/IP Parameters for Optimization (on Linux): Add the following lines to /etc/sysctl.conf to tune the TCP stack for better RAC performance:

net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 87380 16777216

Then apply changes:

sysctl -p

5. Best Practices for Tuning RAC and Clusterware Performance

Ensuring optimal performance in RAC requires tuning both the RAC instances and the Oracle Clusterware. Following best practices can prevent bottlenecks and ensure high availability.

Load Balancing:

• Distribute workloads evenly across RAC nodes using services and SCAN (Single Client Access Name).

srvctl add service -d racdb -s finance_service -r racnode1,racnode2 -a racnode3
srvctl start service -d racdb -s finance_service

Monitor Wait Events:

• Focus on reducing critical wait events (such as Global Cache waits and enqueues) by analyzing AWR and ADDM reports.
• Generate an ADDM Report:

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Tune the Interconnect:

• Use the best network hardware and ensure that network configuration is optimized for RAC.
• Monitor RAC Interconnect with ifconfig (Linux):

ifconfig eth0

Optimize SQL Queries:

• Poorly optimized SQL queries can impact overall RAC performance. Regularly analyze SQL execution plans using SQLT or SQL tuning advisor.
• Generate a SQL Plan:

EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_id = 1234;
SELECT * FROM table(dbms_xplan.display);

Clusterware Performance Tuning:

• Monitor Oracle Clusterware resources to ensure they are running efficiently.

crsctl status resource -t

Restart a Resource if needed:

crsctl stop resource ora.racdb.db
crsctl start resource ora.racdb.db

Monitor Resource Load:

• Use GV$ views to monitor resource usage across RAC nodes

SELECT inst_id, cpu_usage, memory_usage
FROM gv$instance_resource_usage;

By following these practices and using the commands provided, you can effectively identify and resolve performance bottlenecks, tune cache fusion and interconnect performance, and ensure smooth operation of Oracle RAC and Clusterware. Regular monitoring and proactive tuning are essential to maintaining a high-performing and resilient RAC environment.