Introduction

Active Session History is a tool inside Oracle databases. It helps you see what sessions did and why. Many DBAs search for active session history to fix slow queries. This guide explains the idea step by step. I write in plain words and short sentences. You will learn what ASH records. You will see how to read its data. You will also get tips for tuning and for safe use. I add real-world checks and a small example. This article keeps things practical. It is for DBAs, developers, and learners. You do not need deep prior knowledge. By the end, you will know how active session history can help you find and fix performance problems.

What is Active Session History (ASH) in simple words

Active Session History samples running sessions often. It stores the data in memory and in AWR snapshots. Every sampled entry is an active session snapshot. DBAs use these snapshots to see waits and SQL IDs. The idea is to capture what sessions did over time. Active session history is not full tracing. It is lightweight and quick. It does not record every event. Instead, it samples many sessions many times per minute. That makes it useful for spotting patterns. If a query repeats heavy waits, ASH will show that. If a background job locks objects, ASH often captures the state. This makes ASH a powerful tool for root-cause hunting.

How ASH works behind the scenes

ASH collects samples from active sessions at set intervals. The samples include session ID, SQL ID, wait event, and program name. Oracle stores samples in memory first. Then the AWR job writes parts to disk at snapshot time. This approach keeps overhead low. You can query V$ACTIVE_SESSION_HISTORY for recent data. Older data comes from DBA_HIST_ACTIVE_SESS_HISTORY saved in AWR. When you search for active session history, you will see SQL and wait stacks. These stacks help tie a session to its bottleneck. Because ASH samples, short spikes may be missed. But sustained issues usually appear clearly.

Why DBAs rely on active session history

DBAs use active session history to find hot spots fast. It shows which SQL statements used the CPU most. It shows where sessions waited more than they should. This helps fix slow reports and app slowness. ASH is easier than sifting through raw trace files. It gives a view over time. You can see patterns by time of day. This is useful for recurring problems. When you have a performance ticket, ASH is often the first stop. It points to the SQL ID and waits you must study. That saves many hours of blind search.

Key ASH fields you should know

ASH records include many useful fields. The main ones are SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, SQL_ID, EVENT, WAIT_CLASS, and PROGRAM. Many DBAs look at SQL_ID first. SQL_ID points to the statement that ran. EVENT shows what the session waited for. WAIT_CLASS groups similar waits like CPU or Network. PROGRAM can show which app or job caused the session to run. When you query for active session history, focus on these fields first. They tell the story quickly. Other fields like BLOCKING_SESSION or MACHINE help in complex cases.

How to query active session history (basic example)

You can run a simple query on V$ACTIVE_SESSION_HISTORY to see recent activity. For example, show top events with counts. Use GROUP BY EVENT and ORDER BY count desc. That reveals current waits. Use WHERE SAMPLE_TIME > SYSDATE – 1/24 for the last hour. To find heavy SQL, GROUP BY SQL_ID. You will then join to V$SQL to get SQL_TEXT. This method quickly shows which SQL burns time. When you use active session history queries, try small time windows first. This avoids huge result sets and speeds your work.

ASH vs AWR and other Oracle views

ASH is the session sample store. AWR collects and stores snapshots, which include ASH summaries. AWR provides historical depth for longer windows. V$ views show current live data. ASH sits in the middle, giving recent sampled history. Use AWR if you need weeks of data. Use ASH for near-term and detailed session-level states. If you need full trace for a single session, use SQL Trace or 10046 tracing. But for most performance hunts, active session history plus AWR suffices. They balance detail and overhead well.

Common ASH patterns and how to read them

Common patterns show where to look for fixes. A spike in “db file sequential read” suggests index I/O waits. High “latch” waits point to contention for shared memory. Many “log file parallel write” waits can mean slow storage for redo. If SQL_ID shows repeated executions with high buffer gets, tune the SQL or add indexes. When you search active session history, map waits to SQL_IDs and to programs. This gives you a clear target. Track patterns over time, not just one sample. Patterns tell the long-term story and guide safer fixes.

Using ASH for incident response

During incidents, time is short. ASH helps triage quickly. Query ASH for the busiest SQL or waits right now. Check for blocking sessions using blocking_session fields. Use short intervals for speed. Ask: is CPU maxed? Is I/O high? Is one SQL repeating? ASH often tells which application module caused the spike. When multiple teams react, share the SQL_ID and top events for fast alignment. That is faster than sending raw traces. In my own on-call experience, a quick ASH check often cut mean time to identify in half.

Limitations and pitfalls of active session history

ASH samples, so it may miss brief events. Short spikes under the sampling interval can be invisible. Also, ASH needs memory and uses CPU. Very busy systems must set parameters properly. Some waits appear in ASH as generic “other”, requiring deeper digs. ASH also shows active sessions only, not all historical details. If you rely only on ASH, you might miss system-wide config issues. Always pair ASH with system metrics like CPU, I/O, and OS stats. That gives a fuller picture and avoids chasing the wrong root cause.

Best practices for using active session history data

Start with a clear question. Narrow the time window. Use GROUP BY to find top waits and SQL. Join SQL_ID to SQL text early. Save queries as templates for fast reuse. Review AWR for longer trends. Keep an eye on sampling frequency and memory settings. Protect AWR with proper retention for your needs. When making changes, test in a staging environment first. Document findings and actions for future reference. These steps make active session history work for your team repeatedly and safely.

How to find blocking and lock issues with ASH

Blocking often shows as sessions waiting on enqueues or latch waits. Use ASH to find sessions with blocking_session set. Cross-check with V$LOCK to see locks held. If one session holds many locks, identify its SQL_ID and program. Then decide to kill, roll back, or contact the app owner. ASH helps you avoid blind kills by naming the offender. In one real example, ASH showed a nightly job holding locks while a web app backed up. We fixed the job ordering and removed the daily outages. That saved hours of manual restarts.

Integrating ASH with other tools and automation

ASH data feeds many tools. Many monitoring dashboards use ASH to populate top waits charts. You can write scripts to alert when a wait percentage crosses a threshold. Ship ASH summaries to hooks that trigger paging. Use scheduled AWR extracts for automated trend analysis. Combine OS metrics with ASH to build a composite health score. Remember to use secure credentials and to keep automation safe. Good automation reduces mean time to detect and lets your operations team focus on fixes, not chasing signals.

Tuning using ASH: a step-by-step mini workflow

1. Capture the recent ASH top waits and SQL. 2) Identify the high-impact SQL IDs. 3) Check execution plans in V$SQL_PLAN. 4) Run explain plan and optimizer stats check. 5) Try index or SQL rewrites in a test box. 6) Measure change with a new ASH or AWR window. This closed loop keeps changes small and measured. Using active session history as the measurement tool helps you prove success. It shows whether waits drop and CPU usage improves after your change.

Security and access considerations for ASH data

ASH contains SQL text and session details. This can include sensitive data such as user IDs and program names. Grant ASH access only to trusted DBAs and admins. Use roles and audit who runs heavy ASH queries. For cloud DBs, check how ASH data sits in backups and snapshots. Keep compliance in mind. When sharing ASH extracts with app teams, scrub sensitive fields. Respect privacy rules and company policies. Securing access to active session history keeps your system safe and compliant.

When to use ASH vs other trace methods

Use ASH for quick, broad root-cause discovery. Use SQL Trace or 10046 for deep single-session profiling. Use Statspack or AWR for longer-term trends. Use OS-level tracing for kernel or network-level problems. If CPU is spiking across many sessions, ASH shows which SQL dominates. If one session misbehaves, a 10046 trace gives full call flow and binds. Knowing the right tool avoids wasted time. For most day-to-day problems, active session history gives the fastest path to an actionable lead.

Practical tips for long-term ASH retention and cost control

ASH writes to AWR snapshots, which cost space. Set retention based on needs and budget. Reduce snapshot frequency if space is tight. Archive older AWR data to cheaper storage or compress it. Keep the retention long enough to spot weekly patterns. For seasonal systems, keep a history that spans the key season. Monitor the size of DBA_HIST_ACTIVE_SESS_HISTORY and DBA_HIST_SNAPSHOT tables. Balance the value of historical insight against storage costs. Smart retention keeps active session history useful and affordable.

FAQs — quick answers about active session history

1. What is the main use of active session history?
The main use of active session history is to find who waited and why. It helps DBAs spot top waits and the SQL behind them. ASH shows session states and waits over time. This makes it easier to find performance hot spots and to tune queries or system settings.

2. How recent is the data in V$ACTIVE_SESSION_HISTORY?
V$ACTIVE_SESSION_HISTORY shows recent in-memory samples. It usually covers minutes to hours depending on load and memory settings. For older ASH data, check DBA_HIST_ACTIVE_SESS_HISTORY in AWR. Use AWR when you need longer historical views beyond the recent window.

3. Does ASH add a lot of overhead?
ASH is designed to be light. It samples sessions rather than tracing every call. Still, on very busy systems, ASH needs enough memory and CPU. Tune sampling and AWR retention if you see resource pressure. The trade-off is usually worth it for the visibility ASH provides.

4. Can I use ASH to find which SQL caused high CPU?
Yes. Group ASH by SQL_ID and look for CPU and DB CPU wait classes. ASH shows which SQL ran during high CPU periods. Then use V$SQL_PLAN to inspect the execution plan. This combination points you to heavy queries that need tuning.

5. How do I find deadlocks with ASH?
ASH can show waits and blocking sessions. For actual deadlocks, check the alert log or trace files for deadlock graphs. Use ASH to find session activity around the deadlock time for context. That helps you understand how the deadlock developed.

6. Is ASH available in all Oracle editions?
ASH is part of Oracle Diagnostics Pack features and needs the relevant license. AWR and ASH availability depends on your Oracle edition and licensing. Check your license and Oracle documentation before relying on ASH for production support.

Conclusion

Active session history is a practical and fast way to find database pain points. Use active session history to see waits, SQL IDs, and programs that matter. Start with short queries and small time windows. Then join to SQL and plan views to get the full story. Protect ASH access and manage retention so you keep the data safe and useful. Practice the step-by-step tuning loop and automate simple alerts. If you want, I can give you a one-page checklist for ASH queries and a starter SQL template to run during incidents. That checklist will help your team use active session history quickly and safely. Would you like that checklist?