Note
This view is available starting with V4.2.1.
Overview
The CDB_WR_ACTIVE_SESSION_HISTORY view displays the persisted ASH data for all tenants.
Columns
Column |
Type |
Is NULL |
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 | Sample ID |
| SESSION_ID | bigint(20) | NO | The ID of the sampled session. For V4.3.x versions:
|
| SAMPLE_TIME | timestamp(6) | NO | Sampling Time |
| USER_ID | bigint(20) | YES | User ID of the sampled session |
| SESSION_TYPE | tinyint(4) | YES | SESSION type.
|
| SESSION_STATE | varchar(7) | NO | Status of the session at the sampling point
|
| SQL_ID | varchar(32) | YES | SQL ID |
| TRACE_ID | varchar(64) | YES | TRACE_ID |
| EVENT_NO | bigint(20) | YES | Internal event ID, used to associate queries with other tables |
| EVENT_ID | bigint(20) | YES | The ID of the event currently being waited for.
Note
|
| TIME_WAITED | bigint(20) | YES | Total wait time for this event, in microseconds (μs). |
| P1 | bigint(20) | YES | Value of Parameter 1 for the Wait Event |
| P2 | bigint(20) | YES | Value of Parameter 2 for the Wait Event |
| P3 | bigint(20) | YES | Value of Parameter 3 for the Wait Event |
| SQL_PLAN_LINE_ID | bigint(20) | YES | The ID of the SQL operator corresponding to the sampling. The value is NULL if no operator is found. |
| GROUP_ID | bigint(20) | YES | Resource Group ID
Note
|
| PLAN_HASH | bigint(20) unsigned | YES | The current execution SQL command corresponding Plan Hash
Note
|
| THREAD_ID | bigint(20) | YES | Thread ID of the current active session
Note
|
| STMT_TYPE | bigint(20) | YES | SQL Type of the Current Active Session
Note
|
| TX_ID | bigint(20) | YES | Current Transaction ID
Note
|
| BLOCKING_SESSION_ID | bigint(20) | YES | If the current session is blocked, the session ID that blocks it is displayed. This field takes effect only in lock conflict scenarios, showing the session ID holding the lock.
Note
|
| TIME_MODEL | bigint(20) unsigned | YES | Information about TIME MODELS |
| IN_PARSE | varchar(1) | NO | Whether the current session is performing SQL parse during sampling |
| IN_PL_PARSE | varchar(1) | NO | Whether the current session is performing SQL PL parse during sampling. |
| IN_PLAN_CACHE | varchar(1) | NO | Whether the current session is performing plan cache during sampling |
| IN_SQL_OPTIMIZE | varchar(1) | NO | Whether the current session is performing SQL optimization during sampling |
| IN_SQL_EXECUTION | varchar(1) | NO | Whether the current session is performing SQL execution during sampling |
| IN_PX_EXECUTION | varchar(1) | NO | Whether the current session is performing SQL parallel execution during sampling. When a session is in this state, it must also be in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | varchar(1) | NO | Whether the current session is fetching values from an auto-increment column or a sequence during sampling. |
| 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 store pushdown execution phase
Note
|
| IN_RPC_ENCODE | varchar(1) | NO | The serialization operation of the current SQL statement is in progress.
Note
|
| IN_RPC_DECODE | varchar(1) | NO | The current SQL is performing deserialization operations
Note
|
| IN_CONNECTION_MGR | varchar(1) | NO | The current SQL statement is performing a link creation operation
Note
|
| PROGRAM | varchar(64) | YES | Program name being executed at the current sampling point:
Note
|
| MODULE | varchar(64) | YES | The MODULE value recorded in the current session at the sampling moment, obtained throughDBMS_APPLICATION_INFO.SET_MODULEPackage settings |
| ACTION | varchar(64) | YES | The sampled ACTION value recorded on the current session, obtained throughDBMS_APPLICATION_INFO.SET_ACTIONPackage Settings |
| CLIENT_ID | varchar(64) | YES | The CLIENT_ID value recorded in the current session at the sampling moment. It is obtained through theDBMS_SESSION.set_identifierPackage Settings |
| BACKTRACE | varchar(512) | YES | Auxiliary debugging field, used to record the code call stack at the time the event occurred. |
| PLAN_ID | bigint(20) | YES | The plan ID of the sampled SQL statement in the PLAN CACHE, used to associate the sampling point with the plan. |
| TM_DELTA_TIME | bigint(20) | YES | Calculates the time interval of the time model, in microseconds
Note
|
| TM_DELTA_CPU_TIME | bigint(20) | YES | PastTM_DELTA_TIMETime Spent on CPU During the Period
Note
|
| TM_DELTA_DB_TIME | bigint(20) | YES | PastTM_DELTA_TIMEThe amount of time spent on database calls during the period
Note
|
| TOP_LEVEL_SQL_ID | varchar(32) | YES | Top-level SQL ID
Note
|
| IN_PLSQL_COMPILATION | varchar(1) | NO | Current PL Compilation Status: Y/N
Note
|
| IN_PLSQL_EXECUTION | varchar(1) | NO | Current PL Execution Status: Y/N
Note
|
| PLSQL_ENTRY_OBJECT_ID | bigint(20) | YES | OBJECT ID
Note
|
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | YES | Sub project ID
Note
|
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | YES | Top-level PL Sub project name
Note
|
| PLSQL_OBJECT_ID | bigint(20) | YES | The ID of the PL object currently being executed
Note
|
| PLSQL_SUBPROGRAM_ID | bigint(20) | YES | The ID of the PL subprogram currently being executed
Note
|
| PLSQL_SUBPROGRAM_NAME | varchar(32) | YES | The name of the currently executing PL subprogram. name
Note
|
| DELTA_READ_IO_REQUESTS | bigint(20) | YES | Number of Reads Between Two Samples
Note
|
| DELTA_READ_IO_BYTES | bigint(20) | YES | Cumulative File Size Read Between Two Samples
Note
|
| DELTA_WRITE_IO_REQUESTS | bigint(20) | YES | Number of Writes Between Two Samples
Note
|
| DELTA_WRITE_IO_BYTES | bigint(20) | YES | Cumulative File Size Written Between Two Samples
Note
|
| TABLET_ID | bigint(20) | YES | The ID of the tablet being processed by the current SQL statement
Note
|
| PROXY_SID | bigint(20) | YES | Proxy Session ID
Note
|
| TX_ID | bigint(20) | YES | Current Transaction ID
Note
|
Sample query
Query the persisted ASH data of tenant mysql001.
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY WHERE tenant_id IN (SELECT tenant_id FROM oceanbase.DBA_OB_TENANTS WHERE tenant_name='mysql001') LIMIT 1\G
The query 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: YB42AC1E87F8-00062F29EA381666-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_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_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.200 sec)