AWR & ASH Deep Dive: Finding the Real Cause of RAC Performance Problems
In Part 15, we built a methodology for troubleshooting RAC performance:
User complaint
↓
DB Time
↓
AWR / ASH
↓
Wait events
↓
SQL
↓
Service
↓
Instance
↓
RAC / Cache Fusion
↓
OS / Network / Storage
↓
Root Cause
Now we’re going deeper into the two tools that every serious Oracle DBA should master:
- AWR — Automatic Workload Repository
- ASH — Active Session History
The goal isn’t simply to generate an AWR report.
The goal is to answer:
Which sessions were affected, what were they doing, where were they running, what were they waiting for, and which SQL or object caused the problem?
1. AWR vs ASH
The easiest way to remember the difference:
AWR tells you what happened over a period of time. ASH tells you what sessions were doing during that period.
| AWR | ASH |
|---|---|
| Historical workload overview | Session-level activity |
| Usually compares snapshots | Samples active sessions |
| SQL statistics | SQL/session/wait information |
| System statistics | Instance/service/object details |
| Wait events | Exact activity during incident |
| Excellent for trends | Excellent for troubleshooting |
Think of it like this:
AWR │ ├── What happened? ├── How severe was it? ├── Which waits dominated? └── Which SQL consumed DB Time?
while:
ASH │ ├── Who was affected? ├── Which instance? ├── Which service? ├── Which SQL? ├── Which object? └── What were they waiting for?
2. Important Licensing Note
AWR and ASH are part of Oracle’s Diagnostic Pack functionality.
Before using these features in production, make sure your Oracle licensing permits their use.
For licensed environments, AWR reports can be generated with:
@?/rdbms/admin/awrrpt.sqlFor RAC-wide analysis:
@?/rdbms/admin/awrgrpt.sqlFor an individual RAC instance:
@?/rdbms/admin/awrrpti.sql3. Understanding AWR Snapshots
AWR periodically captures database performance information.
You can see snapshots with:
SELECT snap_id,
begin_interval_time,
end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;
Example:
SNAP_ID BEGIN_TIME END_TIME ------- ------------------- ------------------- 1201 08-AUG-26 09:00 08-AUG-26 10:00 1200 08-AUG-26 08:00 08-AUG-26 09:00 1199 08-AUG-26 07:00 08-AUG-26 08:00
4. Choosing the Correct Snapshot Range
Suppose users report:
“The application was slow between 09:15 and 09:45.”
Don’t generate:
08:00 → 12:00
if you can avoid it.
Instead, choose snapshots that surround the incident.
For example:
09:00 → 10:00
Then use ASH to investigate the specific 30-minute period.
5. AWR Report
A standard AWR report:
@?/rdbms/admin/awrrpt.sql
Oracle asks for:
begin_snap end_snap report_type
For RAC environments, a global report is often more appropriate:
@?/rdbms/admin/awrgrpt.sql
This gives you a cluster-level perspective.
6. Why Global AWR Matters
Imagine a two-node RAC:
ORCL1 DB Time = 20,000 sec ORCL2 DB Time = 2,000 sec
A report looking only at ORCL2 may make the system appear healthy.
But globally:
Total DB Time = 22,000 sec
and ORCL1 is clearly carrying most of the workload.
For RAC troubleshooting:
Always determine whether you need an instance-level or cluster-level view.
7. Start With DB Time
In AWR, look at:
DB Time DB CPU
Suppose:
DB Time 120,000 sec DB CPU 80,000 sec
Then approximately:
Wait Time = 40,000 sec
This immediately tells you that both CPU and waiting are significant.
8. Top Foreground Events
Next:
Top 10 Foreground Events by Total Wait Time
Example:
Event Time % -------------------------------- DB CPU 45% gc current request 20% db file sequential read 15% log file sync 8%
Now you know where to focus.
9. But Don’t Read AWR Mechanically
A common mistake is:
“The largest wait event is X, therefore X is the problem.”
Not always.
Suppose:
DB CPU = 45% gc current request = 20%
The application might have inefficient SQL consuming huge CPU and simultaneously causing RAC traffic.
The GC wait could be a consequence rather than the root cause.
10. Top SQL by DB Time
One of the most useful AWR sections is:
SQL ordered by Elapsed Time
But remember:
Elapsed time alone doesn’t tell you whether SQL is CPU-bound, I/O-bound, or RAC-bound.
You need to inspect the other SQL metrics.
11. Top SQL by CPU
You can also query historical SQL statistics.
For example:
SELECT sql_id,
plan_hash_value,
executions_delta,
cpu_time_delta,
elapsed_time_delta,
buffer_gets_delta,
disk_reads_delta
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN :begin_snap AND :end_snap
ORDER BY cpu_time_delta DESC
FETCH FIRST 20 ROWS ONLY;
This is much more useful than looking only at current GV$SQL after the incident has already ended.
12. Top SQL by Elapsed Time
SELECT sql_id,
plan_hash_value,
executions_delta,
elapsed_time_delta,
cpu_time_delta
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN :begin_snap AND :end_snap
ORDER BY elapsed_time_delta DESC
FETCH FIRST 20 ROWS ONLY;
Now compare:
Elapsed Time CPU Time Executions
13. CPU Per Execution
A SQL statement executed 100,000 times may have high total CPU.
Another statement executed only 10 times may have terrible performance per execution.
Calculate:
SELECT sql_id,
executions_delta,
cpu_time_delta,
ROUND(
cpu_time_delta /
NULLIF(executions_delta,0)
) cpu_per_exec
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN :begin_snap AND :end_snap
AND executions_delta > 0
ORDER BY cpu_per_exec DESC
FETCH FIRST 20 ROWS ONLY;
This is extremely useful for identifying expensive individual executions.
14. Logical Reads
Look at:
buffer_gets_delta
High buffer gets can indicate:
- Inefficient execution plan
- Missing index
- Poor join method
- Full scans
- Excessive executions
For example:
SQL A Executions = 1,000,000 Buffer Gets = 500,000,000
That’s very different from:
SQL B Executions = 10 Buffer Gets = 500,000,000
Both deserve investigation, but for different reasons.
15. Physical Reads
Look at:
disk_reads_delta
High physical reads may indicate:
- Large scans
- Poor caching
- Inefficient SQL
- Storage pressure
But don’t automatically conclude:
“We need more memory.”
First identify the SQL generating the reads.
16. RAC-Specific SQL Analysis
Now we combine AWR with RAC.
Suppose the top SQL has:
SQL_ID = 7f3abc...
Query:
SELECT sql_id,
plan_hash_value,
executions_delta,
elapsed_time_delta,
cpu_time_delta
FROM dba_hist_sqlstat
WHERE sql_id = '7f3abc...'
ORDER BY snap_id DESC;
Then determine which instance generated the workload.
Depending on the available historical views and version, use the instance-level columns in AWR SQL statistics to distinguish the RAC instances.
17. ASH: The Real-Time Microscope
AWR gives you the big picture.
ASH gives you the microscope.
Current ASH:
SELECT sample_time,
inst_id,
session_id,
session_serial#,
sql_id,
event,
wait_class,
service_name
FROM gv$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '10' MINUTE
ORDER BY sample_time DESC;
This is extremely powerful during an active incident.
18. Historical ASH
After the incident:
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
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
ORDER BY sample_time;
This allows you to investigate an incident that happened hours or days earlier, subject to your retention and licensing.
19. ASH by Instance
Start simple:
SELECT instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
GROUP BY instance_number
ORDER BY instance_number;
Example:
INSTANCE SAMPLES -------- ------- 1 15,200 2 4,100
This immediately tells you that Instance 1 was carrying substantially more active workload.
20. ASH by Service
SELECT service_name,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
GROUP BY service_name
ORDER BY samples DESC;
Example:
SERVICE SAMPLES --------------- ------- SALES_APP 13,500 REPORTING 4,000 BATCH 900
Now you know which workload dominates the incident.
21. ASH by Wait Event
SELECT event,
wait_class,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
GROUP BY event, wait_class
ORDER BY samples DESC;
Example:
EVENT SAMPLES ----------------------- ------- gc current request 8,200 CPU 6,400 db file sequential read 3,100
Now you have a much clearer picture.
22. ASH by SQL
SELECT sql_id,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;
This is one of the fastest ways to find SQL consuming DB Time during an incident.
23. ASH by SQL and Wait Event
Now combine them:
SELECT sql_id,
event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
AND sql_id IS NOT NULL
GROUP BY sql_id, event
ORDER BY samples DESC
FETCH FIRST 30 ROWS ONLY;
You might find:
SQL_ID EVENT ----------- -------------------- 8f2abc gc current request 8f2abc gc buffer busy 8f2abc CPU
Now you have a strong candidate for investigation.
24. ASH by Instance + SQL
This is especially useful in RAC:
SELECT instance_number,
sql_id,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
AND sql_id IS NOT NULL
GROUP BY instance_number, sql_id
ORDER BY samples DESC;
You may discover:
Instance 1 → SQL ABC → 8,000 samples Instance 2 → SQL ABC → 500 samples
That tells you the problem is heavily concentrated on Instance 1.
25. Find Hot Objects
ASH can also help identify objects involved in waits.
SELECT current_obj#,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-08-08 09:00:00'
AND TIMESTAMP '2026-08-08 09:30:00'
AND event LIKE 'gc%'
AND current_obj# > 0
GROUP BY current_obj#
ORDER BY samples DESC;
Then:
SELECT owner,
object_name,
object_type
FROM dba_objects
WHERE object_id = :object_id;
This allows you to move from:
GC wait
to:
OBJECT
which is a major step toward root cause.
26. Hot Block Investigation
If the object is an index:
HOT_INDEX
investigate:
- Insert pattern
- Index structure
- Right-hand growth
- Concurrent modifications
- Sequence-driven inserts
If the object is a table:
HOT_TABLE
investigate:
- Frequently updated rows
- Hot blocks
- Transaction design
- Application concurrency
- Partitioning opportunities
27. Finding Blocking Sessions
ASH can help identify blocking activity.
Current sessions:
SELECT inst_id,
sid,
serial#,
username,
blocking_instance,
blocking_session,
event,
sql_id
FROM gv$session
WHERE blocking_session IS NOT NULL;
Example:
ORCL2 Session 250 blocked by: ORCL1 Session 100
Now RAC is directly involved in the blocking chain.
28. RAC Blocking Chain
Visualize it:
ORCL1
Session 100
|
| Holds resource
v
Resource
^
|
|
ORCL2
Session 250
|
| Waiting
This is where GES and global locking become important.
29. SQL Plan Differences
One of the most interesting RAC problems is:
Same SQL, different performance on different instances.
Start with:
SELECT inst_id,
sql_id,
child_number,
plan_hash_value,
executions,
elapsed_time,
cpu_time
FROM gv$sql
WHERE sql_id = '&sql_id'
ORDER BY inst_id, child_number;
Suppose:
ORCL1 Plan 123456 ORCL2 Plan 987654
Investigate why.
30. Display the Execution Plan
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&sql_id',
NULL,
'ALLSTATS LAST'
)
);
If the SQL is no longer in memory, use the historical AWR SQL plan information where available.
31. RAC Instance Comparison
For a problematic SQL statement, create a comparison:
ORCL1 ORCL2 ------------------------------------------------ Executions 50,000 5,000 Elapsed Time 20 sec 200 sec CPU 15 sec 180 sec Plan Hash 123456 987654 GC Waits Low High
This is much more valuable than simply saying:
“SQL is slow.”
32. AWR + ASH Combined
This is the workflow I recommend:
AWR ↓ Identify the problematic period ↓ Identify dominant wait class ↓ Identify top SQL ↓ ASH ↓ Identify affected instances ↓ Identify services ↓ Identify SQL ↓ Identify objects ↓ Identify blocking / RAC waits ↓ Check infrastructure
AWR tells you where to look.
ASH tells you what was happening there.
33. Example Incident
Let’s analyze a realistic case.
Users report:
“The sales application is slow between 10:00 and 10:30.”
AWR:
DB Time ↑ 250% Top event: gc current request
First conclusion:
RAC problem.
But ASH shows:
SERVICE = SALES_APP INSTANCE = ORCL2 SQL_ID = 9k4abc OBJECT = ORDERS
Further investigation shows:
SQL_ID 9k4abc Plan Hash = 12345
and the SQL updates thousands of rows in the same table.
At the same time:
ORCL1 → Batch service ORCL2 → Sales application
The batch workload modifies the same table.
So the real architecture is:
ORDERS
|
+--------+--------+
| |
ORCL1 ORCL2
BATCH OLTP
| |
+--- Cache Fusion-+
The GC wait is a symptom of the workload architecture.
34. The Solution
Possible solutions include:
- Separate batch and OLTP workloads.
- Review service placement.
- Reduce cross-instance updates.
- Optimize the SQL.
- Review transaction size.
- Consider partitioning.
- Reschedule heavy batch processing.
The correct solution depends on the actual application architecture.
35. Building Your Own RAC AWR/ASH Report
You can start creating a reusable diagnostic script.
SET LINESIZE 220
SET PAGESIZE 100
PROMPT ==========================================
PROMPT RAC INSTANCE SUMMARY
PROMPT ==========================================
SELECT inst_id,
instance_name,
host_name,
status
FROM gv$instance
ORDER BY inst_id;
PROMPT ==========================================
PROMPT ACTIVE SESSIONS BY INSTANCE / SERVICE
PROMPT ==========================================
SELECT inst_id,
service_name,
COUNT(*) active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id, service_name
ORDER BY inst_id, active_sessions DESC;
PROMPT ==========================================
PROMPT CURRENT RAC WAITS
PROMPT ==========================================
SELECT inst_id,
event,
COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND event LIKE 'gc%'
GROUP BY inst_id, event
ORDER BY sessions DESC;
PROMPT ==========================================
PROMPT TOP SQL BY CPU
PROMPT ==========================================
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;
This is only a starting point.
A production-grade RAC diagnostic tool should also collect:
- AWR snapshots
- ASH samples
- SQL plans
- Services
- Interconnect
- ASM
- OS statistics
- CPU
- Memory
- Network
- Storage
36. What an OCM-Level DBA Should Be Able to Do
When someone gives you:
“RAC is slow.”
You should be able to produce something like:
Incident: 08-AUG-2026 09:00–09:30 Affected service: SALES_APP Affected instance: ORCL2 Primary symptom: High DB Time Top wait: gc current request Top SQL: 9k4abc Object: ORDERS Cause: Cross-instance modification of hot blocks Contributing factor: Batch workload running on ORCL1 Infrastructure: Interconnect healthy Recommended action: Separate batch/OLTP workload and review SQL/transaction design
That is a professional incident analysis.
Not:
“AWR shows GC waits.”
37. Important AWR/ASH Queries
Snapshots
SELECT snap_id,
begin_interval_time,
end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC;
Historical SQL
SELECT sql_id,
plan_hash_value,
executions_delta,
elapsed_time_delta,
cpu_time_delta,
buffer_gets_delta
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN :begin_snap AND :end_snap
ORDER BY elapsed_time_delta DESC;
Historical ASH by instance
SELECT instance_number,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :start_time AND :end_time
GROUP BY instance_number;
Historical ASH by event
SELECT event,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :start_time AND :end_time
GROUP BY event
ORDER BY samples DESC;
Historical ASH by SQL
SELECT sql_id,
COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN :start_time AND :end_time
AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY samples DESC;
38. The Expert’s AWR Reading Order
When I analyze a RAC AWR report, I don’t randomly browse the pages.
My usual approach is:
1. Snapshot period
↓
2. DB Time
↓
3. Load Profile
↓
4. Top Foreground Events
↓
5. RAC Statistics
↓
6. Top SQL
↓
7. SQL Plans
↓
8. Instance comparison
↓
9. I/O
↓
10. OS / Network
Then I use ASH to drill into the exact problem.
39. The Most Important Question
AWR may tell you:
gc current request = 20%
ASH may tell you:
SQL 9k4abc ORCL2 SALES_APP ORDERS
Now ask:
Why is SQL 9k4abc accessing ORDERS across RAC instances?
That’s where actual troubleshooting begins.
40. Don’t Tune the Symptom
This is perhaps the most important lesson in the entire series.
Suppose:
gc current request
is high.
Don’t immediately:
Change RAC parameters Increase network bandwidth Add CPU Restart instance
First identify:
SQL ↓ Object ↓ Workload ↓ Service ↓ Instance ↓ Block movement
Then decide what should change.
41. RAC Performance Investigation Checklist
When you receive a RAC performance incident:
Database
- DB Time
- DB CPU
- Top waits
- Transactions/sec
- Logical reads
- Physical reads
SQL
- Top SQL by elapsed time
- Top SQL by CPU
- Top SQL by logical reads
- Top SQL by physical reads
- Execution plans
- Plan differences
RAC
- GC waits
- Cache Fusion statistics
- Instance distribution
- Service distribution
- Blocking sessions
- Hot objects
Infrastructure
- CPU
- Memory
- I/O latency
- ASM
- Interconnect
- NIC errors
- Network utilization
Application
- Batch jobs
- Connection pools
- Transaction design
- Workload distribution
- Recent changes
42. Final Mental Model
By now, you should be thinking about RAC performance like this:
USER
|
↓
APPLICATION
|
↓
SERVICE
|
+-----------+-----------+
| |
ORCL1 ORCL2
| |
+-----------+-----------+
|
SQL
|
+----------+----------+
| |
CPU WAIT
|
+----------------+----------------+
| | |
I/O LOCK RAC
|
Cache Fusion
|
GCS/GES
|
Interconnect
|
Network
And AWR/ASH are the tools that allow you to navigate this architecture.
DBA Expert Tip
When someone sends you an AWR report and says:
“Please optimize this database.”
Don’t immediately start changing parameters.
First answer:
What was the workload doing during the reported period?
Then:
Which SQL consumed the DB Time?
Then:
Which sessions were affected?
Then:
Which instance and service were involved?
Then:
Why were they waiting?
That sequence is what separates report reading from performance engineering.
Conclusion
AWR and ASH are not simply reporting tools.
Used correctly, they become an investigation framework.
For RAC, the most powerful combination is:
AWR + ASH + GV$ views + SQL execution plans + Services + Cache Fusion statistics + OS/network/storage metrics
Together they allow you to move from:
“The RAC database is slow.”
to:
“The SALES_APP service on ORCL2 experienced elevated DB Time because SQL 9k4abc generated heavy cross-instance access to a hot ORDERS object while the batch workload was running on ORCL1.”
That is the level of analysis you should aim for.
AWR tells you where the fire was. ASH helps you find who was standing next to it when it started.
Google+
Facebook
