Oracle ASH Analysis Guide: Practical Techniques to Diagnose CPU, I/O, and Lock Performance Issues

Introduction
ASH
How to Retrieve ASH Data
1. Key Columns in ASH
2. Example Query
When to Use ASH vs AWR
Common Performance Issues and ASH Analysis
ASH Analysis Tips
Summary

Introduction

When dealing with performance issues in Oracle, have you ever encountered cases such as:

No issues found in AWR
Sudden slowdowns occurring only at specific times
Inability to identify the root SQL or wait events

For these types of “momentary performance degradation” or “low-reproducibility issues,” a powerful analysis tool is ASH (Active Session History).

ASH

First, let’s go over the basics of ASH.

What is ASH?

ASH is historical data that samples the state of active sessions at approximately one-second intervals.

An active session refers to a session that is either:

Actively executing on the CPU, or
Waiting for resources such as I/O or locks

ASH samples these active sessions and records them as historical data.

Specifically, it records information such as:

Executing SQL (SQL_ID)
Session information (SID)
Wait events (EVENT)
CPU usage or WAIT state

While AWR stores aggregated data over several minutes to an hour, ASH collects sample data every second. This makes it particularly effective for analyzing short-term spike issues.

However, since ASH captures only sampled snapshots, it cannot capture operations that complete within less than one second. Also, the 1-second sampling interval cannot be changed.

ASH Data Collection and Storage Mechanism

ASH data is collected and written by a background process called MMNL (Manageability Monitor Lite).

MMNL samples active sessions approximately every second and stores them in the ASH buffer within the SGA. This data can be queried via V$ACTIVE_SESSION_HISTORY.

The data accumulated in the ASH buffer is flushed to AWR (in the SYSAUX tablespace) either:

When the buffer reaches a certain usage threshold, or
When an AWR snapshot is taken

At that time, not all data is saved—only a sampled subset is stored in DBA_HIST_ACTIVE_SESS_HISTORY.

Notes When Using ASH

Short retention period (memory-based storage)
ASH data is stored in a dedicated area within the SGA (ASH buffer), which uses a ring buffer structure. Its size is dynamically determined based on the SGA size. Older data is automatically overwritten, so long-term retention is not possible.

Therefore, it is critical to analyze ASH within its retention window when issues occur. Increasing the SGA size may indirectly extend retention, but there is no parameter to explicitly control it. If long-term retention is required, you must periodically extract and store ASH data via scripts.

Use AWR for long-term analysis
AWR stores data persistently in snapshots, making it suitable for long-term trend analysis. Note that using AWR requires a Diagnostic Pack license.

How to Retrieve ASH Data

Let’s look at how to retrieve ASH data and what kind of information is available.

Key Columns in ASH

Because ASH contains many columns, it is recommended to focus on key ones:

Column	Description
SAMPLE_TIME	The timestamp when the sample was taken. It allows analysis of events occurring within a specific time period.
SESSION_ID	The session identifier assigned internally by Oracle. Since this value can be reused, it should be combined with SESSION_SERIAL# to uniquely identify a session.
SESSION_SERIAL#	The session serial number.
SESSION_TYPE	Indicates whether the session is a user process or an Oracle internal process. FOREGROUND: User process BACKGROUND: Oracle internal process
USER_ID	Information about the executing user. It helps distinguish whether the process is from an online application or a batch job. For example, if it shows “JDBC Thin Client,” it indicates execution from a Java application.
SQL_ID	Identifier of the SQL statement being executed at the time of sampling. Used to identify the SQL under analysis.
SQL_CHILD_NUMBER	Identifier of the child cursor of the SQL statement executed at sampling time. Child cursor: Stores individual execution plans generated for the same SQL statement (SQL_ID).
TOP_LEVEL_SQL_ID	Identifier of the top-level SQL statement. Useful for identifying the calling SQL in batch jobs or PL/SQL executions.
EVENT	The name of the wait event. A critical field for identifying the cause of SQL performance issues. Typical examples: • db file sequential read (I/O): Wait for single-block reads • enq: TX – row lock contention (Lock): Wait due to row-level lock contention
WAIT_CLASS	Classification of waits. Typical categories include: • User I/O: User I/O waits • System I/O: Background process I/O waits • Concurrency: Internal database resource waits (locks/latches) • Cluster: RAC-related waits Reference: Classes of Wait Events
SESSION_STATE	Indicates the session state. WAITING: Waiting for disk I/O, locks, etc. ON CPU: Actively executing on the CPU
TIME_WAITED	When SESSION_STATE = WAITING, this shows the actual time spent waiting for the event (in microseconds).
BLOCKING_SESSION_STATUS	Status of the blocking session. VALID: Blocking session identified NO HOLDER: No blocking session exists UNKNOWN: Blocking session cannot be identified NOT IN WAIT: Session is not waiting
BLOCKING_SESSION	The session identifier of the blocking session.

Example Query

SELECT
    sample_time,
    session_id,
    session_serial#,
    session_type,
    user_id,
    sql_id,
    sql_child_number,
    top_level_sql_id,
    event,
    wait_class,
    session_state,
    time_waited,
    blocking_session_status,
    blocking_session
FROM
    v$active_session_history
WHERE
    sample_time BETWEEN SYSDATE - INTERVAL '1' MINUTE AND SYSDATE
ORDER BY
    sample_time;

When to Use ASH vs AWR

Both ASH and AWR are used for performance analysis, but their purposes differ:

AWR: Long-term trend analysis (macro perspective)
ASH: Short-term detailed analysis (micro perspective)

A common approach is:

Use AWR to identify problematic time periods
Use ASH to analyze detailed session activity during those periods

Common Performance Issues and ASH Analysis

CPU Bottleneck

If ASH shows:

SESSION_STATE = ON CPU
EVENT = NULL

Then the system is CPU-bound, not waiting. The root cause may be heavy processing or inefficient SQL, requiring execution plan analysis.

I/O Bottleneck

If ASH shows wait events like:

direct path read
db file sequential read
db file scattered read

Then the system is I/O-bound. This often occurs during full table scans or large data reads. Adding indexes or tuning SQL may help.

Lock Contention

If ASH shows:

enq: TX – row lock contention

Then sessions are waiting due to row-level locking conflicts.

Typical solutions include:

Shortening transactions
Reducing contention by distributing updates

ASH Analysis Tips

ASH is sampled data (1-second intervals)
Short-lived operations may not be captured
Long-running operations may appear more frequently (sampling bias)
Always narrow analysis to the relevant time window
Evaluate proportions of events and SQL activity

Summary

ASH (Active Session History) is a powerful tool that records the state of active sessions every second, allowing you to visualize what was happening inside the database at a specific moment.

With ASH, you can:

Distinguish between CPU usage and waiting states
Identify bottlenecks via wait events (I/O, locks, redo, etc.)
Pinpoint problematic SQL using SQL_ID
Identify blocking sessions

At the same time, keep in mind its limitations:

Cannot capture operations shorter than one second
Sampling bias toward long-running processes
Short retention due to memory storage

In practice, the best approach is:

Use AWR for identifying trends and time windows
Use ASH for detailed, moment-by-moment analysis

ASH helps answer:
“Who was doing what, at which moment, and what were they waiting for?”

Use it effectively to improve the accuracy and efficiency of your performance troubleshooting.