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 V$OB_ACTIVE_SESSION_HISTORY view displays the active session history of the current OBServer node in the tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| SVR_IP | VARCHAR2(46) | NO | IP address of the server where the sample is located. |
| SVR_PORT | NUMBER | NO | Port number of the server where the sample is located. |
| SAMPLE_ID | NUMBER | NO | Unique ID of the sample on the local server. |
| SAMPLE_TIME | TIMESTAMP(6) | NO | Sampling time. |
| CON_ID | NUMBER | NO | Tenant ID. |
| USER_ID | NUMBER | NO | User ID of the sampled session. |
| SESSION_ID | bigint(20) | NO | ID of the sampled session. For V4.3.x:
|
| SESSION_TYPE | VARCHAR2(10) | NO | Session type. Valid values include:
|
| SESSION_STATE | VARCHAR2(7) | NO | Session status. Valid values include:
|
| SQL_ID | VARCHAR2(32) | NO | SQL ID. |
| PLAN_ID | NUMBER | NO | Plan ID of the sampled SQL statement in the plan cache, which is used to associate sampling points with plans. |
| TRACE_ID | VARCHAR2(64) | NO | Trace ID of the operator. |
| EVENT | VARCHAR2(64) | NO | Description of the waiting event. |
| EVENT_NO | NUMBER | NO | Internal ID of the waiting event, which is used for queries across tables. |
| EVENT_ID | NUMBER | NO | ID of the current waiting event. |
| P1TEXT | VARCHAR2(64) | NO | Name of parameter 1 of the waiting event. The value of this parameter varies based on the event. |
| P1 | NUMBER | NO | Value of parameter 1 of the waiting event. |
| P2TEXT | VARCHAR2(64) | NO | Name of parameter 2 of the waiting event. The value of this parameter varies based on the event. |
| P2 | NUMBER | NO | Value of parameter 2 of the waiting event. |
| P3TEXT | VARCHAR2(64) | NO | Name of parameter 3 of the waiting event. The value of this parameter varies based on the event. |
| P3 | NUMBER | NO | Value of parameter 3 of the waiting event. |
| WAIT_CLASS | VARCHAR2(64) | NO | Type of the waiting event. |
| WAIT_CLASS_ID | NUMBER | NO | ID of the type of the waiting event, which is used for queries across tables. |
| TIME_WAITED | NUMBER | NO | Total waiting time of the waiting event in microseconds (us). |
| SQL_PLAN_LINE_ID | NUMBER | NO | Number of the SQL operator in the SQL plan when the session is sampled. |
| GROUP_ID | NUMBER | NO | ID of the resource group to which the sample belongs. |
| PLAN_HASH | NUMBER | YES | Plan hash value of the current SQL statement. |
| THREAD_ID | NUMBER | YES | ID of the thread where the current active session is located. |
| STMT_TYPE | NUMBER | YES | SQL type of the current active session. |
| TIME_MODEL | NUMBER | NO | time model information, which is a collection of data from all IN_XXX fields (such as IN_PARSE and IN_PL_PARSE). |
| IN_PARSE | VARCHAR2(1) | NO | Indicates whether the session is parsing an SQL statement when it is sampled. |
| IN_PL_PARSE | VARCHAR2(1) | NO | Indicates whether the session is parsing an SQL PL statement when it is sampled. |
| IN_PLAN_CACHE | VARCHAR2(1) | NO | Indicates whether the session is matching a plan in the plan cache when it is sampled. |
| IN_SQL_OPTIMIZE | VARCHAR2(1) | NO | Indicates whether the session is optimizing an SQL statement when it is sampled. |
| IN_SQL_EXECUTION | VARCHAR2(1) | NO | Indicates whether the session is executing an SQL statement when it is sampled. |
| IN_PX_EXECUTION | VARCHAR2(1) | NO | Indicates whether the session is executing an SQL statement in parallel when it is sampled. When the session is in this state, it is also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | VARCHAR2(1) | NO | Indicates whether the session is fetching values from a sequence or an auto-increment column when it is sampled. |
| IN_COMMITTING | VARCHAR2(1) | NO | Indicates whether the sampling point is in the transaction commit phase. |
| IN_STORAGE_READ | VARCHAR2(1) | NO | Indicates whether the sampling point is in the storage read phase. |
| IN_STORAGE_WRITE | VARCHAR2(1) | NO | Indicates whether the sampling point is in the storage write phase. |
| IN_REMOTE_DAS_EXECUTION | VARCHAR2(1) | NO | Indicates whether the sampling point is in the remote DAS execution phase. |
| IN_FILTER_ROWS | VARCHAR2(1) | NO | Indicates whether the sampling point is in the storage downpress execution phase. |
| IN_RPC_ENCODE | VARCHAR2(1) | NO | The serialization operation being performed by the current SQL. |
| IN_RPC_DECODE | VARCHAR2(1) | NO | The deserialization operation being performed by the current SQL. |
| IN_CONNECTION_MGR | VARCHAR2(1) | NO | The connection establishment operation being performed by the current SQL. |
| PROGRAM | VARCHAR2(64) | NO | Name of the program being executed at the sampling point:
|
| MODULE | VARCHAR2(64) | NO | MODULE value recorded when the session is sampled, which is set by the DBMS_APPLICATION_INFO.SET_MODULE package. |
| ACTION | VARCHAR2(64) | NO | ACTION value recorded when the session is sampled, which is set by the DBMS_APPLICATION_INFO.SET_ACTION package. |
| CLIENT_ID | VARCHAR2(64) | NO | CLIENT_ID value recorded when the session is sampled, which is set by the DBMS_SESSION.set_identifier package. |
| BACKTRACE | VARCHAR2(512) | NO | Auxiliary debugging field that records the code call stack when the event occurs. The value of this field is always NULL. |
| TM_DELTA_TIME | NUMBER | NO | Time interval for calculating time model, in microseconds. |
| TM_DELTA_CPU_TIME | NUMBER | NO | CPU time spent in the past TM_DELTA_TIME period. |
| TM_DELTA_DB_TIME | NUMBER | NO | Database call time spent in the past TM_DELTA_TIME period. |
| TOP_LEVEL_SQL_ID | CHAR(32) | NO | Top-level SQL ID. |
| IN_PLSQL_COMPILATION | VARCHAR2(1) | NO | PL compilation status: Y/N |
| IN_PLSQL_EXECUTION | VARCHAR2(1) | NO | PL execution status: Y/N |
| PLSQL_ENTRY_OBJECT_ID | NUMBER | NO | OBJECT ID of the top-level PL. |
| PLSQL_ENTRY_SUBPROGRAM_ID | NUMBER | NO | Subprogram ID of the top-level PL. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | VARCHAR2(32) | NO | Subprogram name of the top-level PL. |
| PLSQL_OBJECT_ID | NUMBER | NO | ID of the PL being executed. |
| PLSQL_SUBPROGRAM_ID | NUMBER | NO | ID of the subprogram being executed. |
| PLSQL_SUBPROGRAM_NAME | VARCHAR2(32) | NO | Name of the subprogram being executed. |
| TX_ID | NUMBER | NO | ID of the current transaction. |
| BLOCKING_SESSION_ID | NUMBER | 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. |
| TABLET_ID | NUMBER | NO | ID of the tablet being processed by the current SQL statement. |
| PROXY_SID | NUMBER | NO | ID of the proxy session. |
| TX_ID | NUMBER | NO | ID of the current transaction. |
| DELTA_READ_IO_REQUESTS | NUMBER | NO | The number of reads between two samples.
Note
|
| DELTA_READ_IO_BYTES | NUMBER | NO | The cumulative size of files read between two samples.
Note
|
| DELTA_WRITE_IO_REQUESTS | NUMBER | NO | The number of writes between two samples.
Note
|
| DELTA_WRITE_IO_BYTES | NUMBER | NO | The cumulative size of files written between two samples.
Note
|
Sample query
View the history of active sessions on the current OBServer node in the tenant.
obclient [SYS]> SELECT * FROM SYS.V$OB_ACTIVE_SESSION_HISTORY WHERE ROWNUM = 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SAMPLE_ID: 2255456
SAMPLE_TIME: 11-DEC-24 11.33.48.880769 AM
CON_ID: 1004
USER_ID: 0
SESSION_ID: 29476360552448
SESSION_TYPE: BACKGROUND
SESSION_STATE: WAITING
SQL_ID: NULL
PLAN_ID: 0
TRACE_ID: NULL
EVENT: thread idling condition wait
EVENT_NO: 39
EVENT_ID: 15112
P1TEXT: address
P1: 140454303586184
P2TEXT: NULL
P2: 0
P3TEXT: NULL
P3: 0
WAIT_CLASS: IDLE
WAIT_CLASS_ID: 106
TIME_WAITED: 0
SQL_PLAN_LINE_ID: NULL
GROUP_ID: 0
PLAN_HASH: NULL
THREAD_ID: 2838
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: T1004_TFSwap
MODULE: NULL
ACTION: NULL
CLIENT_ID: NULL
BACKTRACE: NULL
TM_DELTA_TIME: 0
TM_DELTA_CPU_TIME: 0
TM_DELTA_DB_TIME: 0
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: 29476360552448
1 row in set (0.121 sec)