Ever wondered what exactly your Oracle database is doing right now when things slow down? That’s where Active Session History (ASH) helps. It shows you real activity inside the database, not guesses. Active Session History lets you see which sessions were active, what they were waiting on, and where time was spent — so you can quickly find and fix performance problems.
What Is Active Session History ?
Active Session History is a built-in Oracle feature that takes small snapshots of active sessions every second.
It only records sessions that are actually doing work, not idle ones.
Think of ASH like a CCTV camera for your database.
It doesn’t record everything forever, but it captures enough to understand what happened.
Why ASH Is So Useful
ASH answers simple but powerful questions:
- Why was the database slow?
- Which SQL was running?
- Was it CPU, I/O, or locks?
- Who was blocking whom?
Instead of guessing, you see real data.
How ASH Works (In Simple Words)
- Oracle checks active sessions every second
- It stores this data in memory
- Older data rolls off automatically
- You can query it using views
The main view you’ll use is:
V$ACTIVE_SESSION_HISTORY
How to Query Active Session History
Here’s a very basic example to see recent activity:
SELECT session_id, sql_id, event
FROM v$active_session_history
WHERE sample_time > SYSDATE - (5/1440);
👉 This shows what was active in the last 5 minutes.
Find Top Waiting Events
Want to know what the database was waiting on most?
SELECT event, COUNT(*) samples
FROM v$active_session_history
GROUP BY event
ORDER BY samples DESC;
This quickly tells you where time was spent.
Find Slow or Heavy SQL Queries
To see which SQL caused the most activity:
SELECT sql_id, COUNT(*) samples
FROM v$active_session_history
GROUP BY sql_id
ORDER BY samples DESC;
More samples = more time used.
Find Blocking Sessions
ASH is great for lock problems:
SELECT blocking_session, COUNT(*)
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
GROUP BY blocking_session;
This helps you spot who was blocking others.
Using ASH Through Oracle Enterprise Manager
If you use Oracle Enterprise Manager:
- Go to Performance → ASH Analytics
- Choose time range
- Filter by user, SQL, or wait event
This is perfect if you prefer charts instead of queries.
Pros and Cons of Active Session History
| Pros | Cons |
|---|---|
| Very easy to use | Keeps limited history |
| Shows real activity | Needs license (Diagnostic Pack) |
| Great for quick analysis | Not full session logs |
| Works for past problems | Memory-based |
Real-World Examples
Example 1: Slow Reports
A report suddenly takes 20 minutes instead of 2.
ASH shows most time spent on I/O waits → storage issue found.
Example 2: Blocking Issue
Users complain the app freezes.
ASH shows one session blocking many others → bad transaction fixed.
Example 3: CPU Spike
CPU hits 100%.
ASH points to one SQL query → rewritten and optimized.
FAQs (People Also Ask)
Is ASH enabled by default?
Yes, ASH runs automatically if the Diagnostic Pack is enabled.
How long does ASH keep data?
Usually about one hour in memory. Older data is removed.
Can I use ASH without Enterprise Manager?
Yes. You can query ASH using SQL views.
Is ASH available in all Oracle versions?
ASH exists in Oracle 10g and later, with improvements in newer versions.
Does ASH impact performance?
Very little. It is designed to be lightweight.
Final Verdict
Active Session History is one of the most powerful tools in Oracle for performance troubleshooting.
It’s simple, fast, and shows what really happened — not guesses.
If you manage Oracle databases and care about performance, ASH is not optional — it’s essential.
Use it early, use it often, and your troubleshooting time will drop fast.