Note
- For V4.3.x, this view was introduced in V4.3.5.
- For V4.2.x, this view was introduced in V4.2.2.
Purpose
The GV$OB_ACTIVE_SESSION_HISTORY view displays the active session history of all OBServer nodes across all tenants.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | The IP address of the server to which the sample belongs. |
| SVR_PORT | bigint(20) | NO | The port number of the server to which the sample belongs. |
| SAMPLE_ID | bigint(20) | NO | The unique identifier of the sample on the local server. |
| SAMPLE_TIME | timestamp(6) | NO | The sampling time. |
| CON_ID | bigint(20) | NO | The tenant ID. |
| USER_ID | bigint(20) | NO | The user ID of the sampled session. |
| SESSION_ID | bigint(20) | NO | The ID of the sampled session. For V4.3.x:
|
| SESSION_TYPE | varchar(10) | NO | Session type.
|
| SESSION_STATE | varchar(7) | NO | Session state.
|
| SQL_ID | varchar(32) | NO | The SQL ID. |
| PLAN_ID | bigint(20) | NO | The plan ID of the sampled SQL statement in the plan cache. This field helps associate the sampling point with the plan. |
| TRACE_ID | varchar(64) | NO | The trace ID of the operator. |
| EVENT | varchar(64) | NO | The description of the waiting event. |
| EVENT_NO | bigint(20) | NO | The internal number of the waiting event. This number can be used for queries across tables. |
| EVENT_ID | bigint(20) | YES | The ID of the current waiting event. |
| P1TEXT | varchar(64) | NO | The name of the parameter 1 of the waiting event. The name of this parameter varies with the value of the EVENT field. |
| P1 | bigint(20) | NO | The value of parameter 1 of the waiting event. |
| P2TEXT | varchar(64) | NO | The name of the parameter 2 of the waiting event. The name of this parameter varies with the value of the EVENT field. |
| P2 | bigint(20) | NO | The value of parameter 2 of the waiting event. |
| P3TEXT | varchar(64) | NO | The name of the parameter 3 of the waiting event. The name of this parameter varies with the value of the EVENT field. |
| P3 | bigint(20) | NO | The value of parameter 3 of the waiting event. |
| WAIT_CLASS | varchar(64) | NO | The type to which the waiting event belongs. |
| WAIT_CLASS_ID | bigint(20) | NO | The ID of the type to which the waiting event belongs. This number can be used for queries across tables. |
| TIME_WAITED | bigint(20) | NO | The total waiting time of the waiting event, in microseconds (us). |
| SQL_PLAN_LINE_ID | bigint(20) | YES | The number of the SQL operator in the SQL plan when the session was sampled. |
| GROUP_ID | bigint(20) | YES | The ID of the resource group to which the sample belongs. |
| PLAN_HASH | bigint(20) unsigned | YES | The plan hash value of the SQL command being executed. |
| THREAD_ID | bigint(20) | YES | The ID of the thread in which the active session is running. |
| STMT_TYPE | bigint(20) | YES | The SQL type of the active session. |
| TIME_MODEL | bigint(20) | NO | time model information, which is a collection of IN_XXX field values (such as IN_PARSE and IN_PL_PARSE). |
| IN_PARSE | varchar(1) | NO | Indicates whether the session was parsing an SQL statement when it was sampled. |
| IN_PL_PARSE | varchar(1) | NO | Indicates whether the session was parsing an SQL PL statement when it was sampled. |
| IN_PLAN_CACHE | varchar(1) | NO | Indicates whether the session was matching the plan cache when it was sampled. |
| IN_SQL_OPTIMIZE | varchar(1) | NO | Indicates whether the session was optimizing an SQL statement when it was sampled. |
| IN_SQL_EXECUTION | varchar(1) | NO | Indicates whether the session was executing an SQL statement when it was sampled. |
| IN_PX_EXECUTION | varchar(1) | NO | Indicates whether the session was executing an SQL statement in parallel when it was sampled. When the session is in this state, it is also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | varchar(1) | NO | Indicates whether the session was obtaining values from auto-increment columns or sequences when it was sampled. |
| IN_COMMITTING | varchar(1) | NO | Indicates whether the current sampling point is in the transaction committing 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 DAS remote execution phase. |
| IN_FILTER_ROWS | varchar(1) | NO | Indicates whether the current sampling point is in the storage downpress execution phase. |
| IN_RPC_ENCODE | varchar(1) | NO | The serialization operation being performed by the current SQL. |
| IN_RPC_DECODE | varchar(1) | NO | The deserialization operation being performed by the current SQL. |
| IN_CONNECTION_MGR | varchar(1) | NO | The connection establishment operation being performed by the current SQL. |
| PROGRAM | varchar(64) | YES | The name of the program being executed at the current sampling point:
|
| MODULE | varchar(64) | YES | The value of the MODULE field recorded when the session was sampled. This value is set by using the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | varchar(64) | YES | The value of the ACTION field recorded when the session was sampled. This value is set by using the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | varchar(64) | YES | The value of the CLIENT_ID field recorded when the session was sampled. This value is set by using the DBMS_SESSION.set_identifier package. |
| BACKTRACE | varchar(512) | YES | The auxiliary debugging field used to record the code call stack when the event occurred. The value of this field is always NULL. |
| TM_DELTA_TIME | bigint(20) | YES | The time interval for calculating time model, in microseconds. |
| TM_DELTA_CPU_TIME | bigint(20) | YES | The amount of time spent on CPU in the past TM_DELTA_TIME period. |
| TM_DELTA_DB_TIME | bigint(20) | YES | The amount of time spent on database calls in the past TM_DELTA_TIME period. |
| TOP_LEVEL_SQL_ID | varchar(32) | YES | The ID of the top-level SQL statement. |
| IN_PLSQL_COMPILATION | varchar(1) | NO | The PL compilation status: Y/N. |
| IN_PLSQL_EXECUTION | varchar(1) | NO | The PL execution status: Y/N. |
| PLSQL_ENTRY_OBJECT_ID | bigint(20) | YES | The object ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | YES | The subprogram ID of the top-level PL statement. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | YES | The subprogram name of the top-level PL statement. |
| PLSQL_OBJECT_ID | bigint(20) | YES | The object ID of the current PL statement being executed. |
| PLSQL_SUBPROGRAM_ID | bigint(20) | YES | The subprogram ID of the current PL statement being executed. |
| PLSQL_SUBPROGRAM_NAME | varchar(32) | YES | The subprogram name of the current PL statement being executed. |
| TX_ID | bigint(20) | YES | The ID of the current transaction. |
| BLOCKING_SESSION_ID | bigint(20) | YES | If the current session is blocked, this field displays the ID of the session that blocks it. This field only takes effect in lock conflict scenarios, showing the ID of the session holding the lock. |
| TABLET_ID | bigint(20) | YES | The ID of the tablet being processed by the current SQL statement. |
| PROXY_SID | bigint(20) | YES | The ID of the proxy session. |
| TX_ID | bigint(20) | YES | The ID of the current transaction.
Note
|
| DELTA_READ_IO_REQUESTS | bigint(20) | NO | The number of read requests between two samples.
Note
|
| DELTA_READ_IO_BYTES | bigint(20) | NO | The cumulative size of files read between two samples.
Note
|
| DELTA_WRITE_IO_REQUESTS | bigint(20) | NO | The number of write requests between two samples.
Note
|
| DELTA_WRITE_IO_BYTES | bigint(20) | NO | The cumulative size of files written between two samples.
Note
|
Sample query
View the active session history of all OBServer nodes where the tenant resides.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_ACTIVE_SESSION_HISTORY LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SAMPLE_ID: 1922813
SAMPLE_TIME: 2025-03-04 10:53:37.507877
CON_ID: 1001
USER_ID: 0
SESSION_ID: -9223372036854729409
SESSION_TYPE: BACKGROUND
SESSION_STATE: ON CPU
SQL_ID: NULL
PLAN_ID: 0
TRACE_ID: NULL
EVENT:
EVENT_NO: 0
EVENT_ID: NULL
P1TEXT:
P1: 0
P2TEXT:
P2: 0
P3TEXT:
P3: 0
WAIT_CLASS: OTHER
WAIT_CLASS_ID: 100
TIME_WAITED: 0
SQL_PLAN_LINE_ID: NULL
GROUP_ID: 0
PLAN_HASH: NULL
THREAD_ID: 82216
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: T1001_LogService
MODULE: LogRestoreService
ACTION: RemoteLogWriter
CLIENT_ID: NULL
BACKTRACE: NULL
TM_DELTA_TIME: 1017781
TM_DELTA_CPU_TIME: 1017781
TM_DELTA_DB_TIME: 1017781
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
BLOCKING_SESSION_ID: NULL
TABLET_ID: NULL
PROXY_SID: -9223372036854729409
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.014 sec)
References
To view the active session history of all tenants on the current OBServer node, run the V$OB_ACTIVE_SESSION_HISTORY command.
After you obtain the active session history, you can generate an ASH report as needed for analysis. For more information about ASH, see ASH report.