Note
This view was introduced in OceanBase Database V4.2.1.
Purpose
The DBA_WR_ACTIVE_SESSION_HISTORY view displays the ASH data persisted in the tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| CLUSTER_ID | bigint(20) | NO | Cluster ID |
| TENANT_ID | bigint(20) | NO | Tenant ID |
| SNAP_ID | bigint(20) | NO | Snapshot ID |
| SVR_IP | varchar(46) | NO | Node IP |
| SVR_PORT | bigint(20) | NO | Node port |
| SAMPLE_ID | bigint(20) | NO | Sampling ID |
| SESSION_ID | bigint(20) | NO | ID of the sampled session For V4.3.x:
|
| SAMPLE_TIME | timestamp(6) | NO | Sampling time |
| USER_ID | bigint(20) | YES | User ID of the sampled session |
| SESSION_TYPE | tinyint(4) | YES | Type of the session
|
| SESSION_STATE | varchar(7) | NO | Status of the session at the sampling moment
|
| SQL_ID | varchar(32) | YES | SQL ID |
| TRACE_ID | varchar(64) | YES | TRACE_ID |
| EVENT_NO | bigint(20) | YES | Internal ID of the waiting event, which is used for joining with other tables |
| EVENT_ID | bigint(20) | YES | ID of the current waiting event
Note
|
| TIME_WAITED | bigint(20) | YES | Total waiting time of the waiting event, in microseconds (us) |
| P1 | bigint(20) | YES | Value of parameter 1 of the waiting event |
| P2 | bigint(20) | YES | Value of parameter 2 of the waiting event |
| P3 | bigint(20) | YES | Value of parameter 3 of the waiting event |
| SQL_PLAN_LINE_ID | bigint(20) | YES | ID of the corresponding SQL operator when the sampling is performed. If no corresponding operator is found, the value is NULL. |
| GROUP_ID | bigint(20) | YES | ID of the resource group
Note
|
| PLAN_HASH | bigint(20) unsigned | YES | Plan hash value of the current SQL statement
Note
|
| THREAD_ID | bigint(20) | YES | ID of the thread where the current active session resides
Note
|
| STMT_TYPE | bigint(20) | YES | SQL type of the current active session
Note
|
| TX_ID | bigint(20) | YES | ID of the current transaction
Note
|
| BLOCKING_SESSION_ID | bigint(20) | YES | ID of the session that blocks the current session. This field takes effect 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. If 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 a sequence at the sampling moment |
| IN_COMMITTING | varchar(1) | NO | Whether the current sampling point is in the transaction commit phase |
| IN_STORAGE_READ | varchar(1) | NO | Whether the current sampling point is in the storage read phase |
| IN_STORAGE_WRITE | varchar(1) | NO | Whether the current sampling point is in the storage write phase |
| IN_REMOTE_DAS_EXECUTION | varchar(1) | NO | Whether the current sampling point is in the DAS remote execution phase |
| IN_FILTER_ROWS | varchar(1) | NO | Whether the current sampling point is in the storage pushdown execution phase
Note
|
| PROGRAM | varchar(64) | YES | Name of the program being executed at the sampling moment:
Note
|
| MODULE | varchar(64) | YES | MODULE value recorded in the current session at the sampling moment, which is set by the DBMS_APPLICATION_INFO.SET_MODULE package |
| ACTION | varchar(64) | YES | ACTION value recorded in the current session at the sampling moment, which is set by the DBMS_APPLICATION_INFO.SET_ACTION package |
| CLIENT_ID | varchar(64) | YES | CLIENT_ID value recorded in the current session at the sampling moment, which is set by the DBMS_SESSION.set_identifier package |
| BACKTRACE | varchar(512) | YES | Auxiliary debugging field that records the call stack at the time of the event occurrence |
| PLAN_ID | bigint(20) | YES | ID of the plan of the sampled SQL statement in the plan cache, which is used to join the sampling point with the plan |
| TM_DELTA_TIME | bigint(20) | YES | Time interval for calculating the time model, in microseconds
Note
|
| TM_DELTA_CPU_TIME | bigint(20) | YES | CPU time spent in the past TM_DELTA_TIME period, in microseconds
Note
|
| TM_DELTA_DB_TIME | bigint(20) | YES | Database call time spent in the past TM_DELTA_TIME period, in microseconds
Note
|
| TOP_LEVEL_SQL_ID | varchar(32) | YES | ID of the top-level SQL
Note
|
| IN_PLSQL_COMPILATION | varchar(1) | NO | PL compilation status: Y/N
Note
|
| IN_PLSQL_EXECUTION | varchar(1) | NO | PL execution status: Y/N
Note
|
| PLSQL_ENTRY_OBJECT_ID | bigint(20) | YES | ID of the top-level PL
Note
|
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | YES | Subprogram ID of the top-level PL
Note
|
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | YES | Subprogram name of the top-level PL
Note
|
| PLSQL_OBJECT_ID | bigint(20) | YES | ID of the PL being executed
Note
|
| PLSQL_SUBPROGRAM_ID | bigint(20) | YES | ID of the subprogram being executed
Note
|
| PLSQL_SUBPROGRAM_NAME | varchar(32) | YES | Name of the subprogram being executed
Note
|
| DELTA_READ_IO_REQUESTS | bigint(20) | YES | Number of read I/O requests between two sampling points
Note
|
| DELTA_READ_IO_BYTES | bigint(20) | YES | Total size of files read between two sampling points
Note
|
| DELTA_WRITE_IO_REQUESTS | bigint(20) | YES | Number of write I/O requests between two sampling points
Note
|
| DELTA_WRITE_IO_BYTES | bigint(20) | YES | Total size of files written between two sampling points
Note
|
| TABLET_ID | bigint(20) | YES | ID of the tablet being processed by the current SQL
Note
|
| PROXY_SID | bigint(20) | YES | ID of the proxy session
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: 1
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SAMPLE_ID: 10
SESSION_ID: 468151435264
SAMPLE_TIME: 2024-12-09 11:01:34.824039
USER_ID: 200001
SESSION_TYPE: 1
SESSION_STATE: WAITING
SQL_ID: 17605A1DA6B6A2150E9FBCA5D4C7653A
TRACE_ID:
EVENT_NO: 99
EVENT_ID: 32003
TIME_WAITED: 0
P1: NULL
P2: 1
P3: 0
SQL_PLAN_LINE_ID: 0
GROUP_ID: 0
PLAN_HASH: 12254811659671915614
THREAD_ID: 1926
STMT_TYPE: NULL
TX_ID: NULL
BLOCKING_SESSION_ID: 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_PLSQL_COMPILATION: N
IN_PLSQL_EXECUTION: N
IN_FILTER_ROWS: N
IN_RPC_ENCODE: N
IN_RPC_DECODE: N
IN_CONNECTION_MGR: N
PROGRAM: RSAsyncTask
MODULE: NULL
ACTION: NULL
CLIENT_ID: NULL
BACKTRACE: NULL
PLAN_ID: 465
TM_DELTA_TIME: 45767216
TM_DELTA_CPU_TIME: 43553658
TM_DELTA_DB_TIME: 43620453
TOP_LEVEL_SQL_ID: NULL
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
DELTA_READ_IO_REQUESTS: 0
DELTA_READ_IO_BYTES: 0
DELTA_WRITE_IO_REQUESTS: 0
DELTA_WRITE_IO_BYTES: 0
TABLET_ID: 1
PROXY_SID: 468151435264
1 row in set (0.033 sec)