This topic uses three beginner-friendly experiments and three analysis dimensions, combined with real failure cases, to demonstrate how to quickly locate performance issues such as sudden CPU spikes, I/O bottlenecks, and distributed execution blockages by leveraging the [G]V$OB_ACTIVE_SESSION_HISTORY view.
How ASH works
ASH acts like an automatic video recorder for the database, taking status snapshots every second of all active sessions (such as those executing SQL). These snapshots are stored in the system view GV$OB_ACTIVE_SESSION_HISTORY. The specific implementation principles are as follows:
Active session filtering mechanism
Tracks all tasks executed in the database and assigns them a unique identifier, session_id, including:
- User client connections to the database for executing SQL requests.
- Internal RPC executions.
- Background thread task executions, such as minor compaction threads, clog threads, timer threads, etc.
Only records the status of active sessions; idle sessions are not recorded, including:
- Sessions that are currently executing SQL are considered active. If a session is in the Sleep state and not processing SQL requests, it is considered idle and will not be recorded.
- Background threads that are not performing tasks or waiting for new task scheduling are considered idle and will not be recorded.
- Sessions waiting for resources (such as locks, disk I/O) have their wait events (e.g., db file data read) marked by ASH.
Periodic sampling mechanism
Inside each OBServer, there is a dedicated ASH thread that accesses all active sessions in the database every second and records their status, where:
- The data in each row of
GV$OB_ACTIVE_SESSION_HISTORYrepresents the status of an active session at a certain moment. - If a session's working time is very short (for example, less than 1 second), it is like a person blinking in a photograph and may not be captured by ASH. For such scenarios, it is recommended to increase the load and expand the query time range to make ASH statistics more reliable.
- The data in each row of
Circular buffer design
ASH snapshot data is stored in a 30MB circular buffer. When the stored data exceeds 30MB, the oldest data is automatically overwritten. When we want to save the latest ASH records, we can manually trigger a WR snapshot:
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();After running this command, the ASH snapshot data that has not been persisted to WR at the current moment is persisted at a ratio of 10:1.
ASH getting started experiments
We have prepared three experiments for beginners to quickly get started with ASH by opening the database's ASH timeline.
Experiment 1: View the database status in the last 10 seconds (real-time monitoring)
Check what the database has been busy with in the last 10 seconds:
-- View the system's runtime status in the last 10 seconds.
SELECT
sample_time AS `Time`, -- Timestamp accurate to microseconds
session_id AS `Session ID`, -- Unique identifier for the session
CASE WHEN session_state = 'ON CPU' THEN 'Working' ELSE 'Waiting' END AS `Status`, -- Work status: CPU busy or waiting for resources
event AS `Waiting Reason` -- Specific waiting event (e.g., lock, I/O, etc.)
FROM v$ob_active_session_history
WHERE sample_time > now() - 10 -- Last 10 seconds
AND session_type="FOREGROUND"
ORDER BY sample_time DESC;
The result is as follows:
+----------------------------+------------+-----------+------------------------+
| Time | Session ID | Status | Waiting Reason |
+----------------------------+------------+-----------+------------------------+
| 2025-06-11 20:16:15.307564 | 3221931170 | Waiting | px loop condition wait |
| 2025-06-11 20:16:14.285204 | 3221928286 | At work | |
| 2025-06-11 20:16:14.285204 | 3221923503 | Waiting | wait in request queue |
| 2025-06-11 20:16:14.285204 | 3221923627 | Waiting | db file data read |
| 2025-06-11 20:16:14.285204 | 3221927472 | Waiting | sync rpc |
| 2025-06-11 20:16:13.262695 | 3221929034 | At work | |
| 2025-06-11 20:16:12.240768 | 3221927472 | At work | |
+----------------------------+------------+-----------+------------------------+
Discover:
- Session 3221931170 is waiting for px to complete execution (px loop condition wait).
- Session 3221923627 is waiting for read I/O to complete (db file data read).
- Session 3221927472 was working at 20:16:12, and 2 seconds later, it was waiting for an RPC result (sync rpc).
Experiment 2: Count the most active sessions in the last 10 minutes (load distribution)
View the sessions that were busy in the last 10 minutes:
-- Count the most active sessions in the last 10 minutes.
SELECT
session_id AS `Session ID`,
COUNT(*) AS `Working Seconds`
FROM v$ob_active_session_history
WHERE sample_time > now() - 600 -- Last 10 minutes
AND session_type='FOREGROUND'
GROUP BY session_id
ORDER BY `Working Seconds` DESC limit 3;
The result is as follows:
+------------+--------------+
| Session ID | Working Seconds |
+------------+--------------+
| 3221977564 | 283 |
| 3221972645 | 142 |
| 3221916432 | 77 |
+------------+--------------+
It is found that session 3221977564 was active for 283 seconds in the last 10 minutes. If these three sessions were the only ones in the past period, then the contribution of session 3221977564 to the total database load would be 283 / (283 + 142 + 77) = 56%.
Experiment 3: Locate high-load SQL statements from historical periods (performance analysis)
View the busiest SQL statements from a past period:
-- Query the sql_id with the highest execution load from the past period.
SELECT
SQL_ID,
COUNT(*) AS `Working Seconds`
FROM v$ob_active_session_history
WHERE sample_time BETWEEN
'2025-06-11 10:32:08'
AND '2025-06-11 11:32:07' -- Modify this to the actual period you want to observe.
GROUP BY sql_id
ORDER BY `Working Seconds` DESC limit 3;
The result is as follows:
+----------------------------------+--------------+
| SQL_ID | Working Seconds |
+----------------------------------+--------------+
| 1D0BA376E273B9D622641124D8C59264 | 91265 |
| 19AAD9F2FE3CE0023298AB83F7E75775 | 13608 |
| 7BE7497CCCFE8978AD6B92A938D43929 | 13098 |
+----------------------------------+--------------+
It is found that the busiest SQL statement from the past period is 1D0BA376E273B9D622641124D8C59264, which was executed for 91265 seconds.
ASH analysis method
The V$OB_ACTIVE_SESSION_HISTORY view provides a second-level full session snapshot, allowing you to trace the operational status of the database for the past 7 days without any prior configuration. The following three dimensions are the core metrics that this topic focuses on analyzing, as they collectively depict the operational health of the database:
- AAS (Average Active Sessions): The average number of sessions per second in the database, reflecting the database load. A higher AAS indicates greater load.
- Wait Events: When an active session is waiting for a system resource, such as locks, network RPCs, I/O, etc., it enters the wait-event state. Conversely, sessions that are not in the wait-event state are in the working state (ON_CPU).
- Execution Phases: SQL statements go through various phases when executed in the database, such as IN_PARSE, IN_SQL_OPTIMIZE, IN_SQL_EXECUTION, IN_STORAGE_READ, etc. During execution, SQL also mobilizes various resources and enters different states, such as IN_RPC_ENCODE, IN_CONNECTION_MGR, IN_SEQUENCE_LOAD, etc.
Through the ASH analysis method, you can restore the overall operational status of the database over historical periods, rather than just focusing on the micro-dimension of a specific database represented by monitoring metrics. This is the biggest difference between ASH and traditional monitoring.
AAS analysis
ASH only records active sessions in the database system. Based on this, you can calculate the number of active sessions per second in the database. A higher number of active sessions indicates a busier system and higher load. The AAS metric shows the average number of sessions per second, enabling you to understand the database load with second-level precision.
You can query AAS as follows:
SELECT
DATE_FORMAT(SAMPLE_TIME, '%Y-%m-%d %H:%i') AS TIME_SLOT,
-- Divide by intervals of one minute.
COUNT(*) / 60 as AAS
-- Average number of active sessions per minute.
FROM V$OB_ACTIVE_SESSION_HISTORY
WHERE sample_time BETWEEN
'2025-03-13 10:32:08'
AND '2025-03-13 10:35:07' -- Modify the time to the period you want to observe.
GROUP BY TIME_SLOT ORDER BY TIME_SLOT;
The result is as follows:
+------------------+------+
| TIME_SLOT | AAS |
+------------------+------+
| 2025-03-13 10:32 | 2 |
| 2025-03-13 10:33 | 13 |
| 2025-03-13 10:34 | 15 |
| 2025-03-13 10:35 | 1 |
+------------------+------+
During the query period, the number of active sessions surged from 10:33 to 10:34. This could be due to sudden user SQL traffic or excessive system resource consumption by suddenly scheduled background tasks. Whether a resource bottleneck actually occurred needs to be comprehensively evaluated considering factors such as tenant specifications and system status. Note that the V$OB_ACTIVE_SESSION_HISTORY view only retains historical snapshot data from the most recent period of the system. Therefore, before querying, it is necessary to confirm whether the historical data in the view has been overwritten.
Below, an actual case illustrates how AAS can be applied in problem diagnosis and troubleshooting.
Case 1: Locate a sudden CPU spike caused by background tasks
An internet company uses OceanBase AI Database with a tenant configuration of 3c12g. The database began experiencing abnormally high CPU usage starting at 8:00 AM. After receiving an OCP alert, the company upgraded the database tenant configuration to 6c20g around 8:14, but the tenant CPU was still fully utilized. The issue persisted for 2 hours before disappearing. During the fault, OCP showed no significant change in user QPS.
You can query the ASH view to locate the cause of the load mutation. Since the fault period has already been identified in OCP, we can directly analyze the AAS during that period.
SELECT
SVR_IP,
SVR_PORT,
SESSION_TYPE,
COUNT(*) as AAS
-- Total active session time divided by duration equals AAS.
FROM V$OB_ACTIVE_SESSION_HISTORY
WHERE sample_time BETWEEN
'2025-03-13 8:00:00'
AND '2025-03-13 10:00:00'
GROUP BY SVR_IP, SVR_PORT, SESSION_TYPE ORDER BY AAS DESC limit 5;
The query result is as follows:
+--------------+----------+--------------+-------+
| SVR_IP | SVR_PORT | SESSION_TYPE | AAS |
+--------------+----------+--------------+-------+
| xx.xx.xx.x6 | 46775 | BACKGROUND | 59633 |
| xx.xx.xx.x6 | 46775 | FOREGROUND | 1324 |
| xx.xx.xx.x5 | 46779 | FOREGROUND | 512 |
| xx.xx.xx.x9 | 46771 | FOREGROUND | 412 |
| xx.xx.xx.x9 | 46771 | BACKGROUND | 403 |
| xx.xx.xx.x5 | 46779 | BACKGROUND | 379 |
+--------------+----------+--------------+-------+
It should be noted that since OceanBase AI Database is a distributed database, it generally consists of multiple nodes. Therefore, when querying ASH, it is best to include the SVR_IP and SVR_PORT fields, as these two fields uniquely identify an OBServer node.
SESSION_TYPE identifies the type of session. There are only two types: FOREGROUND and BACKGROUND. A FOREGROUND session is created when a user establishes a link with OceanBase AI Database and is used to execute SQL requests sent by the user. A BACKGROUND session executes internal tasks of OceanBase AI Database, including minor compactions, RPCs, PX tasks, etc.
From the query results, it is not difficult to see that the load is mainly concentrated on the xx.xx.xx.x6:46775 node, where AAS = 59633 / (3600 * 2) = 8.28, which far exceeds the tenant specification of 3 CPUs. Even after scaling up to 6 CPUs, it would be difficult to meet the needs of the background sessions.
Next, we need to investigate which specific background task on the xx.xx.xx.x6:46775 node is consuming so much CPU for the tenant. In ASH, there are many fields that can identify the attributes of background sessions. Commonly used ones include PROGRAM and GROUP_ID. PROGRAM identifies the attribute of the session, and GROUP_ID identifies which resource group the session belongs to. Therefore, we construct the following SQL statement:
SELECT
PROGRAM,
GROUP_ID,
COUNT(*) as CNT
FROM V$OB_ACTIVE_SESSION_HISTORY
WHERE SESSION_TYPE = 'BACKGROUND'
AND SVR_IP = 'xx.xx.xx.x6'
AND SVR_PORT = '46775'
AND sample_time BETWEEN
'2025-03-13 8:00:00'
AND '2025-03-13 10:00:00'
GROUP BY PROGRAM, GROUP_ID ORDER BY CNT DESC limit 10;
The query result is as follows:
+-------------------------+----------+-------+
| PROGRAM | GROUP_ID | CNT |
+-------------------------+----------+-------+
| T1002_RPC_REQUEST | 28 | 53278 |
| T1002_LogService | 0 | 2793 |
| T1002_DAG | 0 | 1425 |
| T1002_IOManager | 0 | 1268 |
| T1002_LSRecoveryService | 0 | 438 |
+-------------------------+----------+-------+
After querying, it was found that on the xx.xx.xx.x6:46775 node, the main background load pressure comes from RPC execution, and its resource group is 28. Resource group 28 is a dedicated resource group for internal OBServer tasks and is allocated to the DBMS_SCHEDULER module. Thus, we reconstructed the database scene during the fault period: for some reason, the DBMS_SCHEDULER module sent a large number of RPC requests to the xx.xx.xx.x6:46775 node, causing the OBServer node's CPU usage to spike. Subsequent investigation confirmed that DBMS_SCHEDULER ran an SQL statistics collection task at 8 a.m. Due to a configuration error, all statistics collection tasks were scheduled to run on the same node, leading to the sudden increase in CPU usage.
Traditional methods for troubleshooting this issue would take a long time. Since monitoring does not provide much additional information, one would have to start with the OBServer runtime logs, analyze the log exceptions, and gradually deduce the issue to the DBMS_SCHEDULER module. In contrast, although ASH does not have dedicated monitoring configurations for the DBMS_SCHEDULER module, by combining the AAS dimension with background attributes, the root cause of the fault was successfully located.
Wait event analysis
Any active session in a database, during execution, is either in a waiting state—waiting for a resource to become available or a condition to be met before it can continue—or in the ON CPU state (for example, parsing SQL statements, processing SQL operators, waiting for memory access to complete, etc.). In OceanBase AI Database, we use wait events to mark an active session as being in a waiting state. Clearly, identifying which wait events occurred during the execution of a database session and what their time proportion is is crucial for database tuning.
We can use SQL to query the proportion of wait events for foreground sessions within a certain period:
SELECT
SESSION_STATE,
EVENT,
COUNT(*) as CNT
FROM V$OB_ACTIVE_SESSION_HISTORY
WHERE SESSION_TYPE = 'FOREGROUND'
AND SVR_IP = 'xx.xx.xx.x7'
AND SVR_PORT = '2828'
AND sample_time BETWEEN
'2025-06-14 8:15:00'
AND '2025-06-14 10:31:00'
GROUP BY SESSION_STATE, EVENT ORDER BY CNT DESC limit 10;
The query result is as follows:
+---------------+--------------------------------------------------------+--------+
| SESSION_STATE | EVENT | CNT |
+---------------+--------------------------------------------------------+--------+
| WAITING | wait in request queue | 227184 |
| ON CPU | | 76111 |
| WAITING | tx commiting wait | 54866 |
| WAITING | exec inner sql wait | 10292 |
| WAITING | sync rpc | 6602 |
| WAITING | px loop condition wait | 3234 |
| WAITING | db file data read | 395 |
| WAITING | row lock wait | 158 |
| WAITING | default condition wait | 143 |
+---------------+--------------------------------------------------------+--------+
As seen, during the query period, the tenant queue backlog was very severe (wait in request queue), which greatly affected SQL execution time.
Case 2: Locate disk I/O bottlenecks
An educational company experienced increased database request latency one afternoon, leading to customer complaints. The DBA discovered through OCP monitoring that the data disk read bandwidth was full during the fault period, and speculated that the high latency was caused by excessive disk reads by SQL queries. However, observing the TOP SQL during the fault period revealed no difference from normal times, making it impossible to determine which SQL statement was causing the disk read traffic.
You can query ASH wait events to locate which SQL statements are related to the disk read traffic:
OceanBase(root@oceanbase)>select svr_ip, svr_port, con_id as tenant_id, sql_id, event, count(*) as cnt from GV$OB_ACTIVE_SESSION_HISTORY ash where wait_class in ('SYSTEM_IO', 'USER_IO') and sample_time between "2025-01-29 10:35:45" and "2025-01-29 10:45:47" group by svr_ip, svr_port, tenant_id, sql_id, event order by cnt desc limit 100;
The query result is as follows:
+----------------+----------+-----------+------------------------------------+-----------------------+-------+
| svr_ip | svr_port | tenant_id | sql_id | event | cnt |
+----------------+----------+-----------+------------------------------------+-----------------------+-------+
| xx.xx.xx.xx | 2882 | 1002 | 18AB15790D884E6A4823C892456E8650 | db file data read | 24511 |
| xx.xx.xx.xx | 2882 | 1002 | DBCB5D52A5E14718345165081BC7EAE4 | db file data read | 17859 |
| xx.xx.xx.xx | 2882 | 1002 | 32AB97A0126F566064F84DDDF4936F82 | db file data read | 15463 |
| xx.xx.xx.xx | 2882 | 1002 | 752F9D90CE614E180E965438E4B5A87D | db file data read | 11674 |
| xx.xx.xx.xx | 2882 | 1002 | NULL | palf write | 1581 |
| xx.xx.xx.xx | 2882 | 1002 | NULL | db file compact read | 193 |
+----------------+----------+-----------+------------------------------------+-----------------------+-------+
As seen, four SQL statements have a large number of I/O-related wait events during execution, which significantly slows down SQL response time.
Execution phase analysis
The execution flow of an SQL statement is divided into many phases. On v$ob_active_session_history, 17 columns are used to represent these execution phases. If an SQL statement is in that phase at the sampling moment, the column value is 'Y'; if not, the column value is 'N'.
- IN_PARSE: The system is performing syntax parsing on the SQL statement.
- IN_PL_PARSE: The system is parsing PL/SQL code (such as stored procedures or functions).
- IN_PLAN_CACHE: The system is retrieving or generating an execution plan for the SQL.
- IN_SQL_OPTIMIZE: The system is optimizing the SQL execution path.
- IN_SQL_EXECUTION: SQL is being executed (such as accessing data or calculating results).
- IN_PX_EXECUTION: The system is executing SQL in parallel across multiple threads or nodes (parallel query).
- IN_SEQUENCE_LOAD: The system is generating unique values for auto-increment columns (such as the ID field) or sequences.
- IN_COMMITTING: The system is committing a transaction.
- IN_STORAGE_READ: The system is reading data from the storage engine (such as tables or indexes).
- IN_STORAGE_WRITE: The system is writing data to the storage engine.
- IN_REMOTE_DAS_EXECUTION: The system is using the DAS execution engine for distributed execution.
- IN_FILTER_ROWS: The system's storage engine is executing pushdown operators (such as filtering or aggregation).
- IN_RPC_ENCODE: The system is encoding request/response data.
- IN_RPC_DECODE: The system is decoding request/response data.
- IN_CONNECTION_MGR: The system is handling session creation or closure (connection pool management).
- IN_PLSQL_COMPILATION: The system is compiling PL/SQL code (such as stored procedures).
- IN_PLSQL_EXECUTION: The system is executing PL/SQL code (such as stored procedures).
In OceanBase AI Database, events are mutually inclusive when a session is waiting for one. For example, a session in the IN_SQL_EXECUTION phase may be accessing the storage layer, meaning it is also in the IN_STORAGE_READ or IN_STORAGE_WRITE phase.
Case 3: Identify bottlenecks in distributed execution
A mid-end business experienced user operation lag in the morning. Monitoring showed that the CPU of node xx.xx.xx.x9 in the database cluster was continuously at full load, causing SQL execution delays to soar. After the DBA attempted to switch the primary node and restart the node, the CPU usage returned to normal.
Upon post-fault investigation, since the audit logs (SQL Audit) were lost due to the restart during the fault, we analyzed the session status during the fault period (10:30 to 10:45) using ASH (Active Session History) data and made the following key findings:
OceanBase(root@oceanbase)>select svr_ip, tenant_id, IN_REMOTE_DAS_EXECUTION, name.name as event, count(*) * 10 as cnt from CDB_WR_ACTIVE_SESSION_HISTORY ash left join v$event_name name on ash.event_no=name.`event#` where sample_time between "2024-11-29 10:30:45" and "2024-11-29 10:45:45" and tenant_id=1002 group by svr_ip, tenant_id, IN_REMOTE_DAS_EXECUTION, event order by cnt desc limit 20;
The query result is as follows:
+---------------+-----------+-------------------------+--------------------------+------+
| svr_ip | tenant_id | IN_REMOTE_DAS_EXECUTION | event | cnt |
+---------------+-----------+-------------------------+--------------------------+------+
| xx.xx.xx.x9 | 1002 | Y | | 48330 |
| xx.xx.xx.x4 | 1002 | N | das wait remote response | 28920 |
| xx.xx.xx.x1 | 1002 | N | das wait remote response | 20050 |
| xx.xx.xx.x9 | 1002 | N | default condition wait | 2460 |
| xx.xx.xx.x4 | 1002 | N | default condition wait | 1450 |
| xx.xx.xx.x1 | 1002 | N | px loop condition wait | 1420 |
| xx.xx.xx.x9 | 1002 | N | px loop condition wait | 1330 |
| xx.xx.xx.x9 | 1002 | N | | 1170 |
| xx.xx.xx.x4 | 1002 | N | | 900 |
| xx.xx.xx.x1 | 1002 | N | default condition wait | 830 |
| xx.xx.xx.x4 | 1002 | N | default condition wait | 570 |
| xx.xx.xx.x4 | 1002 | N | px loop condition wait | 530 |
| xx.xx.xx.x9 | 1002 | Y | | 520 |
+---------------+-----------+-------------------------+--------------------------+------+
Key findings are as follows:
Node IP |
Critical phase/waiting event |
Number of sessions (percentage) |
|---|---|---|
| xx.xx.xx.x9 | Remote DAS Task (IN_REMOTE_DAS_EXECUTION) | 48,330 (far exceeding other nodes) |
| Other Nodes | Wait for Remote Response (das wait remote response) | 28,920 times, 20,050 times |
The following conclusions are drawn:
Root cause:
- The faulty node (xx.xx.xx.x9) was at full CPU load because it was simultaneously processing a large number of remote DAS requests (from other nodes).
- Other nodes (such as xx.xx.xx.x4 and xx.xx.xx.x1) occupied a large number of worker threads waiting for responses from this node, leading to queue backlogs and further exacerbating system congestion.
Underlying cause:
Through correlation analysis in the SQL dimension, it was found that two scheduled AP business SQL statements (such as batch data synchronization tasks) were triggered in the morning and consumed excessive CPU resources during concurrent execution, competing with normal business SQL for resources.