Note
This view is introduced since OceanBase Database V4.2.1.
Purpose
The DBA_WR_ACTIVE_SESSION_HISTORY displays the persisted Active Session History (ASH) data of the current tenant.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| CLUSTER_ID | NUMBER(38) | No | The ID of the cluster. |
| TENANT_ID | NUMBER(38) | No | The ID of the tenant. |
| SNAP_ID | NUMBER(38) | No | The ID of the snapshot. |
| SVR_IP | VARCHAR2(46) | No | The IP address of the server. |
| SVR_PORT | NUMBER(38) | No | The port of the server. |
| SAMPLE_ID | NUMBER(38) | No | The sampling ID. |
| SESSION_ID | NUMBER(38) | No | The ID of the sampled session. |
| SAMPLE_TIME | TIMESTAMP(6) WITH LOCAL TIME ZONE | No | The sampling time. |
| USER_ID | NUMBER(38) | Yes | The user ID of the sampled session. |
| SESSION_TYPE | NUMBER(38) | Yes | The type of the session. Valid values:
|
| SESSION_STATE | VARCHAR2(7) | No | The status of the session during sampling. Valid values:
|
| SQL_ID | VARCHAR2(32) | Yes | The ID of the SQL statement. |
| TRACE_ID | VARCHAR2(64) | Yes | TRACE_ID |
| EVENT_NO | NUMBER(38) | Yes | The internal number of the wait event, which is used for JOIN queries. |
| TIME_WAITED | NUMBER(38) | Yes | The total wait time of the wait event, in μs. |
| P1 | NUMBER(38) | Yes | The value of the first parameter of the wait event. |
| P2 | NUMBER(38) | Yes | The value of the second parameter of the wait event. |
| P3 | NUMBER(38) | Yes | The value of the third parameter of the wait event. |
| SQL_PLAN_LINE_ID | NUMBER(38) | Yes | The ID of the SQL operator during sampling. If no operator is used, the value is NULL. |
| TIME_MODEL | NUMBER(38) | Yes | The time model. |
| IN_PARSE | VARCHAR2(1) | No | Indicates whether the current session is performing SQL parsing during sampling. |
| IN_PL_PARSE | VARCHAR2(1) | No | Indicates whether the current session is performing SQL PL parsing during sampling. |
| IN_PLAN_CACHE | VARCHAR2(1) | No | Indicates whether the current session is using the plan cache during sampling. |
| IN_SQL_OPTIMIZE | VARCHAR2(1) | No | Indicates whether the current session is performing SQL optimization during sampling. |
| IN_SQL_EXECUTION | VARCHAR2(1) | No | Indicates whether the current session is executing an SQL statement during sampling. |
| IN_PX_EXECUTION | VARCHAR2(1) | No | Indicates whether the current session is performing parallel SQL execution during sampling. If the current session is in this state, it must be also in the IN_SQL_EXECUTION state. |
| IN_SEQUENCE_LOAD | VARCHAR2(1) | No | Indicates whether the current session is generating values for an auto-increment column or a sequence during sampling. |
| IN_COMMITTING | VARCHAR2(1) | No | Indicates whether the current sampling point is in the transaction commit phase. |
| IN_STORAGE_READ | VARCHAR2(1) | No | Indicates whether the current sampling point is in the storage read phase. |
| IN_STORAGE_WRITE | VARCHAR2(1) | No | Indicates whether the current sampling point is in the storage write phase. |
| IN_REMOTE_DAS_EXECUTION | VARCHAR2(1) | No | Indicates whether the current sampling point is in the phase of remote execution by using direct access service (DAS). |
| MODULE | VARCHAR2(64) | Yes | The MODULE value recorded in the current session when being sampled. |
| ACTION | VARCHAR2(64) | Yes | The ACTION value recorded in the current session when being sampled. |
| CLIENT_ID | VARCHAR2(64) | Yes | The CLIENT_ID value recorded in the current session when being sampled. |
| BACKTRACE | VARCHAR2(512) | Yes | An auxiliary debugging field, which records the code call stack when an event occurs. |
| PLAN_ID | NUMBER(38) | 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 | NUMBER | Yes | The time interval for calculating the time model, in μs.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| TM_DELTA_CPU_TIME | NUMBER | Yes | The amount of CPU time spent in the last interval indicated by the TM_DELTA_TIME column.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| TM_DELTA_DB_TIME | NUMBER | Yes | The amount of time spent in database calls in the last interval indicated by the TM_DELTA_TIME column.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| TOP_LEVEL_SQL_ID | CHAR(32) | Yes | The ID of the top-level SQL statement.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| IN_PLSQL_COMPILATION | VARCHAR2(1) | No | The current status of PL compilation. Valid values: Y and N.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| IN_PLSQL_EXECUTION | VARCHAR2(1) | No | The current status of PL execution. Valid values: Y and N.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_OBJECT_ID | NUMBER | Yes | The ID of the top-level PL object.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_ID | NUMBER | Yes | The ID of the top-level PL subprogram.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | VARCHAR2(32) | Yes | The name of the top-level PL subprogram.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_OBJECT_ID | NUMBER | Yes | The ID of the PL object that is being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_SUBPROGRAM_ID | NUMBER | Yes | The ID of the PL subprogram that is being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_SUBPROGRAM_NAME | VARCHAR2(32) | Yes | The name of the PL subprogram that is being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
Example
obclient [SYS]> SELECT * FROM SYS.DBA_WR_ACTIVE_SESSION_HISTORY WHERE ROWNUM <= 2;
The query 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 | 1004 | 1 | xx.xx.xx.xx | 28824 | 830 | 3831110828032 | 05-JAN-24 02.35.34.468508 PM | 0 | 1 | ON CPU | NULL | Y0-0000000000000000-0-0 | 0 | -1 | 0 | 0 | 0 | 0 | NULL | 0 | N | N | N | N | N | N | N | N | N | N | N | N | T1004_Occam | NULL | NULL | NULL | NULL | 0 | 4781 | 4781 | 4781 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
| 40001 | 1004 | 1 | xx.xx.xx.xx | 28824 | 900 | 4436701216768 | 05-JAN-24 02.36.33.657286 PM | 0 | 1 | ON CPU | NULL | Y70980BA1CCFB-00060E2D0999C854-0-0 | 0 | -1 | 0 | 0 | 0 | 0 | NULL | 0 | N | N | N | N | N | N | N | N | N | N | N | N | T1004_ArcSender | NULL | NULL | NULL | NULL | 0 | 1086892 | 1086892 | 1086892 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+-----------+---------+----------------+----------+-----------+---------------+------------------------------+---------+--------------+---------------+--------+------------------------------------+----------+----------+-------------+------+------+------+------------------+------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------+--------+--------+-----------+-----------+---------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
2 rows in set (0.113 sec)