Note
This view was introduced in OceanBase Database V4.2.1.
Purpose
The DBA_WR_ACTIVE_SESSION_HISTORY view displays the ASH data persisted in the current tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| CLUSTER_ID | NUMBER(38) | NO | Cluster ID |
| TENANT_ID | NUMBER(38) | NO | Tenant ID |
| SNAP_ID | NUMBER(38) | NO | Snapshot ID |
| SVR_IP | VARCHAR2(46) | NO | Node IP |
| SVR_PORT | NUMBER(38) | NO | Node port |
| SAMPLE_ID | NUMBER(38) | NO | Sample ID |
| SESSION_ID | bigint(20) | NO | ID of the sampled session. For V4.3.x:
|
| SAMPLE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | Sampling time |
| USER_ID | NUMBER(38) | YES | User ID of the sampled session |
| SESSION_TYPE | NUMBER(38) | YES | Session type
|
| SESSION_STATE | VARCHAR2(7) | NO | Status of the session at the sampling moment
|
| SQL_ID | VARCHAR2(32) | YES | SQL ID |
| TRACE_ID | VARCHAR2(64) | YES | TRACE_ID |
| EVENT_NO | NUMBER(38) | YES | Internal event ID, which is used for queries across tables |
| EVENT_ID | NUMBER | YES | ID of the current waiting event
Note
|
| TIME_WAITED | NUMBER(38) | YES | Total wait time of the waiting event in microseconds (us) |
| P1 | NUMBER(38) | YES | Value of parameter 1 of the waiting event |
| P2 | NUMBER(38) | YES | Value of parameter 2 of the waiting event |
| P3 | NUMBER(38) | YES | Value of parameter 3 of the waiting event |
| SQL_PLAN_LINE_ID | NUMBER(38) | YES | ID of the corresponding SQL operator when the sample is taken. If there is no corresponding operator, the value is NULL |
| PLAN_HASH | NUMBER(38) | YES | Plan hash value of the current executing SQL statement
Note
|
| THREAD_ID | NUMBER(38) | YES | ID of the thread where the current active session resides
Note
|
| STMT_TYPE | NUMBER(38) | YES | SQL type of the current active session
Note
|
| GROUP_ID | NUMBER(38) | YES | ID of the resource group to which the session belongs
Note
|
| TX_ID | NUMBER(38) | YES | ID of the current transaction
Note
|
| BLOCKING_SESSION_ID | NUMBER(38) | YES | ID of the session that blocks the current session, which is displayed only in the case of lock conflict. The ID of the session that holds the lock is displayed
Note
|
| TIME_MODEL | NUMBER(38) | YES | Information about the time model |
| IN_PARSE | VARCHAR2(1) | NO | Whether the current session is parsing an SQL statement during sampling |
| IN_PL_PARSE | VARCHAR2(1) | NO | Whether the current session is parsing an SQL PL statement during sampling |
| IN_PLAN_CACHE | VARCHAR2(1) | NO | Whether the current session is using the plan cache during sampling |
| IN_SQL_OPTIMIZE | VARCHAR2(1) | NO | Whether the current session is optimizing an SQL statement during sampling |
| IN_SQL_EXECUTION | VARCHAR2(1) | NO | Whether the current session is executing an SQL statement during sampling |
| IN_PX_EXECUTION | VARCHAR2(1) | NO | Whether the current session is executing an SQL statement in parallel during sampling. If the session is in this state, it is also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | VARCHAR2(1) | NO | Whether the current session is fetching values from an auto-increment column or a sequence during sampling |
| IN_COMMITTING | VARCHAR2(1) | NO | Whether the current sampling point is in the transaction commit phase |
| IN_STORAGE_READ | VARCHAR2(1) | NO | Whether the current sampling point is in the storage read phase |
| IN_STORAGE_WRITE | VARCHAR2(1) | NO | Whether the current sampling point is in the storage write phase |
| IN_REMOTE_DAS_EXECUTION | VARCHAR2(1) | NO | Whether the current sampling point is in the remote DAS execution phase |
| IN_FILTER_ROWS | VARCHAR2(1) | NO | Whether the current sampling point is in the storage pushdown execution phase
Note
|
| PROGRAM | VARCHAR2(64) | YES | Name of the program that is being executed at the current sampling point:
Note
|
| MODULE | VARCHAR2(64) | YES | MODULE value recorded in the current session at the sampling moment, which is set by the DBMS_APPLICATION_INFO.SET_MODULE package |
| ACTION | VARCHAR2(64) | YES | ACTION value recorded in the current session at the sampling moment, which is set by the DBMS_APPLICATION_INFO.SET_ACTION package |
| CLIENT_ID | VARCHAR2(64) | YES | CLIENT_ID value recorded in the current session at the sampling moment, which is set by the DBMS_SESSION.set_identifier package |
| BACKTRACE | VARCHAR2(512) | YES | Auxiliary debugging field that records the call stack of the code at the time of the event occurrence |
| PLAN_ID | NUMBER(38) | YES | 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 | NUMBER | YES | Time interval for calculating the time model, in microseconds
Note
|
| TM_DELTA_CPU_TIME | NUMBER | YES | CPU time spent in the past TM_DELTA_TIME period, in microseconds
Note
|
| TM_DELTA_DB_TIME | NUMBER | YES | Database call time spent in the past TM_DELTA_TIME period, in microseconds
Note
|
| TOP_LEVEL_SQL_ID | CHAR(32) | YES | ID of the top-level SQL
Note
|
| IN_PLSQL_COMPILATION | VARCHAR2(1) | NO | PL compilation status: Y/N
Note
|
| IN_PLSQL_EXECUTION | VARCHAR2(1) | NO | PL execution status: Y/N
Note
|
| PLSQL_ENTRY_OBJECT_ID | NUMBER | YES | OBJECT ID of the top-level PL
Note
|
| PLSQL_ENTRY_SUBPROGRAM_ID | NUMBER | YES | Subprogram ID of the top-level PL
Note
|
| PLSQL_ENTRY_SUBPROGRAM_NAME | VARCHAR2(32) | YES | Subprogram name of the top-level PL
Note
|
| PLSQL_OBJECT_ID | NUMBER | YES | ID of the PL being executed
Note
|
| PLSQL_SUBPROGRAM_ID | NUMBER | YES | ID of the subprogram being executed
Note
|
| PLSQL_SUBPROGRAM_NAME | VARCHAR2(32) | YES | Name of the subprogram being executed
Note
|
| DELTA_READ_IO_REQUESTS | NUMBER(38) | YES | Number of read I/O requests between two sampling points
NoteFor V4.3.x, this field was introduced in V4.3.5 BP2. |
| DELTA_READ_IO_BYTES | NUMBER(38) | YES | Cumulative size of files read between two sampling points
NoteFor V4.3.x, this field was introduced in V4.3.5 BP2. |
| DELTA_WRITE_IO_REQUESTS | NUMBER(38) | YES | Number of write I/O requests between two sampling points
NoteFor V4.3.x, this field was introduced in V4.3.5 BP2. |
| DELTA_WRITE_IO_BYTES | NUMBER(38) | YES | Cumulative size of files written between two sampling points
NoteFor V4.3.x, this field was introduced in V4.3.5 BP2. |
| TABLET_ID | NUMBER(38) | YES | ID of the tablet that is being processed by the current SQL
Note
|
| PROXY_SID | NUMBER(38) | YES | ID of the proxy session
Note
|
Sample query
Query the ASH data that is persisted in this tenant.
obclient [SYS]> SELECT * FROM SYS.DBA_WR_ACTIVE_SESSION_HISTORY WHERE ROWNUM <= 1\G
The query result is as follows:
*************************** 1. row ***************************
CLUSTER_ID: 10001
TENANT_ID: 1004
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SAMPLE_ID: 2050
SESSION_ID: 29562259898368
SAMPLE_TIME: 09-DEC-24 11.05.44.038392 AM
USER_ID: 200001
SESSION_TYPE: 1
SESSION_STATE: WAITING
SQL_ID: NULL
TRACE_ID: NULL
EVENT_NO: 89
EVENT_ID: 30000
TIME_WAITED: 0
P1: 0
P2: 3221528469
P3: 0
SQL_PLAN_LINE_ID: NULL
PLAN_HASH: NULL
THREAD_ID: 2858
STMT_TYPE: NULL
GROUP_ID: 0
TX_ID: NULL
BLOCKING_SESSION_ID: 3221528469
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_PLSQL_COMPILATION: N
IN_PLSQL_EXECUTION: N
IN_FILTER_ROWS: N
IN_RPC_ENCODE: N
IN_RPC_DECODE: N
IN_CONNECTION_MGR: N
PROGRAM: T1004_ArcSrv
MODULE: NULL
ACTION: NULL
CLIENT_ID: NULL
BACKTRACE: NULL
PLAN_ID: 0
TM_DELTA_TIME: 919201
TM_DELTA_CPU_TIME: 1303
TM_DELTA_DB_TIME: 919201
TOP_LEVEL_SQL_ID: NULL
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
DELTA_READ_IO_REQUESTS: 0
DELTA_READ_IO_BYTES: 0
DELTA_WRITE_IO_REQUESTS: 0
DELTA_WRITE_IO_BYTES: 0
TABLET_ID: NULL
PROXY_SID: 29562259898368
1 row in set (0.117 sec)