Oracle RAC Performance Troubleshooting: From AWR to Root Cause
In the previous parts, we looked at:
- RAC Services
- Cache Fusion
- GCS and GES
- RAC interconnect
- Global cache waits
- Resource mastering
- Workload distribution
Now it’s time to put everything together.
A production RAC problem rarely appears as:
“The interconnect is broken.”
Instead, users usually report:
“The application is slow.”
Your job as a senior Oracle DBA is to move from that symptom to the actual root cause.
A useful RAC troubleshooting methodology is:
User Complaint
↓
Database Symptoms
↓
AWR / ASH
↓
Wait Events
↓
SQL
↓
Instance / Service
↓
RAC / Cache Fusion
↓
Network / Storage / CPU
↓
Root Cause
This article builds that methodology step by step.
1. The First Rule: Don’t Guess
One of the biggest differences between a junior and senior DBA is how they react to an incident.
Junior approach:
“CPU is high. Add CPU.”
Another common one:
“There are GC waits. The interconnect must be slow.”
Or:
“The database is slow. Increase SGA.”
These may be completely wrong.
A senior DBA starts with:
What changed? What is the evidence?
2. RAC Performance Is a Multi-Layer Problem
Think about RAC as several layers:
+----------------------+ | Application | +----------------------+ | SQL | +----------------------+ | Database Instance | +----------------------+ | RAC / Cache Fusion | +----------------------+ | Clusterware | +----------------------+ | Network | +----------------------+ | Storage / ASM | +----------------------+ | Operating System | +----------------------+ | Hardware | +----------------------+
A problem at any layer can appear as database slowness.
3. Start With the User’s Problem
Suppose the application team says:
“The application is slow today.”
That’s not enough information.
Ask:
- When did it start?
- Is everyone affected?
- Which application?
- Which transaction?
- Which database service?
- Is it always slow or intermittent?
- Is it slow on one RAC node or all nodes?
- Was anything changed?
- Is the problem still occurring?
This determines which diagnostic tools you should use.
4. Check the RAC Instances
Start with:
SELECT inst_id,
instance_name,
host_name,
status,
active_state
FROM gv$instance
ORDER BY inst_id;
Example:
INST_ID INSTANCE_NAME HOST_NAME STATUS ------- ------------- --------- ------- 1 ORCL1 RAC01 OPEN 2 ORCL2 RAC02 OPEN
If one instance is behaving differently, that is an important clue.
5. Compare Instances
Don’t automatically analyze the RAC cluster as one system.
Compare:
ORCL1 ORCL2
For example:
SELECT inst_id,
event,
total_waits,
time_waited_micro
FROM gv$system_event
WHERE wait_class <> 'Idle'
ORDER BY inst_id, time_waited_micro DESC;
You may discover:
ORCL1 → CPU dominant ORCL2 → GC dominant
That changes the investigation completely.
6. Check Services
Identify where application sessions are running:
SELECT inst_id,
service_name,
COUNT(*) AS sessions
FROM gv$session
WHERE username IS NOT NULL
GROUP BY inst_id, service_name
ORDER BY service_name, inst_id;
Example:
SERVICE ORCL1 ORCL2 ------------ ------ ------ SALES 850 120 REPORTING 20 600
This tells you immediately whether workload distribution is balanced.
But remember:
Session count is not the same as workload.
100 idle sessions don’t necessarily mean 100 active workloads.
7. Check Active Sessions
Use:
SELECT inst_id,
service_name,
COUNT(*) AS active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, service_name
ORDER BY service_name, inst_id;
This is much more useful during an incident.
8. Check the Top Wait Events
SELECT inst_id,
event,
total_waits,
ROUND(time_waited_micro / 1000000,2) AS seconds_waited
FROM gv$system_event
WHERE wait_class <> 'Idle'
ORDER BY inst_id, seconds_waited DESC;
Look for patterns.
CPU
Potential CPU saturation.
User I/O
Potential storage or SQL efficiency problem.
Concurrency
Potential contention.
Cluster
Potential RAC / Cache Fusion activity.
9. The RAC “Cluster” Wait Class
This is where RAC DBAs need to pay attention.
Examples:
gc cr request gc current request gc current block busy gc buffer busy
These don’t automatically mean the RAC infrastructure is broken.
They tell you:
Sessions are waiting for resources involving RAC global cache coordination.
Now you need to determine why.
10. AWR Is Your Starting Point
For historical incidents, AWR is one of your most important tools.
Generate an AWR report:
@?/rdbms/admin/awrrpt.sql
For RAC, use the RAC-specific report:
@?/rdbms/admin/awrgrpt.sql
The exact available scripts can depend on your Oracle installation and licensing.
For a specific instance, you can use:
@?/rdbms/admin/awrrpti.sql
The important point is to choose the report that matches the question you’re investigating.
11. What to Look at First in AWR
Don’t read the entire AWR report from page 1 to page 100.
Start with:
- DB Time
- Load Profile
- Top Foreground Events
- RAC Statistics
- SQL ordered by Elapsed Time
- SQL ordered by CPU
- SQL ordered by Gets
- SQL ordered by Reads
- Instance Activity
- I/O statistics
This gives you the overall direction.
12. DB Time
One of the most important AWR metrics is:
DB Time
Conceptually:
DB Time = CPU Time + Wait Time
If DB Time increases dramatically while transaction volume remains similar, something changed.
For example:
Normal: DB Time = 2,000 sec Incident: DB Time = 20,000 sec
That’s a major clue.
13. DB Time Is Not Wall Clock Time
Suppose:
10 sessions each consuming 100 seconds of DB Time
Total:
1,000 seconds DB Time
during a period that might be only a few minutes of wall-clock time.
Therefore:
DB Time measures database work, not elapsed wall-clock time.
This distinction is essential when interpreting AWR.
14. Find the Top Foreground Event
Suppose AWR shows:
Event Time % -------------------------------- DB CPU 55% db file sequential read 18% gc current request 15% log file sync 5%
Don’t immediately focus on gc current request.
The biggest contributor is CPU.
You need to investigate the complete workload.
15. Scenario 1 — CPU Bottleneck
Suppose:
DB CPU = 75% CPU utilization = 95%
The first question is:
Which SQL is consuming the CPU?
Run:
SELECT inst_id,
sql_id,
executions,
cpu_time,
elapsed_time,
buffer_gets
FROM gv$sql
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;
16. Find SQL With High CPU Per Execution
Total CPU can be misleading.
Calculate approximately:
SELECT inst_id,
sql_id,
executions,
cpu_time,
ROUND(cpu_time / NULLIF(executions,0), 2)
AS cpu_per_exec
FROM gv$sql
WHERE executions > 0
ORDER BY cpu_per_exec DESC
FETCH FIRST 20 ROWS ONLY;
A SQL statement with only 100 executions but enormous CPU per execution may deserve more attention than one executed millions of times.
17. Scenario 2 — Storage Bottleneck
Suppose AWR shows:
db file sequential read db file scattered read direct path read
Now investigate storage and SQL behavior.
Look at:
SELECT inst_id,
event,
total_waits,
time_waited_micro
FROM gv$system_event
WHERE wait_class = 'User I/O'
ORDER BY time_waited_micro DESC;
Then correlate with ASM and operating-system I/O.
18. Scenario 3 — RAC Bottleneck
Suppose:
gc current request gc cr request
are among the top waits.
Now don’t stop.
Continue:
GC Wait ↓ Which instance? ↓ Which SQL? ↓ Which object? ↓ Which service? ↓ Why cross-instance?
19. Find Sessions Waiting on GC
SELECT inst_id,
sid,
serial#,
username,
service_name,
event,
sql_id,
seconds_in_wait
FROM gv$session
WHERE status = 'ACTIVE'
AND event LIKE 'gc%'
ORDER BY seconds_in_wait DESC;
This gives you an active snapshot.
20. Find the SQL
Once you have the SQL ID:
SELECT sql_id,
child_number,
plan_hash_value,
executions,
elapsed_time,
cpu_time,
buffer_gets,
disk_reads
FROM gv$sql
WHERE sql_id = '&sql_id';
Now inspect the execution plan.
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&sql_id',
NULL,
'ALLSTATS LAST'
)
);
This can reveal whether the SQL itself is inefficient.
21. RAC Doesn’t Fix Bad SQL
This is an important lesson.
Suppose:
SQL performs 10 million logical reads
Adding another RAC node won’t magically make that SQL efficient.
You may simply end up with:
Instance 1 → CPU Instance 2 → CPU Instance 3 → CPU
and possibly more Cache Fusion traffic.
The correct solution may be:
- Better execution plan
- Correct indexes
- Statistics
- SQL rewrite
- Partitioning
- Application optimization
22. Scenario 4 — Hot Block
Suppose you find:
gc current block busy
This is a strong reason to investigate block contention.
A possible architecture:
ORCL1 | | UPDATE v HOT BLOCK ^ | UPDATE | ORCL2
The same block becomes a point of contention.
23. Finding Objects Behind RAC Contention
ASH is extremely useful here.
For example:
SELECT inst_id,
current_obj#,
event,
COUNT(*) AS samples
FROM gv$active_session_history
WHERE event LIKE 'gc%'
GROUP BY inst_id, current_obj#, event
ORDER BY samples DESC;
Then map the object:
SELECT owner,
object_name,
object_type
FROM dba_objects
WHERE object_id = &object_id;
Now you can potentially move from:
GC Wait
to:
TABLE X
That’s a huge improvement in diagnosis.
24. ASH Is Extremely Powerful for RAC
ASH lets you ask:
What were sessions doing during the exact period of the incident?
Example:
SELECT inst_id,
service_name,
event,
COUNT(*) AS samples
FROM gv$active_session_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-07 12:00:00'
AND TIMESTAMP '2026-08-07 12:15:00'
GROUP BY inst_id, service_name, event
ORDER BY samples DESC;
This can show which service and instance experienced the problem.
25. ASH by Instance
SELECT inst_id,
event,
COUNT(*) AS samples
FROM gv$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '30' MINUTE
GROUP BY inst_id, event
ORDER BY inst_id, samples DESC;
This helps answer:
Is the problem cluster-wide or isolated to one instance?
26. Cluster-Wide vs Instance-Specific
This distinction is extremely important.
Cluster-wide
ORCL1 → slow ORCL2 → slow ORCL3 → slow
Think about:
- Application workload
- Storage
- Shared infrastructure
- Network
- SQL
Instance-specific
ORCL1 → slow ORCL2 → normal ORCL3 → normal
Think about:
- Instance CPU
- Service placement
- Local workload
- Instance-specific network path
- Local OS issue
- Resource imbalance
27. Scenario 5 — Uneven RAC Workload
Suppose:
ORCL1 → 90% CPU ORCL2 → 25% CPU
You might think:
“RAC load balancing is broken.”
Not necessarily.
Maybe the application service is configured:
SALES_SERVICE Preferred → ORCL1 Available → ORCL2
and that’s exactly what the architecture requested.
First understand the intended design.
28. Check Service Configuration
From the OS:
srvctl config service -d ORCL
Then:
srvctl status service -d ORCL
Compare this with actual session distribution.
29. Session Distribution vs Work Distribution
This is a subtle but important point.
Suppose:
ORCL1 → 1,000 sessions ORCL2 → 500 sessions
That doesn’t necessarily mean ORCL1 is overloaded.
Maybe:
ORCL1 → mostly idle connections ORCL2 → 500 highly active sessions
Therefore always compare:
- Sessions
- Active sessions
- CPU
- DB Time
- Transactions
- SQL workload
30. Scenario 6 — Slow SQL on One RAC Node
Suppose SQL ID:
ABC123
runs on both nodes.
Results:
ORCL1: Average = 20 ms ORCL2: Average = 500 ms
This is a very valuable clue.
The SQL itself may not be the problem.
Investigate:
ORCL2 ↓ Wait events ↓ GC? I/O? CPU? Locks? Network?
31. Compare SQL Across Instances
SELECT inst_id,
sql_id,
executions,
elapsed_time,
cpu_time,
buffer_gets,
disk_reads
FROM gv$sql
WHERE sql_id = '&sql_id'
ORDER BY inst_id;
You may discover:
ORCL1: Plan hash = 123456 Buffer gets = 1,000 ORCL2: Plan hash = 987654 Buffer gets = 10,000,000
Now the issue may be an execution-plan difference.
32. Execution Plan Stability in RAC
Always check:
plan_hash_value
for problematic SQL.
If the plan differs between instances, investigate:
- Child cursors
- Optimizer environment
- Statistics
- Bind variables
- SQL Plan Management
- Adaptive behavior
RAC doesn’t guarantee that every instance will behave identically if SQL has multiple child cursors or different execution environments.
33. Scenario 7 — Interconnect Problem
Suppose:
CPU → normal Storage → normal SQL → normal GC waits → high
Now investigate:
Private NIC ↓ Errors? Drops? Latency? Utilization? ↓ Switch ↓ Congestion?
Use:
ip -s link
and:
ethtool <private_interface>
Then correlate with Oracle GC activity.
34. Scenario 8 — Storage Problem Masquerading as RAC Problem
Suppose:
gc current request
is high.
But the underlying block request may eventually depend on another instance’s ability to service the request, which itself can be affected by I/O pressure.
Therefore:
Always correlate RAC waits with CPU, memory, storage and network.
Never troubleshoot RAC in isolation.
35. The RAC Troubleshooting Decision Tree
Here’s the approach I recommend:
Application Slow
|
v
Is DB Time high?
/ \
NO YES
| |
Check outside AWR/ASH
database |
v
Top Wait / CPU
|
+-----------------+----------------+
| | |
CPU I/O RAC
| | |
SQL CPU Storage GC waits
|
+---------+---------+
| |
Interconnect Hot blocks
| |
Network SQL/Object
This is much better than changing parameters randomly.
36. A Practical Incident Workflow
During an active incident:
Step 1
Identify the affected service.
Step 2
Identify affected instances.
Step 3
Capture active sessions.
Step 4
Capture top wait events.
Step 5
Identify SQL IDs.
Step 6
Check execution plans.
Step 7
Check RAC-specific waits.
Step 8
Check interconnect.
Step 9
Check CPU/storage/OS.
Step 10
Correlate everything.
37. Capture Active Sessions
During the incident:
SELECT inst_id,
sid,
serial#,
username,
service_name,
sql_id,
event,
state,
seconds_in_wait
FROM gv$session
WHERE status = 'ACTIVE'
ORDER BY inst_id, seconds_in_wait DESC;
Save the result.
Don’t wait until the incident disappears.
38. Capture RAC Statistics
SELECT inst_id,
name,
value
FROM gv$sysstat
WHERE name LIKE 'gc%'
ORDER BY inst_id, name;
Run it again after a few minutes.
The delta is more meaningful than the absolute value.
39. Snapshot-Based Analysis
Suppose:
12:00
gc current blocks received = 1,000,000
12:10
gc current blocks received = 1,800,000
Delta:
800,000 blocks / 10 min
Now you have an actual workload rate.
This is far more useful than:
“The value is 1.8 million.”
40. A Simple RAC Diagnostic Script
You can start building your own RAC diagnostic toolkit:
SET LINESIZE 220
SET PAGESIZE 100
PROMPT ================================
PROMPT RAC INSTANCE STATUS
PROMPT ================================
SELECT inst_id,
instance_name,
host_name,
status,
active_state
FROM gv$instance
ORDER BY inst_id;
PROMPT ================================
PROMPT ACTIVE SESSIONS BY SERVICE
PROMPT ================================
SELECT inst_id,
service_name,
COUNT(*) active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, service_name
ORDER BY service_name, inst_id;
PROMPT ================================
PROMPT RAC WAIT EVENTS
PROMPT ================================
SELECT inst_id,
event,
total_waits,
ROUND(time_waited_micro/1000000,2) seconds_waited
FROM gv$system_event
WHERE event LIKE 'gc%'
ORDER BY seconds_waited DESC;
PROMPT ================================
PROMPT RAC INTERCONNECT
PROMPT ================================
SELECT inst_id,
name,
ip_address,
is_public
FROM gv$cluster_interconnects
ORDER BY inst_id;
This can become the foundation of a larger RAC health-check framework.
41. What Should You NOT Do?
Avoid blindly:
Increase SGA Increase PGA Increase processes Increase sessions Increase CPU Change GC parameters Change hidden parameters Restart instances
without evidence.
A restart can temporarily make symptoms disappear while leaving the root cause untouched.
42. A Senior DBA’s Rule
Before changing a parameter, answer:
- What problem am I solving?
- What evidence proves the parameter is related?
- What is the expected impact?
- What is the risk?
- How will I validate the result?
- What is the rollback plan?
If you cannot answer these questions, don’t change the parameter yet.
43. Production Case Study
Let’s put everything together.
A customer reports:
“The application becomes slow every morning around 09:00.”
AWR shows:
DB Time ↑ gc current request ↑ CPU = 50% Storage = normal
First thought:
RAC problem.
But ASH shows:
SALES_SERVICE ORCL1 HOT_TABLE
Further investigation shows the application starts a batch job at 09:00.
The batch job runs on ORCL1.
The OLTP application also runs on ORCL1 and ORCL2.
Both workloads modify the same table.
Result:
Batch ↓ ORCL1 ↓ HOT BLOCKS ↑ ORCL2 ↑ OLTP
Cache Fusion traffic increases dramatically.
The solution is not:
Increase CPU
Instead:
- Review service placement.
- Separate batch workload.
- Review application transaction design.
- Reduce cross-instance block contention.
- Schedule heavy batch operations appropriately.
This is what a proper RAC root-cause analysis looks like.
44. RAC Performance Golden Rules
Rule 1
Don’t diagnose from one metric.
Rule 2
Always compare RAC instances.
Rule 3
Use AWR for history and ASH for detail.
Rule 4
GC waits are symptoms, not necessarily causes.
Rule 5
Correlate SQL with services and instances.
Rule 6
Investigate hot blocks.
Rule 7
Validate the private interconnect.
Rule 8
Don’t ignore the application.
Rule 9
Don’t change hidden parameters without a documented reason.
Rule 10
Measure before and after every tuning action.
45. Expert-Level Mental Model
When you become comfortable with RAC troubleshooting, you should mentally see the environment like this:
Application
|
Service
|
+-----------+-----------+
| |
ORCL1 ORCL2
| |
Sessions Sessions
| |
+----------+------------+
|
SQL workload
|
+---------+---------+
| |
CPU Waits
|
+-------------+-------------+
| | |
I/O Locks RAC
|
+---------+---------+
| |
GCS GES
|
Cache Fusion
|
Interconnect
|
Network
This is the mental model of a RAC performance engineer.
46. Final Takeaway
Oracle RAC troubleshooting isn’t about memorizing hundreds of wait events.
It’s about learning how to connect the evidence.
A high-level methodology is:
SYMPTOM
|
↓
DB TIME
|
↓
AWR / ASH
|
↓
TOP WAIT / CPU
|
+------------+------------+
| | |
CPU I/O RAC
| | |
SQL Storage GC
|
Cache Fusion
|
Interconnect
|
Network
The most important skill is not knowing the name of every RAC wait event.
It’s knowing what question to ask next.
An expert DBA doesn’t stop at “what is waiting?” The expert asks “why is it waiting, who is causing it, and what changed?”
Practical Challenge
For your own Oracle RAC 19c environment, try to produce a one-page report containing:
RAC
SELECT inst_id,
instance_name,
host_name,
status
FROM gv$instance;
Services
srvctl status service -d ORCL
Interconnect
SELECT inst_id,
name,
ip_address,
is_public
FROM gv$cluster_interconnects;
GC waits
SELECT inst_id,
event,
total_waits,
ROUND(time_waited_micro/1000000,2) seconds_waited
FROM gv$system_event
WHERE event LIKE 'gc%';
Active sessions
SELECT inst_id,
service_name,
COUNT(*)
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, service_name;
Then answer these five questions:
- Which instance has the highest workload?
- Which service generates most of the activity?
- Are GC waits significant?
- Is workload distribution intentional?
- Is there evidence of cross-instance contention?
If you can answer those five questions from a production RAC environment, you’re moving beyond RAC administration and into RAC performance engineering.
Google+
Facebook
