Oracle databases provide several dynamic performance views for monitoring session activity, performance tuning, and troubleshooting. Key views include v$session, v$active_session_history, and dba_hist_active_session_history. For multi-node environments, gv$session and gv$active_session_history offer consolidated information across all instances.

v$session

The v$session view provides real-time information about currently connected sessions. Each row represents an active session, and entries are created upon connection and removed upon disconnection. This view reflects the dynamic state of the database's connections.

v$active_session_history

v$active_session_history (and its multi-node counterpart gv$active_session_history) captures snapshots of active sessions. The database samples active sessions approximately every second, storing this data in memory. This view is fundamental to Active Session History (ASH) and retains information for about an hour. Its populated from the X$ASH base table, which resides in the Shared Pool. The size of this memory buffer is controlled by the hidden parameter _ash_size, and the sampling interval by _ash_sampling_interval.

Data Flow for ASH:

1. The MMNL process collects session information (from X$KSUSE, the base table for gv$session) and writes it to ASH buffers, accessible via X$ASH. This occurs by default every second.
2. The gv$active_session_history view reads data from X$ASH.
3. The MMON process persists the data from gv$active_session_history into the base table WRH$_ACTIVE_SESSION_HISTORY.
4. The dba_hist_active_session_history view retrieves data from WRH$_ACTIVE_SESSION_HISTORY.
5. AWR reports draw their ASH data from dba_hist_active_session_history.

It's important to distinguish between ASH and AWR. ASH focuses on in-memory, real-time sampling of active sessions, while AWR provides historical, disk-based performance data. The dba_hist_active_session_history view serves as the historical storage for ASH data.

Key Hidden Parameters for ASH:

• _ash_disk_filter_ratio: Frequency of sampling into DBA_HIST_ACTIVE_SESS_HISTORY (default: 10 seconds).
• _ash_disk_write_enable: Controls whether data is written to DBA_HIST_ACTIVE_SESS_HISTORY.
• _ash_eflush_trigger: Threshold for flushing ASH buffers (default: 66%).
• _ash_enable: Enables or disables ASH sampling.
• _ash_sample_all: If true, samples all sessions, including idle ones (default: false).
• _ash_sampling_interval: Interval between ASH samples in milliseconds (default: 1000ms).
• _ash_size: Size of the ASH buffer in bytes.

Monitoring v$ash_info can reveal insights into ASH buffer usage and emergency flushes:

SELECT total_size/1024/1024 AS buffer_size_mb, awr_flush_emergency_count
FROM v$ash_info;

An excessively small _ash_size can lead to frequent flushes, impacting performance. Oracle recommends an _ash_size approximately 1.5 times the total_size for optimal performance.

dba_hist_active_session_history

This view stores the historical sample data collected by ASH. While v$active_session_history holds data in memory for a limited duration, dba_hist_active_session_history provides persistent storage of this information, enabling post-event analysis.

Data Flow Summary

[gv$session (via X$KSUSE)] -> [ASH Buffers (X$ASH)] -> [gv$active_session_history] -> [MMON Process] -> [WRH$_ACTIVE_SESSION_HISTORY (Base Table)] -> [dba_hist_active_session_history]

It's crucial to remember that dba_hist_active_session_history samples from v$active_session_history periodical (e.g., every 10 seconds). If a SQL statement runs for 100 seconds, it might be sampled 100-101 times in v$active_session_history but only 10-11 times in dba_hist_active_session_history.

Use Cases

Detecting Real-time Blocking Sessions with v$session

v$session can be queried to identify and visualize session blocking chains, similar to what is presented in Oracle Enterprise Manager (EM).

SELECT
    LPAD(' ', 5 * LEVEL - 1) || S.USERNAME AS user_name,
    LPAD(' ', 5 * LEVEL - 1) || S.SID AS session_id,
    S.SERIAL#,
    S.SQL_ID,
    S.ROW_WAIT_OBJ#,
    S.WAIT_CLASS,
    S.EVENT,
    S.P1,
    S.P2,
    S.P3,
    S.SECONDS_IN_WAIT
FROM
    V$SESSION S
WHERE
    S.BLOCKING_SESSION IS NOT NULL
    OR S.SID IN (SELECT DISTINCT BLOCKING_SESSION FROM V$SESSION)
START WITH S.BLOCKING_SESSION IS NULL
CONNECT BY PRIOR S.SID = S.BLOCKING_SESSION;

This query reconstructs the blocking hierarchy, highlighting sessions that are either blocking others or are themselves blocked.

Analyzing Historical Blocking and Performance with v$active_session_history and dba_hist_active_session_history

When investigating past performance issues or unexpected behavior, these views are invaluable. They allow administrators to reconstruct the activity timeline and pinpoint the root causes of problems that may have occurred when they were not actively monitoring the system. Analyzing dba_hist_active_session_history provides insights into historical wait events, session activity, and resource consumption over time.