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
|
| WEIGHT | double | YES | The number of times the same action of the same tenant occurs in the corresponding time period of the WR record.
NoteFor V4.4.x, this field was introduced in V4.4.2. |
Sample query
Query the ASH data of the current tenant after persistence and display the first record.
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: 2305843009213697064
SAMPLE_TIME: 2026-01-07 15:06:23.098583
USER_ID: 200001
SESSION_TYPE: 1
SESSION_STATE: WAITING
SQL_ID: NULL
TRACE_ID: NULL
EVENT_NO: 117
EVENT_ID: 20200
TIME_WAITED: 498914
P1: 500000
P2: -4008
P3: 5
SQL_PLAN_LINE_ID: NULL
GROUP_ID: 0
PLAN_HASH: NULL
THREAD_ID: 95492
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: T1_MergeScheduler
MODULE: MergeScheduler
ACTION: NULL
CLIENT_ID: NULL
BACKTRACE: NULL
PLAN_ID: 0
TM_DELTA_TIME: 1856579
TM_DELTA_CPU_TIME: 5768
TM_DELTA_DB_TIME: 1856578
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: NULL
PROXY_SID: 2305843009213697064
WEIGHT: 1
1 row in set (0.021 sec)
References
Query the ASH data of all tenants after persistence: oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY