Note
This view was introduced in OceanBase Database V4.2.1.
Purpose
The DBA_WR_ACTIVE_SESSION_HISTORY view displays the ASH data that has been persisted in this tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| CLUSTER_ID | bigint(20) | NO | The ID of the cluster. |
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| SNAP_ID | bigint(20) | NO | The ID of the snapshot. |
| SVR_IP | varchar(46) | NO | The IP address of the node. |
| SVR_PORT | bigint(20) | NO | The port of the node. |
| SAMPLE_ID | bigint(20) | NO | The ID of the sample. |
| SESSION_ID | bigint(20) | NO | The ID of the sampled session. For V4.3.x:
|
| SAMPLE_TIME | timestamp(6) | NO | The sampling time. |
| USER_ID | bigint(20) | YES | The user ID of the sampled session. |
| SESSION_TYPE | tinyint(4) | YES | The session type.
|
| SESSION_STATE | varchar(7) | NO | The state of the session at the sampling moment.
|
| SQL_ID | varchar(32) | YES | The ID of the SQL statement. |
| TRACE_ID | varchar(64) | YES | The trace ID. |
| EVENT_NO | bigint(20) | YES | The internal ID of the waiting event. This field is used for joining with other tables for queries. |
| EVENT_ID | bigint(20) | YES | The ID of the current waiting event.
Note
|
| TIME_WAITED | bigint(20) | YES | The total waiting time of the waiting event in microseconds (us). |
| P1 | bigint(20) | YES | The value of parameter 1 of the waiting event. |
| P2 | bigint(20) | YES | The value of parameter 2 of the waiting event. |
| P3 | bigint(20) | YES | The value of parameter 3 of the waiting event. |
| SQL_PLAN_LINE_ID | bigint(20) | YES | The ID of the SQL operator at the sampling moment. If no SQL operator is found, the value is NULL. |
| GROUP_ID | bigint(20) | YES | The ID of the resource group
Note
|
| PLAN_HASH | bigint(20) unsigned | YES | The plan hash value of the current executing SQL statement
Note
|
| THREAD_ID | bigint(20) | YES | The ID of the thread where the current active session resides
Note
|
| STMT_TYPE | bigint(20) | YES | The SQL type of the current active session
Note
|
| TX_ID | bigint(20) | YES | The ID of the current transaction
Note
|
| BLOCKING_SESSION_ID | bigint(20) | YES | The ID of the session that blocks the current session. This field is effective only in lock conflict scenarios and displays the ID of the session that holds the lock
Note
|
| TIME_MODEL | bigint(20) unsigned | YES | Information about the time model. |
| IN_PARSE | varchar(1) | NO | Whether the current session is parsing an SQL statement at the sampling moment. |
| IN_PL_PARSE | varchar(1) | NO | Whether the current session is parsing an SQL PL statement at the sampling moment. |
| IN_PLAN_CACHE | varchar(1) | NO | Whether the current session is caching a plan at the sampling moment. |
| IN_SQL_OPTIMIZE | varchar(1) | NO | Whether the current session is optimizing an SQL statement at the sampling moment. |
| IN_SQL_EXECUTION | varchar(1) | NO | Whether the current session is executing an SQL statement at the sampling moment. |
| IN_PX_EXECUTION | varchar(1) | NO | Whether the current session is executing an SQL statement in parallel at the sampling moment. When the session is in this state, it is also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | varchar(1) | NO | Whether the current session is loading a value from an auto-increment column or sequence at the sampling moment. |
| IN_COMMITTING | varchar(1) | NO | Whether the sampling point is in the transaction commit phase. |
| IN_STORAGE_READ | varchar(1) | NO | Whether the sampling point is in the storage read phase. |
| IN_STORAGE_WRITE | varchar(1) | NO | Whether the sampling point is in the storage write phase. |
| IN_REMOTE_DAS_EXECUTION | varchar(1) | NO | Whether the sampling point is in the remote DAS execution phase. |
| IN_FILTER_ROWS | varchar(1) | NO | Whether the sampling point is in the storage pushdown execution phase
Note
|
| IN_RPC_ENCODE | varchar(1) | NO | Whether the current SQL statement is being serialized.
Note
|
| IN_RPC_DECODE | varchar(1) | NO | Whether the current SQL statement is being deserialized.
Note
|
| IN_CONNECTION_MGR | varchar(1) | NO | Whether the current SQL statement is establishing a connection.
Note
|
| PROGRAM | varchar(64) | YES | The name of the program being executed at the sampling point:
Note
|
| MODULE | varchar(64) | YES | The value of the MODULE field of the current session at the sampling point, which is set by the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | varchar(64) | YES | The value of the ACTION field of the current session at the sampling point, which is set by the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | varchar(64) | YES | The value of the CLIENT_ID field of the current session at the sampling point, which is set by the DBMS_SESSION.set_identifier package. |
| BACKTRACE | varchar(512) | YES | The code call stack when the event occurred, which is used for debugging. |
| PLAN_ID | bigint(20) | YES | The plan ID of the sampled SQL statement in the plan cache. This field is used to associate the sampling point with the plan. |
| TM_DELTA_TIME | bigint(20) | YES | The time interval for calculating the time model, in microseconds
Note
|
| TM_DELTA_CPU_TIME | bigint(20) | YES | The CPU time spent in the past TM_DELTA_TIME period, in microseconds
Note
|
| TM_DELTA_DB_TIME | bigint(20) | YES | The database call time spent in the past TM_DELTA_TIME period, in microseconds
Note
|
| TOP_LEVEL_SQL_ID | varchar(32) | YES | The ID of the top-level SQL statement
Note
|
| IN_PLSQL_COMPILATION | varchar(1) | NO | The PL compilation status: Y/N
Note
|
| IN_PLSQL_EXECUTION | varchar(1) | NO | The PL execution status: Y/N
Note
|
| PLSQL_ENTRY_OBJECT_ID | bigint(20) | YES | The OBJECT ID of the top-level PL
Note
|
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | YES | The subprogram ID of the top-level PL
Note
|
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | YES | The subprogram name of the top-level PL
Note
|
| PLSQL_OBJECT_ID | bigint(20) | YES | The ID of the PL being executed
Note
|
| PLSQL_SUBPROGRAM_ID | bigint(20) | YES | The ID of the subprogram being executed
Note
|
| PLSQL_SUBPROGRAM_NAME | varchar(32) | YES | The name of the subprogram being executed
Note
|
| DELTA_READ_IO_REQUESTS | bigint(20) | YES | The number of read I/O requests between two samples
Note
|
| DELTA_READ_IO_BYTES | bigint(20) | YES | The cumulative size of files read between two samples
Note
|
| DELTA_WRITE_IO_REQUESTS | bigint(20) | YES | The number of write I/O requests between two samples
Note
|
| DELTA_WRITE_IO_BYTES | bigint(20) | YES | The cumulative size of files written between two samples
Note
|
| TABLET_ID | bigint(20) | YES | The ID of the tablet being processed by the SQL statement
Note
|
| PROXY_SID | bigint(20) | YES | The ID of the proxy session
Note
|
| TX_ID | bigint(20) | YES | The ID of the current transaction
Note
|
Sample query
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_ACTIVE_SESSION_HISTORY limit 1\G
The returned result is as follows:
*************************** 1. row ***************************
CLUSTER_ID: 10001
TENANT_ID: 1002
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SAMPLE_ID: 740
SESSION_ID: -9223372036854729597
SAMPLE_TIME: 2025-02-28 09:43:43.809287
USER_ID: 200001
SESSION_TYPE: 1
SESSION_STATE: WAITING
SQL_ID:
TRACE_ID: YBXXXXXXXX-000XXXXXXXXXXXX-0-0
EVENT_NO: 29
EVENT_ID: 15102
TIME_WAITED: 199579
P1: 200000
P2: 0
P3: 0
SQL_PLAN_LINE_ID: NULL
GROUP_ID: 0
PLAN_HASH: NULL
THREAD_ID: 82046
STMT_TYPE: NULL
TIME_MODEL: 0
IN_PARSE: N
IN_PL_PARSE: N
IN_PLAN_CACHE: N
IN_SQL_OPTIMIZE: N
IN_SQL_EXECUTION: N
IN_PX_EXECUTION: N
IN_SEQUENCE_LOAD: N
IN_COMMITTING: N
IN_STORAGE_READ: N
IN_STORAGE_WRITE: N
IN_REMOTE_DAS_EXECUTION: N
IN_FILTER_ROWS: N
IN_RPC_ENCODE: N
IN_RPC_DECODE: N
IN_CONNECTION_MGR: N
PROGRAM: T1002_LogService
MODULE: LOCAL INNER SQL EXEC
ACTION: NULL_INNER_SQL
CLIENT_ID: NULL
BACKTRACE: NULL
PLAN_ID: 0
TM_DELTA_TIME: 739151
TM_DELTA_CPU_TIME: 165
TM_DELTA_DB_TIME: 165
TOP_LEVEL_SQL_ID: NULL
IN_PLSQL_COMPILATION: N
IN_PLSQL_EXECUTION: N
PLSQL_ENTRY_OBJECT_ID: NULL
PLSQL_ENTRY_SUBPROGRAM_ID: NULL
PLSQL_ENTRY_SUBPROGRAM_NAME: NULL
PLSQL_OBJECT_ID: NULL
PLSQL_SUBPROGRAM_ID: NULL
PLSQL_SUBPROGRAM_NAME: NULL
BLOCKING_SESSION_ID: NULL
TABLET_ID: NULL
PROXY_SID: -9223372036854729597
TX_ID: NULL
DELTA_READ_IO_REQUESTS: 0
DELTA_READ_IO_BYTES: 0
DELTA_WRITE_IO_REQUESTS: 0
DELTA_WRITE_IO_BYTES: 0
1 row in set (0.093 sec)