Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The V$ACTIVE_SESSION_HISTORY view displays the active session history.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| SVR_IP | VARCHAR2(46) | NO | The IP address of the server to which the sample belongs. |
| SVR_PORT | NUMBER | NO | The port number of the server to which the sample belongs. |
| SAMPLE_ID | NUMBER | NO | The unique ID of the sample. |
| SAMPLE_TIME | TIMESTAMP(6) | NO | The sampling time. |
| CON_ID | NUMBER | NO | The ID of the tenant. |
| USER_ID | NUMBER | NO | The ID of the user whose session is sampled. |
| SESSION_ID | NUMBER | NO | The ID of the sampled session. |
| SESSION_TYPE | VARCHAR2(10) | NO | The session type. Valid values:FOREGROUND: foreground session, that is, user session.BACKGROUND: background session, which is not supported for now. |
| SESSION_STATE | VARCHAR2(7) | NO | The session status. Valid values:ON CPU: The SQL logic is being executed without waiting.WAITING: The session is waiting. For more information, see the EVENT column. |
| SQL_ID | VARCHAR2(32) | NO | The SQL ID. |
| PLAN_ID | bigint(20) | NO | The plan ID of the sampled SQL statement in the plan cache, which is used to associate the sampling point with the plan. |
| TRACE_ID | VARCHAR2(64) | NO | The trace ID of the operator. |
| EVENT | VARCHAR2(64) | NO | The description of the wait event. |
| EVENT_NO | NUMBER | NO | The internal number of the wait event, which is used for join query with other tables. |
| P1TEXT | VARCHAR2(64) | NO | The name of the first parameter of the wait event. The parameter name varies with the event. |
| P1 | NUMBER | NO | The value of the first parameter of the wait event. |
| P2TEXT | VARCHAR2(64) | NO | The name of the second parameter of the wait event. The parameter name varies with the event. |
| P2 | NUMBER | NO | The value of the second parameter of the wait event. |
| P3TEXT | VARCHAR2(64) | NO | The name of the third parameter of the wait event. The parameter name varies with the event. |
| P3 | NUMBER | NO | The value of the third parameter of the wait event. |
| WAIT_CLASS | VARCHAR2(64) | NO | The class of the wait event. |
| WAIT_CLASS_ID | NUMBER | NO | The ID of the class of the wait event, which is used for join query with other tables. |
| TIME_WAITED | NUMBER | NO | The total wait time of the wait event. Unit: us. |
| SQL_PLAN_LINE_ID | NUMBER | NO | The ID of the SQL operator in the SQL plan when the session is being sampled. |
| IN_PARSE | VARCHAR2(1) | NO | Indicates whether the session is parsing an SQL statement during sampling. |
| IN_PL_PARSE | VARCHAR2(1) | NO | Indicates whether the session is parsing an SQL PL statement during sampling. |
| IN_PLAN_CACHE | VARCHAR2(1) | NO | Indicates whether the session is matching a plan cache during sampling. |
| IN_SQL_OPTIMIZE | VARCHAR2(1) | NO | Indicates whether the session is optimizing SQL parsing during sampling. |
| IN_SQL_EXECUTION | VARCHAR2(1) | NO | Indicates whether the session is executing an SQL statement during sampling. |
| IN_PX_EXECUTION | VARCHAR2(1) | NO | Indicates whether the session is executing SQL statements in parallel during sampling. If the session is in this state, it must also be in the state of SQL statement executing. |
| IN_SEQUENCE_LOAD | VARCHAR2(1) | NO | Indicates whether the 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.
NoteThis column is introduced since OceanBase Database V4.2.1. |
| IN_STORAGE_READ | VARCHAR2(1) | NO | Indicates whether the current sampling point is in the storage read phase.
NoteThis column is introduced since OceanBase Database V4.2.1. |
| IN_STORAGE_WRITE | VARCHAR2(1) | NO | Indicates whether the current sampling point is in the storage write phase.
NoteThis column is introduced since OceanBase Database V4.2.1. |
| IN_REMOTE_DAS_EXECUTION | VARCHAR2(1) | NO | Indicates whether the current sampling point is in the remote execution phase of direct-attached storage (DAS).
NoteThis column is introduced since OceanBase Database V4.2.1. |
| MODULE | VARCHAR2(64) | NO | The MODULE value recorded during session sampling. The value is always NULL. |
| ACTION | VARCHAR2(64) | NO | The ACTION value recorded during session sampling. The value is always NULL. |
| CLIENT_ID | VARCHAR2(64) | NO | The CLIENT_ID value recorded during session sampling. The value is always NULL. |
| BACKTRACE | VARCHAR2(512) | NO | An auxiliary debugging column, which records the code call stack when an event occurs. The value is always NULL. |
| TM_DELTA_TIME | NUMBER | NO | 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 | NO | 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 | NO | 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) | NO | 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 | NO | The ID of the top-level PL object.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_ID | NUMBER | NO | The ID of the top-level PL subprogram.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | VARCHAR2(32) | NO | The name of the top-level PL subprogram.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_OBJECT_ID | NUMBER | NO | The ID of the PL object that is being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_SUBPROGRAM_ID | NUMBER | NO | 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) | NO | The name of the PL subprogram that is being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
Sample query
obclient [SYS]> SELECT * FROM SYS.V$ACTIVE_SESSION_HISTORY WHERE ROWNUM <= 2;
The query result is as follows:
+----------------+----------+-----------+------------------------------+--------+---------+---------------+--------------+---------------+--------+---------+-------------------------+-------------------------+----------+----------+----------------------+-----------------+------------------+------------+--------+------+-------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------+--------------------------------------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
| SVR_IP | SVR_PORT | SAMPLE_ID | SAMPLE_TIME | CON_ID | USER_ID | SESSION_ID | SESSION_TYPE | SESSION_STATE | SQL_ID | PLAN_ID | TRACE_ID | EVENT | EVENT_NO | EVENT_ID | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | WAIT_CLASS | WAIT_CLASS_ID | TIME_WAITED | SQL_PLAN_LINE_ID | 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 | 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 |
+----------------+----------+-----------+------------------------------+--------+---------+---------------+--------------+---------------+--------+---------+-------------------------+-------------------------+----------+----------+----------------------+-----------------+------------------+------------+--------+------+-------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------+--------------------------------------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
| xx.xx.xx.xx | 2882 | 4099661 | 03-JAN-24 05.07.19.954302 PM | 1004 | 0 | 5098126180352 | BACKGROUND | WAITING | NULL | 0 | Y0-0000000000000000-0-0 | exec inner sql wait | 69 | 30000 | wait inner sql class | 30001 | inner session id | 3221735589 | NULL | 0 | OTHER | 100 | 11129 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | T1004_TenantWea | MODULE (TX_UPDATE_WEAK_READ_VERSION) | NULL | NULL | NULL | 2045936 | 1538217 | 2045936 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
| xx.xx.xx.xx | 2882 | 4099646 | 03-JAN-24 05.07.18.948799 PM | 1004 | 0 | 4943507357696 | BACKGROUND | WAITING | NULL | 0 | Y0-0000000000000000-0-0 | latch: config lock wait | 91 | 40 | address | 140638541330368 | number | 1073743313 | tries | 1 | CONCURRENCY | 104 | 10514 | 1 | N | N | N | N | N | N | N | N | N | Y | N | N | T1004_ReqMemEvi | NULL | NULL | NULL | NULL | 2331130 | 133742 | 2331130 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
+----------------+----------+-----------+------------------------------+--------+---------+---------------+--------------+---------------+--------+---------+-------------------------+-------------------------+----------+----------+----------------------+-----------------+------------------+------------+--------+------+-------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------+--------------------------------------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
2 rows in set (0.079 sec)