Purpose
The oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY view displays the persisted Active Session History (ASH) data of all tenants.
Note
This view is introduced since OceanBase Database V4.2.1.
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 OBServer node. |
| SVR_PORT | bigint(20) | NO | The port of the OBServer node. |
| SAMPLE_ID | bigint(20) | NO | The sampling ID. |
| SESSION_ID | bigint(20) | NO | The ID of the sampled session. |
| 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 type of the sampled session.
|
| SESSION_STATE | varchar(7) | NO | The status of the session when it is being sampled. Valid values:
|
| SQL_ID | varchar(32) | YES | The SQL ID. |
| TRACE_ID | varchar(64) | YES | The trace ID. |
| EVENT_NO | bigint(20) | YES | The internal number of the wait event, which is used for join query with other tables. |
| TIME_WAITED | bigint(20) | YES | The total wait time of the wait event, in μs. |
| P1 | bigint(20) | YES | The value of the first parameter of the wait event. |
| P2 | bigint(20) | YES | The value of the second parameter of the wait event. |
| P3 | bigint(20) | YES | The value of the third parameter of the wait event. |
| SQL_PLAN_LINE_ID | bigint(20) | YES | The ID of the SQL operator during sampling. If no operator is used, the value is NULL. |
| TIME_MODEL | bigint(20) unsigned | YES | The time model. |
| IN_PARSE | varchar(1) | NO | Indicates whether SQL parsing is being performed in the current session during sampling. |
| IN_PL_PARSE | varchar(1) | NO | Indicates whether SQL PL parsing is being performed in the current session during sampling. |
| IN_PLAN_CACHE | varchar(1) | NO | Indicates whether plan caching is being performed in the current session during sampling. |
| IN_SQL_OPTIMIZE | varchar(1) | NO | Indicates whether SQL tuning is being performed in the current session during sampling. |
| IN_SQL_EXECUTION | varchar(1) | NO | Indicates whether an SQL statement is being executed in the current session during sampling. |
| IN_PX_EXECUTION | varchar(1) | NO | Indicates whether parallel SQL execution is being performed in the current session during sampling. If the current session is in this state, it must be also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | varchar(1) | NO | Indicates whether values are being generated for an auto-increment column or a sequence in the current session during sampling. |
| IN_COMMITTING | varchar(1) | NO | Indicates whether the current sampling point is in the transaction commit phase. |
| IN_STORAGE_READ | varchar(1) | NO | Indicates whether the current sampling point is in the storage read phase. |
| IN_STORAGE_WRITE | varchar(1) | NO | Indicates whether the current sampling point is in the storage write phase. |
| IN_REMOTE_DAS_EXECUTION | varchar(1) | NO | Indicates whether the current sampling point is in the phase of remote execution by using direct access service (DAS). |
| MODULE | varchar(64) | YES | The MODULE value recorded in the current session when it is being sampled. |
| ACTION | varchar(64) | YES | The ACTION value recorded in the current session when it is being sampled. |
| CLIENT_ID | varchar(64) | YES | The CLIENT_ID value recorded in the current session when it is being sampled. |
| BACKTRACE | varchar(512) | YES | An auxiliary debugging field, which records the code call stack when an event occurs. |
| PLAN_ID | bigint(20) | YES | The plan ID of the sampled SQL statement in the plan cache, which is used to associate the sampling point with the plan. |
| TM_DELTA_TIME | bigint(20) | YES | The time interval for calculating time model, in microseconds.
NoteThis column is introduced in V4.2.2. |
| TM_DELTA_CPU_TIME | bigint(20) | YES | The amount of time spent on CPU in the past TM_DELTA_TIME interval.
NoteThis column is introduced in V4.2.2. |
| TM_DELTA_DB_TIME | bigint(20) | YES | The amount of time spent on database calls in the past TM_DELTA_TIME interval.
NoteThis column is introduced in V4.2.2. |
| TOP_LEVEL_SQL_ID | varchar(32) | YES | The top-level SQL ID.
NoteThis column is introduced in V4.2.2. |
| IN_PLSQL_COMPILATION | varchar(1) | NO | The current PL compilation status: Y/N.
NoteThis column is introduced in V4.2.2. |
| IN_PLSQL_EXECUTION | varchar(1) | NO | The current PL execution status: Y/N.
NoteThis column is introduced in V4.2.2. |
| PLSQL_ENTRY_OBJECT_ID | bigint(20) | YES | The object ID of the top-level PL.
NoteThis column is introduced in V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | YES | The subprogram ID of the top-level PL.
NoteThis column is introduced in V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | YES | The subprogram name of the top-level PL.
NoteThis column is introduced in V4.2.2. |
| PLSQL_OBJECT_ID | bigint(20) | YES | The object ID of the PL being executed.
NoteThis column is introduced in V4.2.2. |
| PLSQL_SUBPROGRAM_ID | bigint(20) | YES | The subprogram name of the PL being executed.
NoteThis column is introduced in V4.2.2. |
| PLSQL_SUBPROGRAM_NAME | varchar(32) | YES | The subprogram name of the PL being executed.
NoteThis column is introduced in V4.2.2. |
Sample query
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_WR_ACTIVE_SESSION_HISTORY limit 1;
The return result is as follows:
+------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
| CLUSTER_ID | TENANT_ID | SNAP_ID | SVR_IP | SVR_PORT | SAMPLE_ID | SESSION_ID | SAMPLE_TIME | USER_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | TRACE_ID | EVENT_NO | EVENT_ID | TIME_WAITED | P1 | P2 | P3 | SQL_PLAN_LINE_ID | TIME_MODEL | IN_PARSE | IN_PL_PARSE | IN_PLAN_CACHE | IN_SQL_OPTIMIZE | IN_SQL_EXECUTION | IN_PX_EXECUTION | IN_SEQUENCE_LOAD | IN_COMMITTING | IN_STORAGE_READ | IN_STORAGE_WRITE | IN_REMOTE_DAS_EXECUTION | IN_FILTER_ROWS | PROGRAM | MODULE | ACTION | CLIENT_ID | BACKTRACE | PLAN_ID | TM_DELTA_TIME | TM_DELTA_CPU_TIME | TM_DELTA_DB_TIME | TOP_LEVEL_SQL_ID | IN_PLSQL_COMPILATION | IN_PLSQL_EXECUTION | PLSQL_ENTRY_OBJECT_ID | PLSQL_ENTRY_SUBPROGRAM_ID | PLSQL_ENTRY_SUBPROGRAM_NAME | PLSQL_OBJECT_ID | PLSQL_SUBPROGRAM_ID | PLSQL_SUBPROGRAM_NAME |
+------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
| 40001 | 1 | 1 | xx.xx.xx.xx | 28824 | 90 | 1748051689472 | 2024-01-05 14:32:42.111942 | 0 | 1 | ON CPU | | Y70980BA1CCFB-00060E2D0029C856-0-0 | 0 | -1 | 0 | 0 | 0 | 0 | NULL | 0 | N | N | N | N | N | N | N | N | N | N | N | N | RPC PROCESS (T1_L0_G0)_ 257 | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 0 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-----------+---------+----------------+----------+-----------+---------------+----------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
1 row in set (0.097 sec)