Note
This view was introduced in OceanBase Database V4.0.0.
Purpose
The V$ACTIVE_SESSION_HISTORY view displays the active session history.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node to which the sample belongs. |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node to which the sample belongs. |
| SAMPLE_ID | bigint(20) | NO | The unique ID of the sample. |
| SAMPLE_TIME | datetime | NO | The sampling time. |
| CON_ID | bigint(20) | NO | The ID of the tenant. |
| USER_ID | bigint(20) | NO | The ID of the user whose session is sampled. |
| SESSION_ID | bigint(20) | NO | The ID of the sampled session. |
| SESSION_TYPE | varchar(10) | NO | The type of the session. Valid values: FOREGROUND: indicates a foreground session, namely a user session.BACKGROUND: indicates a background session, which is not supported for now. |
| SESSION_STATE | varchar(7) | NO | The status of the session. 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 | varchar(32) | NO | The ID of the SQL statement. |
| 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. |
| EVENT | varchar(64) | NO | The description of the wait event. |
| EVENT_NO | bigint(20) | NO | The internal number of the wait event, which is used for join queries with other tables. |
| P1TEXT | varchar(64) | NO | The name of the first parameter of the wait event. The parameter name varies with the event. |
| P1 | bigint(20) | NO | The value of the first parameter of the wait event. |
| P2TEXT | varchar(64) | NO | The name of the second parameter of the wait event. The parameter name varies with the event. |
| P2 | bigint(20) | NO | The value of the second parameter of the wait event. |
| P3TEXT | varchar(64) | NO | The name of the third parameter of the wait event. The parameter name varies with the event. |
| P3 | bigint(20) | NO | The value of the third parameter of the wait event. |
| WAIT_CLASS | varchar(64) | NO | The class of the wait event. |
| WAIT_CLASS_ID | bigint(20) | NO | The ID of the class of the wait event, which is used for join queries with other tables. |
| TIME_WAITED | bigint(20) | NO | The total amount of wait time of the wait event, in μs. |
| SQL_PLAN_LINE_ID | bigint(20) | NO | The ID of the SQL operator in the SQL plan when the session is being sampled. |
| IN_PARSE | varchar(1) | NO | Indicates whether SQL parsing is being performed in the session during sampling. |
| IN_PL_PARSE | varchar(1) | NO | Indicates whether SQL PL parsing is being performed in the session during sampling. |
| IN_PLAN_CACHE | varchar(1) | NO | Indicates whether plan caching is being performed in the session during sampling. |
| IN_SQL_OPTIMIZE | varchar(1) | NO | Indicates whether SQL parsing and tuning are being performed in the session during sampling. |
| IN_SQL_EXECUTION | varchar(1) | NO | Indicates whether an SQL statement is being executed in the session during sampling. |
| IN_PX_EXECUTION | varchar(1) | NO | Indicates whether parallel SQL execution is being performed in the session during sampling. If the session is in this state, it must also be in the state of SQL statement execution. |
| IN_SEQUENCE_LOAD | varchar(1) | NO | Indicates whether values are being generated for an auto-increment column or a sequence in the session during sampling. |
| IN_COMMITTING | varchar(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 | varchar(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 | varchar(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 | varchar(1) | NO | Indicates whether the current sampling point is in the phase of remote execution by using direct access service (DAS).
NoteThis column is introduced since OceanBase Database V4.2.1. |
| MODULE | varchar(64) | NO | The MODULE value recorded during session sampling. |
| ACTION | varchar(64) | NO | The ACTION value recorded during session sampling. The column value is always NULL. |
| CLIENT_ID | varchar(64) | NO | The CLIENT_ID value recorded during session sampling. The column value is always NULL. |
| BACKTRACE | varchar(512) | NO | An auxiliary debugging column, which records the code call stack when an event occurs. The column value is always NULL. |
| TM_DELTA_TIME | bigint(20) | NO | The interval for calculating the time model, in microseconds.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| TM_DELTA_CPU_TIME | bigint(20) | NO | The amount of CPU time consumed during the previous interval specified by TM_DELTA_TIME.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| TM_DELTA_DB_TIME | bigint(20) | NO | The amount of time spent on database calls during the previous interval specified by TM_DELTA_TIME.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| TOP_LEVEL_SQL_ID | varchar(32) | NO | The top-level SQL ID.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| IN_PLSQL_COMPILATION | varchar(1) | NO | Indicates whether PL compilation is in progress. Valid values: Y and N.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| IN_PLSQL_EXECUTION | varchar(1) | NO | Indicates whether PL execution is in progress. Valid values: Y and N.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_OBJECT_ID | bigint(20) | NO | The ID of the top-level PL object.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_ID | bigint(20) | NO | The ID of the top-level PL subprogram.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | varchar(32) | NO | The name of the top-level PL subprogram.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_OBJECT_ID | bigint(20) | NO | The ID of the PL object being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_SUBPROGRAM_ID | bigint(20) | NO | The ID of the PL subprogram being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
| PLSQL_SUBPROGRAM_NAME | varchar(32) | NO | The name of the PL subprogram being executed.
NoteThis column is introduced since OceanBase Database V4.2.2. |
Sample query
obclient [oceanbase]> SELECT * FROM oceanbase.V$ACTIVE_SESSION_HISTORY limit 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 | 4091830 | 2024-01-03 16:50:51 | 1002 | 0 | 3397319131136 | BACKGROUND | WAITING | | 0 | Y0-0000000000000000-0-0 | latch: config lock wait | 91 | 40 | address | 140638541328000 | number | 1073743313 | tries | 1 | CONCURRENCY | 104 | 7958 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | T1002_LogArb | NULL | NULL | NULL | NULL | 947046 | 708 | 947046 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
| xx.xx.xx.xx | 2882 | 4091850 | 2024-01-03 16:50:54 | 1002 | 0 | 3397319131136 | BACKGROUND | WAITING | | 0 | Y0-0000000000000000-0-0 | latch: config lock wait | 91 | 40 | address | 140638541328000 | number | 1073743313 | tries | 1 | CONCURRENCY | 104 | 6399 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | T1002_LogArb | NULL | NULL | NULL | NULL | 947046 | 708 | 947046 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
+----------------+----------+-----------+---------------------+--------+---------+---------------+--------------+---------------+--------+---------+-------------------------+-------------------------+----------+----------+---------+-----------------+--------+------------+--------+------+-------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+--------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
2 rows in set (0.167 sec)