Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The GV$OB_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:
|
| SESSION_STATE | VARCHAR2(7) | No | The session status. Valid values:
|
| SQL_ID | VARCHAR2(32) | Yes | The ID of the SQL statement. |
| PLAN_ID | NUMBER | 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) | Yes | 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 queries. |
| EVENT_ID | NUMBER | Yes | The ID of the current wait event. |
| P1TEXT | VARCHAR2(64) | No | The name of the first parameter of the wait event. The parameter name varies based on 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 based on 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 based on 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 queries. |
| TIME_WAITED | NUMBER | No | The total wait time of the wait event, in μs. |
| SQL_PLAN_LINE_ID | NUMBER | Yes | The ID of the SQL operator in the SQL plan when the session is being sampled. |
| IN_PARSE | VARCHAR2(1) | No | Indicates whether SQL parsing is being performed in the session during sampling. |
| IN_PL_PARSE | VARCHAR2(1) | No | Indicates whether SQL PL parsing is being performed in the session during sampling. |
| IN_PLAN_CACHE | VARCHAR2(1) | No | Indicates whether plan caching is being performed in the session during sampling. |
| IN_SQL_OPTIMIZE | VARCHAR2(1) | No | Indicates whether SQL parsing and tuning are being performed in the session during sampling. |
| IN_SQL_EXECUTION | VARCHAR2(1) | No | Indicates whether an SQL statement is being executed in the session during sampling. |
| IN_PX_EXECUTION | VARCHAR2(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 executing. |
| IN_SEQUENCE_LOAD | VARCHAR2(1) | No | Indicates whether values are being generated for an auto-increment column or a sequence in the session 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). |
| IN_FILTER_ROWS | VARCHAR2(1) | No | Indicates whether the current sampling point is in the phase of storage push-down execution. |
| PROGRAM | VARCHAR2(64) | Yes | The name of the program being executed at the current sampling point.
|
| MODULE | VARCHAR2(64) | Yes | The MODULE value recorded during session sampling. |
| ACTION | VARCHAR2(64) | Yes | The ACTION value recorded during session sampling. The field value is always NULL. |
| CLIENT_ID | VARCHAR2(64) | Yes | The CLIENT_ID value recorded during session sampling. The field value is always NULL. |
| BACKTRACE | VARCHAR2(512) | Yes | An auxiliary debugging field, which records the code call stack when an event occurs. The field value is always NULL. |
| TM_DELTA_TIME | NUMBER | Yes | The time interval for calculating the time model, in μs. |
| TM_DELTA_CPU_TIME | NUMBER | Yes | The amount of CPU time spent in the last interval indicated by the TM_DELTA_TIME field. |
| TM_DELTA_DB_TIME | NUMBER | Yes | The amount of time spent in database calls in the last interval indicated by the TM_DELTA_TIME field. |
| TOP_LEVEL_SQL_ID | CHAR(32) | Yes | The ID of the top-level SQL statement. |
| IN_PLSQL_COMPILATION | VARCHAR2(1) | No | The current status of PL compilation. Valid values: Y and N. |
| IN_PLSQL_EXECUTION | VARCHAR2(1) | No | The current status of PL execution. Valid values: Y and N. |
| PLSQL_ENTRY_OBJECT_ID | NUMBER | Yes | The ID of the top-level PL object. |
| PLSQL_ENTRY_SUBPROGRAM_ID | NUMBER | Yes | The ID of the top-level PL subprogram. |
| PLSQL_ENTRY_SUBPROGRAM_NAME | VARCHAR2(32) | Yes | The name of the top-level PL subprogram. |
| PLSQL_OBJECT_ID | NUMBER | Yes | The ID of the PL object that is being executed. |
| PLSQL_SUBPROGRAM_ID | NUMBER | Yes | The ID of the PL subprogram that is being executed. |
| PLSQL_SUBPROGRAM_NAME | VARCHAR2(32) | Yes | The name of the PL subprogram that is being executed. |
Example
obclient [oceanbase]> SELECT * FROM SYS.GV$OB_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 | 4040566 | 03-JAN-24 03.04.09.020829 PM | 1004 | 0 | 4531190497280 | BACKGROUND | WAITING | NULL | 0 | Y0-0000000000000000-0-0 | exec inner sql wait | 69 | 30000 | wait inner sql class | 0 | inner session id | 3221516104 | NULL | 0 | OTHER | 100 | 10871 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | T1004_Occam | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
| xx.xx.xx.xx | 2882 | 4040571 | 03-JAN-24 03.04.10.025456 PM | 1004 | 0 | 5098126180352 | BACKGROUND | WAITING | NULL | 0 | Y0-0000000000000000-0-0 | latch: config lock wait | 91 | 40 | address | 140638541328448 | number | 1073743313 | tries | 1 | CONCURRENCY | 104 | 9221 | NULL | N | N | N | N | N | N | N | N | N | N | N | N | T1004_TenantWea | NULL | NULL | NULL | NULL | 2045936 | 1538217 | 2045936 | NULL | N | N | NULL | NULL | NULL | NULL | NULL | NULL |
+----------------+----------+-----------+------------------------------+--------+---------+---------------+--------------+---------------+--------+---------+-------------------------+-------------------------+----------+----------+----------------------+-----------------+------------------+------------+--------+------+-------------+---------------+-------------+------------------+----------+-------------+---------------+-----------------+------------------+-----------------+------------------+---------------+-----------------+------------------+-------------------------+----------------+-----------------+--------+--------+-----------+-----------+---------------+-------------------+------------------+------------------+----------------------+--------------------+-----------------------+---------------------------+-----------------------------+-----------------+---------------------+-----------------------+
2 rows in set (0.126 sec)