10 Useful Queries Active Session History(Oracle ASH)

Oracle Database continuously tracks the activities occurring within the database. One of the key features that facilitates performance monitoring and troubleshooting is Active Session History (ASH). ASH captures SQL commands that consume database time at specific intervals, storing snapshots of active sessions.

Oracle e-Business Suite R12 also uses Oracle Database and ASH queries will help to monitor Oracle ERP system.

What is Active Session History (Oracle ASH)?

Oracle Active Session History (ASH) is an in-memory performance diagnostic feature that records session activity information. The collected data is stored in the V$ACTIVE_SESSION_HISTORY view (or GV$ACTIVE_SESSION_HISTORY in RAC environments) before being purged and written to disk. Historical ASH data is available in the DBA_HIST_ACTIVE_SESS_HISTORY view for further analysis.

Why is Active Session History Important?

Understanding ASH helps in multiple ways:

1. Identify Performance Bottlenecks – Long-running sessions can indicate performance issues. Analyzing SQL statements from ASH can help optimize queries and processes.
2. Troubleshoot Application Issues – If users experience slowdowns, ASH can pinpoint the root cause by showing what queries were running at the time.
3. Enhance System Security – ASH helps detect unauthorized access by monitoring user activity and session details.
4. Optimize Resource Allocation – By analyzing session resource usage, you can improve workload balancing.
5. Proactive Monitoring – Regular ASH review can identify issues before they escalate.

How to Access Active Session History?

ASH data can be accessed in several ways:

• Using Oracle Enterprise Manager (OEM): Provides a graphical interface for monitoring active sessions.
• SQL Queries: You can query V$SESSION and V$SQL to extract session data

SELECT s.sid, s.serial#, s.username, s.status, s.logon_time, q.sql_text
  FROM v$session s, v$sql q
 WHERE s.sql_id = q.sql_id
   AND s.status = 'ACTIVE';

• You can use the ASH sqls collection for reporting and montoring Oracle Database health, Oracle EBS system performation. You can automate monitoring queries using Oracledb Python library.

Best Practices for Managing Active Session History

• Regular Reviews: Monitor ASH periodically to detect anomalies.
• Set Alerts: Define thresholds for resource usage to trigger alerts.
• Use Monitoring Tools: Leverage OEM for automated data collection.
• Correlate Data: Combine ASH with CPU and I/O metrics for deeper analysis.

Advanced Queries for Oracle Active Session History

You can get a list of useful Oracle DBA scripts that leverage Active Session History (ASH) data. Each query is accompanied by an explanation of its functionality and the reason it’s needed for performance monitoring and troubleshooting.To gain deeper insights into database performance, use these queries:

1. Identify Top Events Consuming Wait Time

This query identifies the top wait events occurring in the last 30 minutes. It helps pinpoint the most frequent bottlenecks in the system. High wait times for specific events (e.g., “db file sequential read,” “enqueue”) indicate areas requiring investigation..

SELECT event, SUM(wait_time + time_waited) ttl_wait_time
  FROM v$active_session_history
 WHERE sample_time BETWEEN sysdate - 1/48 AND sysdate
GROUP BY event
ORDER BY 2 DESC;

2. Find Top Sessions Consuming Resources

Below SQL identifies which sessions are consuming the most time, helping to troubleshoot performance issues in Oracle Database. Lists the sessions that have accumulated the most wait time in the last 30 minutes.

You can easily Identify sessions experiencing the most performance issues. Focusing on these sessions can lead to quicker problem resolution.

SELECT sesion.sid, sesion.username,
       SUM(ash.wait_time + ash.time_waited)/1000000/60 
       ttl_wait_time_in_minutes
  FROM v$active_session_history ash, 
       v$session sesion
 WHERE sample_time BETWEEN sysdate - 1/48 AND sysdate
   AND ash.session_id = sesion.sid
GROUP BY sesion.sid, sesion.username
ORDER BY 3 DESC;

3. Find Top SQL Queries by Wait Time

You can identifies the SQL queries that have accumulated the most wait time in the last day(excluding SYS user queries). It helps in optimizing poorly performing queries that consume excessive database time.

You can pinpoint resource-intensive SQL statements that are impacting performance. These queries are prime candidates for optimization.

SELECT ash.user_id, du.username, 
       sa.sql_text, SUM(ash.wait_time + 
       ash.time_waited)/1000000 ttl_wait_time_in_seconds
  FROM v$active_session_history ash, 
       v$sqlarea sa, 
       dba_users du
 WHERE ash.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
   AND ash.sql_id = sa.sql_id
   AND ash.user_id = du.user_id
   AND du.username <> 'SYS'
GROUP BY ash.user_id, 
         sa.sql_text, 
         du.username
ORDER BY 4 DESC

4. Identify Top Segments with Performance Issues

This query shows the database segments (tables, indexes, etc.) that are associated with the most wait time in the last day.

Below SQL helps identify hot objects that are experiencing contention or performance issues.

SELECT do.object_name,
       do.object_type,
       ash.event,
       SUM(ash.wait_time + ash.time_waited) ttl_wait_time
  FROM v$active_session_history ash,
       dba_objects do
 WHERE ash.sample_time 
                   BETWEEN SYSDATE - 1 AND SYSDATE
   AND ash.current_obj# = do.object_id
GROUP BY do.object_name, do.object_type, ash.event
ORDER BY 4 DESC;

5. Find Top SQL Queries Causing I/O Load

Identifies SQL queries that are experiencing the most User I/O wait events in the last hour. The second query displays the execution plan for a given SQL_ID.

Below query highlights queries that are I/O bound. The execution plan helps understand how the query accesses data and identify potential optimizations.

--Top SQL Queries Causing I/O Load
SELECT sql_id, COUNT(1)
  FROM gv$active_session_history ash, gv$event_name evt
 WHERE ash.sample_time > SYSDATE - 1/24
   AND ash.session_state = 'WAITING'
   AND ash.event_id = evt.event_id
   AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
--Get Plan table for the SQL_ID
SELECT * FROM TABLE(dbms_xplan.display_cursor(:SQL_ID));

6. Top 10 CPU-Consuming Sessions in the Last 60 Minutes

Below SQL lists the top 10 sessions consuming the most CPU in the last 60 minutes.

The SQL also identifies CPU-intensive sessions that may be impacting overall system performance.

SELECT * FROM (
    SELECT session_id, session_serial#, COUNT(*)
      FROM v$active_session_history
     WHERE session_state= 'ON CPU'
       AND sample_time > SYSDATE - 1/24
    GROUP BY session_id, session_serial#
    ORDER BY COUNT(*) DESC
) WHERE ROWNUM <= 10;

7. Top 10 Waiting Sessions in the Last 60 Minutes

Below SQL lists the top 10 sessions spending the most time waiting in the last 60 minutes.

The SQL also identifies sessions that are blocked or experiencing significant delays.

--Top 10 Waiting Sessions in the Last 60 Minutes
SELECT * FROM (
    SELECT session_id, session_serial#, COUNT(*)
      FROM v$active_session_history
     WHERE session_state= 'WAITING'
       AND sample_time > SYSDATE - 1/24
    GROUP BY session_id, session_serial#
    ORDER BY COUNT(*) DESC
) WHERE ROWNUM <= 10;

8. Find Wait Events for a Specific Session

The SQL retrieves details about a specific session given its SID.

It provides context for a given session ID, including username, machine, and program.

SELECT serial#, username, osuser, machine,
       program, resource_consumer_group,
       client_info
  FROM v$session where sid=&sid;

9. SQL to Find SQL_IDs for a Session

SQL to find the distinct SQL_IDs executed by a specific session within the last 60 minutes.

The SQL helps identify the queries associated with a problematic session

--Find SQL_IDs for a Session
SELECT DISTINCT sql_id, session_serial# 
  FROM v$active_session_history
 WHERE sample_time > sysdate - 1/24
   AND session_id=:sid;

10. Find Full SQL Text

--Query to Find Full SQL Text
SELECT sql_fulltext 
  FROM v$sql 
 WHERE sql_id=:sql_id;

Uncommon Facts About ASH

• ASH data is sampled every second but only retained in memory for a short duration before being purged.
• The ASH feature requires the Diagnostics and Tuning Pack license, which is an additional cost.
• Queries against DBA_HIST_ACTIVE_SESS_HISTORY provide historical data but require careful tuning to avoid performance overhead.
• ASH can be used in Real Application Clusters (RAC) to analyze inter-instance contention issues.

Conclusion

Active Session History is essential for maintaining a healthy Oracle database. It enables performance tuning, security monitoring, and proactive issue resolution. By integrating ASH with other performance tools, DBAs can ensure a smooth and optimized database environment.

You need to keep you database queries tuned as well to keep the system healthy. By reviewing v$sql views and enabing trace and reviewing tkprof SQLs and database objects health can be monitored. You can keep the database queries tuned using v$sql views. You can do the performance tuning of database objects by enabling trace and analyzing tkprof report.