Part 21 introduced Resource Manager and workload control.
Now we’re going into one of the areas that separates a normal Oracle DBA from a strong RAC DBA:
Diagnosing RAC performance problems from the wait event all the way down to the SQL, object, block, and application design.
The objective is not to memorize gc_* wait events.
The objective is to learn a repeatable troubleshooting method.
1. The RAC Performance Problem
An application says:
“The database is slow.”
That’s not a diagnosis.
In RAC, we need to determine:
Application
↓
Service
↓
Instance
↓
Session
↓
SQL_ID
↓
Wait event
↓
Object
↓
Block
↓
Root cause
For example:
Application ↓ SALES_APP ↓ RAC2 ↓ SQL_ID = 8a12abc ↓ gc current request ↓ SALES.ORDERS ↓ Hot blocks ↓ Application concurrency
That’s an expert-level diagnosis.
2. Why RAC Performance Is Different
In a single-instance database:
Session ↓ Buffer Cache ↓ Block
In RAC:
Session on RAC1
|
↓
RAC1 Buffer Cache
|
X
|
↓
RAC2 owns the block
|
↓
Cache Fusion
|
↓
Interconnect
So a SQL statement can be slow because the required block is not locally available.
3. Cache Fusion Refresher
Suppose a block is currently cached on RAC1.
RAC1 | +-- Block 100
A session on RAC2 needs that block.
Oracle may need to transfer the block through the RAC interconnect:
RAC2 | | Request ↓ Interconnect | ↓ RAC1 | | Block ↓ Interconnect | ↓ RAC2
This is Cache Fusion.
4. Two Important Types of Blocks
For RAC troubleshooting, understand:
Current block
The latest version of a block that can be modified.
Consistent-read block
A version used to satisfy consistent-read requirements.
That’s why you’ll frequently see events beginning with:
gc current
and:
gc cr
5. The Most Important GC Wait Events
You should become comfortable with:
gc current request gc current block busy gc current block 2-way gc current block 3-way gc cr request gc cr block busy gc cr 2-way gc cr 3-way
The exact event names and statistics should always be interpreted according to the Oracle 19c release and the specific RAC workload.
6. gc current request
This means a session needs a current block and is waiting for RAC-related processing to obtain it.
Conceptually:
Session RAC2
|
↓
Need current block
|
↓
Block is remote
|
↓
Cache Fusion
|
↓
Wait
The important question is:
Why does this workload repeatedly need blocks from another instance?
7. gc current block busy
This is particularly interesting.
A session requests a current block, but the block is busy in another instance.
Conceptually:
RAC1
|
+-- Block X
|
+-- Being modified
RAC2
|
+-- Requests Block X
|
+-- waits
This can point toward:
- Hot blocks
- Concurrent updates
- Poor workload locality
- Application contention
8. gc cr request
Now we’re dealing with consistent-read block requests.
Conceptually:
RAC2
|
+-- SQL needs CR block
|
↓
RAC1
|
↓
Cache Fusion
The same principle applies:
Determine why the block is remote and whether the workload can be improved.
9. Don’t Panic When You See GC Waits
This is one of the most important RAC lessons.
Seeing:
gc current request
doesn’t automatically mean:
“RAC is broken.”
Some RAC workloads naturally generate global cache traffic.
The real questions are:
How much? Which SQL? Which object? Which instance? How often? What latency? Is application performance affected?
10. Start With AWR
When investigating a historical incident, start with AWR.
For example:
SELECT snap_id,
begin_interval_time,
end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 20 ROWS ONLY;
Identify the problematic interval.
Then generate an AWR report for:
begin_snap end_snap instance
In RAC, use the appropriate RAC/global AWR report when you need a cluster-wide view.
11. RAC AWR Is Extremely Important
A single-instance report may hide the relationship between:
RAC1
and:
RAC2
For RAC incidents, you want to understand:
Instance 1 Instance 2 ...
together.
Look for:
- DB Time
- DB CPU
- Top Foreground Events
- Global Cache statistics
- SQL ordered by elapsed time
- SQL ordered by CPU
- SQL ordered by gets
- Instance activity
12. Start With Wait Classes
A useful first query:
SELECT inst_id,
wait_class,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, wait_class
ORDER BY inst_id, sessions DESC;
This tells you whether the active workload is primarily:
CPU User I/O Concurrency Cluster Commit Network System I/O
13. Look Specifically at Cluster Waits
SELECT inst_id,
event,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND wait_class = 'Cluster'
GROUP BY inst_id, event
ORDER BY sessions DESC;
Example:
INST EVENT SESSIONS ---- ----------------------- -------- 2 gc current request 28 2 gc cr request 15 1 gc current block busy 8
Now we know:
RAC2 has significant cluster-related waits.
But we’re not finished.
14. Find the SQL
For active sessions:
SELECT inst_id,
sid,
serial#,
username,
service_name,
sql_id,
event,
seconds_in_wait
FROM gv$session
WHERE status = 'ACTIVE'
AND wait_class = 'Cluster'
ORDER BY seconds_in_wait DESC;
Now we can associate:
WAIT EVENT
↓
SESSION
↓
SQL_ID
15. Find SQL Text
For SQL still in memory:
SELECT inst_id,
sql_id,
child_number,
plan_hash_value,
executions,
elapsed_time/1000000 elapsed_seconds,
cpu_time/1000000 cpu_seconds,
buffer_gets,
disk_reads
FROM gv$sql
WHERE sql_id = :sql_id
ORDER BY inst_id, child_number;
Then:
SELECT sql_id,
sql_text
FROM v$sql
WHERE sql_id = :sql_id;
For historical incidents, use AWR SQL views if the cursor is no longer in the shared pool.
16. ASH Is Even More Powerful
ASH records active session samples.
Start with:
SELECT sample_time,
instance_number,
session_id,
session_serial#,
sql_id,
event,
wait_class,
service_name
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND wait_class = 'Cluster'
ORDER BY sample_time;
Now you can reconstruct the incident.
17. Group GC Waits by SQL
SELECT sql_id,
event,
instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND event LIKE 'gc %'
GROUP BY sql_id,
event,
instance_number
ORDER BY samples DESC;
Example:
SQL_ID EVENT INSTANCE SAMPLES -------- -------------------- -------- ------- 8a12abc gc current request 2 4200 8a12abc gc current block busy 2 1800 91bc123 gc cr request 1 900
Now the investigation becomes much more focused.
18. Add the Service
SELECT service_name,
sql_id,
event,
instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND event LIKE 'gc %'
GROUP BY service_name,
sql_id,
event,
instance_number
ORDER BY samples DESC;
You might discover:
SALES_APP
↓
SQL 8a12abc
↓
RAC2
↓
gc current request
That is much more useful than simply saying:
“RAC has GC waits.”
19. Add the Object
Now we want to identify the object associated with the current object number:
SELECT ash.service_name,
ash.instance_number,
ash.sql_id,
ash.event,
o.owner,
o.object_name,
o.object_type,
COUNT(*) samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects o
ON o.object_id = ash.current_obj#
WHERE ash.sample_time BETWEEN
:start_time AND :end_time
AND ash.event LIKE 'gc %'
GROUP BY ash.service_name,
ash.instance_number,
ash.sql_id,
ash.event,
o.owner,
o.object_name,
o.object_type
ORDER BY samples DESC
FETCH FIRST 50 ROWS ONLY;
This is one of the most useful queries in this entire series.
20. Example Result
Imagine:
SERVICE INST SQL_ID EVENT OBJECT --------- ---- ------- --------------------- ---------------- SALES_APP 2 8a12abc gc current request SALES.ORDERS SALES_APP 2 8a12abc gc current block busy SALES.ORDERS
Now we have:
Application
↓
SALES_APP
↓
RAC2
↓
SQL 8a12abc
↓
SALES.ORDERS
↓
GC contention
That’s an actual troubleshooting chain.
21. The Next Question: Which Blocks?
Finding the object is not necessarily enough.
You may have:
SALES.ORDERS
with millions of blocks.
We need to determine whether a particular subset of blocks is hot.
This is where block-level analysis becomes important.
22. Hot Blocks
A hot block is a block accessed concurrently at a high rate, often causing contention.
In RAC, a hot block can become especially problematic when multiple instances repeatedly need to modify it.
Example:
RAC1 RAC2
| |
| update block X |
| |
+-------- X ------------+
|
Cache Fusion
|
+-------- X ------------+
| |
| update block X |
| |
The same block keeps moving between instances.
23. The Classic RAC Hot Block Pattern
Imagine:
Sequence table
or:
Small lookup/control table
with one frequently updated row.
Then:
RAC1 → UPDATE row RAC2 → UPDATE row RAC1 → UPDATE row RAC2 → UPDATE row
The block containing that row becomes a battlefield.
RAC1 ←→ Block ←→ RAC2
This can create significant global cache traffic.
24. How to Investigate Hot Blocks
Start with ASH:
SELECT instance_number,
current_obj#,
current_file#,
current_block#,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND event LIKE 'gc %'
GROUP BY instance_number,
current_obj#,
current_file#,
current_block#,
event
ORDER BY samples DESC
FETCH FIRST 50 ROWS ONLY;
Now you can identify candidate blocks.
25. Map Block to Object
If you have:
CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#
you can investigate the segment.
For example:
SELECT owner,
segment_name,
segment_type,
tablespace_name,
file_id,
block_id,
blocks
FROM dba_extents
WHERE file_id = :file_id
AND :block_id BETWEEN block_id AND block_id + blocks - 1;
This is useful for locating the segment containing the block.
26. Why Hot Blocks Happen
Typical causes include:
1. Monotonic indexes
For example:
sequence-generated IDs
can concentrate inserts toward the right-hand side of an index.
2. Frequently updated rows
status row counter control row
3. Small lookup/control tables
Many sessions repeatedly update the same blocks.
4. Poor workload locality
RAC1 and RAC2 constantly modify the same data.
5. Application design
The application may create unnecessary serialization.
27. Index Hot Blocks
Consider:
INSERT INTO orders(order_id, ...) VALUES(order_seq.NEXTVAL, ...);
With a normal B-tree index:
ORDER_ID | ↓ Index | +--- rightmost leaf block
New values are continuously increasing.
A high-concurrency insert workload can concentrate activity around the right side of the index.
In RAC, this can become more interesting when inserts occur across multiple instances.
28. But Don’t Automatically Use Reverse Key Indexes
You may hear:
“Hot index block? Use a reverse key index.”
That’s too simplistic.
Reverse key indexes can reduce contention for certain monotonically increasing key workloads, but they have trade-offs.
For example, reverse key indexes are generally unsuitable when you need efficient range scans on the indexed column.
Always investigate the access pattern first.
29. Hot Table Blocks
Another example:
UPDATE application_control SET last_value = last_value + 1 WHERE id = 1;
If thousands of sessions do:
UPDATE same rowyou have an obvious serialization point.
In RAC:
RAC1 ↓ Block X RAC2 ↓ Block X RAC1 ↓ Block X
The problem is not RAC itself.
The problem is the application design.
30. RAC Can Expose Application Design Problems
This is a very important principle.
A single-instance database may hide some contention because:
All sessions
↓
One buffer cache
RAC makes ownership movement visible:
RAC1 buffer cache
↕
Cache Fusion
↕
RAC2 buffer cache
Therefore:
RAC performance tuning often requires application and data-model tuning.
31. Find Objects With High GC Activity
A practical query:
SELECT o.owner,
o.object_name,
o.object_type,
ash.event,
COUNT(*) samples
FROM dba_hist_active_sess_history ash
JOIN dba_objects o
ON o.object_id = ash.current_obj#
WHERE ash.sample_time BETWEEN
:start_time AND :end_time
AND ash.event LIKE 'gc %'
GROUP BY o.owner,
o.object_name,
o.object_type,
ash.event
ORDER BY samples DESC
FETCH FIRST 50 ROWS ONLY;
This gives you a ranking of objects associated with GC waits.
32. Find the Worst Instance
SELECT instance_number,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND event LIKE 'gc %'
GROUP BY instance_number,
event
ORDER BY samples DESC;
Suppose:
INSTANCE EVENT SAMPLES -------- ----------------------- ------- 2 gc current request 82000 1 gc current request 11000
Clearly RAC2 deserves closer investigation.
33. Compare Instances
Don’t just investigate RAC2.
Compare:
RAC1 RAC2
Look at:
DB CPU DB Time GC waits I/O Active sessions Services SQL
A useful query:
SELECT instance_number,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
GROUP BY instance_number
ORDER BY instance_number;
Then break it down by wait class.
34. GC Latency Matters
The number of GC waits alone isn’t enough.
Suppose:
RAC1 → 100,000 GC waits average latency = 0.2 ms
versus:
RAC2 → 20,000 GC waits average latency = 15 ms
The second situation may be much more problematic.
So investigate both:
Frequency + Latency
35. AWR Global Cache Statistics
In AWR, inspect the sections related to:
- Global Cache Load Profile
- Global Cache Efficiency
- Global Cache Statistics
- Cluster Wait Class
- Top Cluster Events
The exact presentation depends on the AWR report format and Oracle version.
The objective is to determine:
Are blocks being transferred efficiently? Are there excessive requests? Are there busy blocks? Is the interconnect contributing to latency?
36. Cache Fusion and the Interconnect
Remember Part 19.
If the interconnect is slow or unstable:
SQL ↓ GC request ↓ Interconnect ↓ Delay
Therefore a gc_* wait can have two broad categories of root causes:
Workload problem
Hot blocks Poor locality Application contention
Infrastructure problem
Interconnect latency
Packet loss
Network errors
Configuration problemsYou need to distinguish them.
37. Check the Interconnect
On Linux:
ip -s link
Look for:
RX errors TX errors dropped packets
Also:
ip -br link
and:
ethtool <interface>
Check:
Speed Duplex Errors Drops
The actual interface name depends on your environment.
38. Check RAC Interconnect Configuration
Inside Oracle:
SELECT inst_id,
name,
ip_address,
is_public,
source
FROM gv$cluster_interconnects
ORDER BY inst_id;
This can help confirm which interfaces Oracle is using for the cluster interconnect.
39. Network Latency
From the OS, test the appropriate private interfaces.
For example:
ping <remote-private-ip>
For deeper analysis:
ip -s link
and where appropriate:
ethtool -S <interface>
The exact network validation should follow your infrastructure team’s standards.
40. Don’t Blame the Interconnect First
This is another common mistake.
If you see:
gc current request
and immediately say:
“The private network is slow.”
you may miss the real problem.
Suppose:
SQL ↓ updates same row ↓ hot block ↓ RAC1 ↔ RAC2 ↓ GC waits
The network is simply transporting the consequences of application contention.
41. Find the SQL Driving the Hot Object
Once you identify:
SALES.ORDERS
find SQL associated with it.
ASH:
SELECT sql_id,
service_name,
instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND current_obj# = :object_id
GROUP BY sql_id,
service_name,
instance_number
ORDER BY samples DESC;
Now you may get:
SQL_ID SERVICE INSTANCE --------- ---------- -------- 8a12abc SALES_APP 1 8a12abc SALES_APP 2 91bc123 BATCH 2
42. Same SQL on Multiple Instances
This is particularly interesting.
Suppose:
SQL 8a12abc RAC1 → 4,000 samples RAC2 → 6,000 samples
and both are updating the same object.
Ask:
Should this workload actually run on both instances?
Maybe the answer is yes.
Maybe the answer is no.
This takes us directly back to Services.
43. Service Affinity
Suppose:
SALES_APP
runs on:
RAC1 + RAC2
but the workload constantly updates the same blocks.
You could investigate whether stronger service affinity would help:
SALES_APP Preferred → RAC1 Available → RAC2
rather than:
SALES_APP Preferred → RAC1,RAC2
This isn’t automatically better.
Measure it.
44. The RAC Tuning Loop
The process should be:
1. Detect ↓ 2. Measure ↓ 3. Identify wait ↓ 4. Identify SQL ↓ 5. Identify object ↓ 6. Identify block ↓ 7. Identify workload pattern ↓ 8. Fix root cause ↓ 9. Measure again
Never skip step 2.
45. Practical Case Study
Let’s work through a realistic incident.
Incident
At 14:30:
“The SALES application becomes very slow.”
Monitoring shows:
RAC2 CPU = 75% RAC1 CPU = 35%
AWR shows:
gc current request gc current block busy
46. Step 1 — Identify the Service
SELECT service_name,
instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 14:00:00'
AND TIMESTAMP '2026-08-08 14:30:00'
GROUP BY service_name,
instance_number
ORDER BY samples DESC;
Result:
SALES_APP RAC2
47. Step 2 — Identify SQL
SELECT sql_id,
instance_number,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 14:00:00'
AND TIMESTAMP '2026-08-08 14:30:00'
AND service_name = 'SALES_APP'
AND event LIKE 'gc %'
GROUP BY sql_id,
instance_number,
event
ORDER BY samples DESC;
Result:
8a12abc RAC2 gc current request
48. Step 3 — Identify Object
SELECT o.owner,
o.object_name,
o.object_type,
COUNT(*) samples
FROM dba_hist_active_sess_history ash
JOIN dba_objects o
ON o.object_id = ash.current_obj#
WHERE ash.sample_time BETWEEN
TIMESTAMP '2026-08-08 14:00:00'
AND TIMESTAMP '2026-08-08 14:30:00'
AND ash.sql_id = '8a12abc'
AND ash.event LIKE 'gc %'
GROUP BY o.owner,
o.object_name,
o.object_type
ORDER BY samples DESC;Result:
SALES
ORDERS
TABLE49. Step 4 — Identify Blocks
SELECT current_file#,
current_block#,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 14:00:00'
AND TIMESTAMP '2026-08-08 14:30:00'
AND sql_id = '8a12abc'
AND event LIKE 'gc %'
GROUP BY current_file#,
current_block#,
event
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;
Suppose:
FILE BLOCK 12 485923 12 485924
appear repeatedly.
That’s a strong hot-block candidate.
50. Step 5 — Identify the Application Pattern
Now inspect the SQL.
Suppose it is:
UPDATE sales.orders SET status = :1 WHERE order_id = :2;
That doesn’t immediately prove a hot block.
You investigate:
Index? Rows per block? Concurrent updates? Same order IDs? Partitioning? Service placement?
51. Step 6 — Check Indexes
SELECT index_name,
column_name,
column_position
FROM dba_ind_columns
WHERE table_owner = 'SALES'
AND table_name = 'ORDERS'
ORDER BY index_name, column_position;
Then:
SELECT index_name,
uniqueness,
status,
clustering_factor,
num_rows
FROM dba_indexes
WHERE owner = 'SALES'
AND table_name = 'ORDERS';
Now examine the execution plan.
52. Step 7 — Examine Execution Plan
For a currently cached SQL:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => '8a12abc',
format => 'ALLSTATS LAST'
)
);
Look for:
Actual Rows Estimated Rows Buffer Gets Access Predicates Filter Predicates
A RAC problem can coexist with a poor execution plan.
53. Step 8 — Determine Whether It Is Really a Hot Block
Don’t conclude:
gc current block busy = hot block
Instead establish evidence:
Same object + Same blocks + Multiple instances + High concurrency + Repeated GC requests
Now the hot-block hypothesis becomes strong.
54. Possible Fix #1 — Service Affinity
If the workload doesn’t need to run everywhere:
SALES_APP Preferred → RAC1 Available → RAC2
This may reduce cross-instance block ownership changes.
But test it.
55. Possible Fix #2 — Application Design
If multiple sessions update the same row:
UPDATE control_table SET counter = counter + 1 WHERE id = 1;
the better solution may be to redesign the mechanism.
Possibilities might include:
- Sequence usage
- Partitioning
- Batching
- Application-side aggregation
- Reducing unnecessary updates
The right solution depends on the application.
56. Possible Fix #3 — Partitioning
Suppose a table is naturally partitioned by:
customer_id region date business_unit
If different workloads naturally access different partitions, partitioning can improve locality and reduce contention in appropriate cases.
For example:
RAC1 → Region A RAC2 → Region B
can sometimes align:
Workload + Data + Instance
But partitioning should be driven by access patterns and lifecycle requirements, not RAC alone.
57. Possible Fix #4 — Index Design
If the problem is an index hot block, investigate:
Index key design Insert pattern Reverse key possibility Hash partitioned indexes where appropriate Partitioning Application distribution
Again:
Never apply a reverse-key index simply because someone says “hot block.”
58. Possible Fix #5 — Reduce Cross-Instance Workload
This returns us to Part 20.
If:
RAC1 → modifies object RAC2 → modifies same object
constantly, investigate whether services can create better workload affinity.
59. What Not to Do
Avoid these reactions:
❌ “Restart RAC.”
gc waits → restart instance
The problem may return immediately.
❌ “Increase CPU.”
If the root cause is hot blocks, more CPU may accomplish very little.
❌ “Increase interconnect bandwidth.”
If the application is constantly fighting over the same blocks, faster networking doesn’t eliminate the contention.
❌ “Move everything to RAC1.”
You may simply overload one instance.
❌ “Create an index.”
An index can actually introduce new contention.
60. The Expert Mindset
When you see:
gc current request
don’t think:
“RAC problem.”
Think:
“A session is waiting for a current block through the RAC global cache mechanism. Why is that block remote, and why is the application requesting it so frequently?”
That question leads you toward the root cause.
61. A Complete RAC GC Diagnostic Script
Here’s a useful starting point for a real incident:
SET LINESIZE 240
SET PAGESIZE 100
PROMPT =========================================
PROMPT ACTIVE CLUSTER WAITS
PROMPT =========================================
SELECT inst_id,
event,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND wait_class = 'Cluster'
GROUP BY inst_id, event
ORDER BY sessions DESC;
PROMPT =========================================
PROMPT GC WAITS BY SERVICE
PROMPT =========================================
SELECT service_name,
inst_id,
event,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND event LIKE 'gc %'
GROUP BY service_name, inst_id, event
ORDER BY sessions DESC;
PROMPT =========================================
PROMPT GC WAITS BY SQL
PROMPT =========================================
SELECT sql_id,
inst_id,
event,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND event LIKE 'gc %'
GROUP BY sql_id, inst_id, event
ORDER BY sessions DESC;
PROMPT =========================================
PROMPT RAC INTERCONNECTS
PROMPT =========================================
SELECT inst_id,
name,
ip_address,
is_public,
source
FROM gv$cluster_interconnects
ORDER BY inst_id;
62. Historical GC Diagnostic Script
For an incident that already happened:
SELECT service_name,
instance_number,
sql_id,
event,
current_obj#,
current_file#,
current_block#,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
:start_time AND :end_time
AND event LIKE 'gc %'
GROUP BY service_name,
instance_number,
sql_id,
event,
current_obj#,
current_file#,
current_block#
ORDER BY samples DESC
FETCH FIRST 100 ROWS ONLY;
This is an excellent first-pass forensic query.
63. Build Your RAC Performance Evidence
For every RAC performance incident, capture:
Incident start/end
↓
AWR snapshots
↓
ASH
↓
Top wait events
↓
Top SQL
↓
Services
↓
Instances
↓
Objects
↓
Blocks
↓
Interconnect statistics
↓
Application behavior
Don’t rely on screenshots of Enterprise Manager alone.
64. RAC Performance Report Structure
For an enterprise incident, I recommend documenting:
1. Executive Summary
Application experienced increased latency.
2. Impact
14:00–14:30 SALES application
3. RAC Findings
RAC2 experienced elevated cluster waits.
4. SQL Findings
SQL_ID 8a12abc
5. Object Findings
SALES.ORDERS
6. Block Findings
Repeated access to blocks X/Y
7. Infrastructure
Interconnect healthy
8. Root Cause
Cross-instance concurrent updates
9. Recommendation
Improve workload affinity/application concurrency.
This is the kind of analysis expected from a senior RAC DBA.
65. Part 22 — The Big Picture
We’ve now connected several parts of the series:
Part 19
Interconnect
↓
Part 20
Services
↓
Part 21
Resource Manager
↓
Part 22
Performance Diagnosis
Together:
RAC APPLICATION
|
SERVICE
|
+---------+---------+
| |
RAC1 RAC2
| |
SQL SQL
| |
Buffer Cache Buffer Cache
| |
+--------+----------+
|
Cache Fusion
|
Interconnect
|
Waits
|
ASH
|
SQL_ID
|
OBJECT
|
BLOCK
|
ROOT CAUSE
66. The RAC Performance Formula
A useful mental model is:
RAC Performance = SQL efficiency + Data locality + Service design + CPU management + Cache Fusion efficiency + Interconnect health
If one of these is badly designed, RAC performance can suffer.
67. What You Should Be Able to Do After Part 22
You should now be able to investigate a complaint such as:
“The application is slow on RAC.”
and systematically answer:
Which instance?
GV$SESSION GV$SQL ASH
Which service?
SERVICE_NAME
Which SQL?
SQL_ID
Which wait?
gc current gc cr
Which object?
CURRENT_OBJ#
Which block?
CURRENT_FILE# CURRENT_BLOCK#
Infrastructure or workload?
ASH AWR Interconnect statistics
What should be changed?
SQL Service Application Partitioning Index Resource Manager Network
That’s the difference between looking at RAC performance and troubleshooting RAC performance.
Google+
Facebook
