Note
- For V4.3.x, the view was renamed to V$OB_ACTIVE_SESSION_HISTORY since V4.3.5. For V4.2.x, the view was renamed to V$OB_ACTIVE_SESSION_HISTORY since V4.2.2.
- This view is introduced in V4.0.0.
Purpose
The V$ACTIVE_SESSION_HISTORY view displays the active session history of the current OBServer node in the tenant.
Columns
| Columns | Type | Nullable? | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | IP address of the server to which the sample belongs. |
| SVR_PORT | bigint(20) | NO | Port number of the server to which the sample belongs. |
| SAMPLE_ID | bigint(20) | NO | Unique identifier of the sample on the local server. |
| SAMPLE_TIME | timestamp(6) | NO | Sampling time. |
| CON_ID | bigint(20) | NO | Tenant ID. |
| USER_ID | bigint(20) | NO | User ID of the sampled session. |
| SESSION_ID | bigint(20) | NO | ID of the sampled session. For V4.3.x:
|
| SESSION_TYPE | varchar(10) | NO | Session type. |
| SESSION_STATE | varchar(7) | NO | Session status. EVENT field. |
| SQL_ID | varchar(32) | NO | SQL ID. |
| PLAN_ID | bigint(20) | NO | ID of the plan in the plan cache. This field associates the sampling point with the plan. |
| TRACE_ID | varchar(64) | NO | Trace ID of the operator. |
| EVENT | varchar(64) | NO | Description of the waiting event. |
| EVENT_NO | bigint(20) | NO | Internal number of the waiting event. This field is used for queries across tables. |
| EVENT_ID | bigint(20) | NO | ID of the current waiting event.
Note
|
| P1TEXT | varchar(64) | NO | Name of parameter 1 of the waiting event. The name in this field changes with the value of the EVENT field. |
| P1 | bigint(20) | NO | Value of parameter 1. |
| P2TEXT | varchar(64) | NO | Name of parameter 2 of the waiting event. The name in this field changes with the value of the EVENT field. |
| P2 | bigint(20) | NO | Value of parameter 2. |
| P3TEXT | varchar(64) | NO | Name of parameter 3 of the waiting event. The name in this field changes with the value of the EVENT field. |
| P3 | bigint(20) | NO | Value of parameter 3. |
| WAIT_CLASS | varchar(64) | NO | Type to which the waiting event belongs. |
| WAIT_CLASS_ID | bigint(20) | NO | ID of the type to which the waiting event belongs. This field is used for queries across tables. |
| TIME_WAITED | bigint(20) | NO | Total wait time of the waiting event. Unit: microseconds (us). |
| SQL_PLAN_LINE_ID | bigint(20) | NO | Number of the SQL operator in the SQL plan. |
| GROUP_ID | bigint(20) | NO | ID of the resource group to which the sample belongs. |
| PLAN_HASH | bigint(20) unsigned | NO | Plan hash value of the SQL command being executed. |
| THREAD_ID | bigint(20) | NO | ID of the thread in which the active session is located. |
| STMT_TYPE | bigint(20) | NO | SQL type of the active session. |
| TIME_MODEL | bigint(20) | NO | Time model information. It is a collection of IN_XXX field values (such as IN_PARSE and IN_PL_PARSE). |
| IN_PARSE | varchar(1) | NO | Specifies whether the session is parsing an SQL statement during the sampling. |
| IN_PL_PARSE | varchar(1) | NO | Specifies whether the session is parsing an SQL PL statement during the sampling. |
| IN_PLAN_CACHE | varchar(1) | NO | Specifies whether the session is matching the plan cache during the sampling. |
| IN_SQL_OPTIMIZE | varchar(1) | NO | Specifies whether the session is optimizing SQL statements during the sampling. |
| IN_SQL_EXECUTION | varchar(1) | NO | Specifies whether the session is executing SQL statements during the sampling. |
| IN_PX_EXECUTION | varchar(1) | NO | Specifies whether the session is executing SQL statements in parallel during the sampling. If the session is in this state, it is also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | varchar(1) | NO | Specifies whether the session is obtaining values from auto-increment columns or sequences during the sampling. |
| IN_COMMITTING | varchar(1) | NO | Specifies whether the current sampling point is in the transaction commit phase.
NoteThis field was introduced in V4.2.1. |
| IN_STORAGE_READ | varchar(1) | NO | Specifies whether the current sampling point is in the storage read phase.
NoteThis field was introduced in V4.2.1. |
| IN_STORAGE_WRITE | varchar(1) | NO | Specifies whether the current sampling point is in the storage write phase.
NoteThis field was introduced in V4.2.1. |
| IN_REMOTE_DAS_EXECUTION | varchar(1) | NO | Specifies whether the current sampling point is in the DAS remote execution phase.
NoteThis field was introduced in V4.2.1. |
| MODULE | varchar(64) | NO | MODULE value recorded for the session during the sampling. This field is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | varchar(64) | NO | ACTION value recorded for the session during the sampling. This field is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | varchar(64) | NO | CLIENT_ID value recorded for the session during the sampling. This field is set by using the DBMS_SESSION.set_identifier package. |
| BACKTRACE | varchar(512) | NO | Auxiliary debugging field used to record the code call stack at the time of the event. The value of this field is always NULL. |
| TM_DELTA_TIME | bigint(20) | NO | Time interval for calculating the time model. Unit: microseconds.
Note
|
| TM_DELTA_CPU_TIME | bigint(20) | NO | CPU time spent in the past TM_DELTA_TIME period.
Note
|
| TM_DELTA_DB_TIME | bigint(20) | NO | Time spent in database calls in the past TM_DELTA_TIME period.
Note
|
| TOP_LEVEL_SQL_ID | CHAR(32) | NO | ID of the top-level SQL statement.
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) | NO | OBJECT ID of the top-level PL statement.
Note
|
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | NO | Subprogram ID of the top-level PL statement.
Note
|
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | NO | Subprogram name of the top-level PL statement.
Note
|
| PLSQL_OBJECT_ID | bigint(20) | NO | ID of the PL object being executed.
Note
|
| PLSQL_SUBPROGRAM_ID | bigint(20) | NO | ID of the PL subprogram being executed.
Note
|
| PLSQL_SUBPROGRAM_NAME | varchar(32) | NO | Name of the PL subprogram being executed.
Note
|
| TX_ID | bigint(20) | NO | ID of the current transaction.
Note
|
| BLOCKING_SESSION_ID | bigint(20) | NO | 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
|
| TABLET_ID | bigint(20) | NO | ID of the tablet that the current SQL statement is processing.
Note
|
| PROXY_SID | bigint(20) | NO | ID of the proxy session.
Note
|
| DELTA_READ_IO_REQUESTS | bigint(20) | NO | Number of reads between two samplings.
Note
|
| DELTA_READ_IO_BYTES | bigint(20) | NO | Cumulative size of files read between two samplings.
Note
|
| DELTA_WRITE_IO_REQUESTS | bigint(20) | NO | Number of writes between two samplings.
Note
|
| DELTA_WRITE_IO_BYTES | bigint(20) | NO | Cumulative size of files written between two samplings.
Note
|
Sample query
Example 1: Query the active session history of the current OBServer node in the tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.V$ACTIVE_SESSION_HISTORY limit 1\G
Example 2: Query the active session history of the current OBServer node in the tenant.
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SAMPLE_ID: 1163552
SAMPLE_TIME: 2025-04-28 17:22:18.567996
CON_ID: 1002
USER_ID: 0
SESSION_ID: 2305843009213709012
SESSION_TYPE: BACKGROUND
SESSION_STATE: WAITING
SQL_ID: NULL
PLAN_ID: 0
TRACE_ID: NULL
EVENT: latch: config lock wait
EVENT_NO: 132
EVENT_ID: 40
P1TEXT: address
P1: 140013133706752
P2TEXT: number
P2: 1073791885
P3TEXT: tries
P3: 0
WAIT_CLASS: CONCURRENCY
WAIT_CLASS_ID: 104
TIME_WAITED: 10408
SQL_PLAN_LINE_ID: NULL
GROUP_ID: 0
PLAN_HASH: NULL
THREAD_ID: 50828
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_ServerProbeSrv
MODULE: ServerProbeSrv
ACTION: NULL
CLIENT_ID: NULL
BACKTRACE: NULL
TM_DELTA_TIME: 1016561
TM_DELTA_CPU_TIME: 2105
TM_DELTA_DB_TIME: 525347
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
TX_ID: NULL
BLOCKING_SESSION_ID: NULL
TABLET_ID: NULL
PROXY_SID: 2305843009213709012
DELTA_READ_IO_REQUESTS: 0
DELTA_READ_IO_BYTES: 0
DELTA_WRITE_IO_REQUESTS: 0
DELTA_WRITE_IO_BYTES: 0
References
After you obtain the ASH, you can generate an ASH report for analysis as needed. For more information about ASH, see ASH report.