V$ACTIVE_SESSION_HISTORY

2025-11-27 10:07:56  Updated

Note

Purpose

This view displays the history of active sessions.

Columns

Column Type Nullable Description
SVR_IP varchar(46) NO The IP address of the server to which the sample belongs.
SVR_PORT bigint(20) NO The port number of the server to which the sample belongs.
SAMPLE_ID bigint(20) NO The unique ID of the sample.
SAMPLE_TIME datetime NO The time when the sample was taken.
CON_ID bigint(20) NO The tenant ID.
USER_ID bigint(20) NO The ID of the user of the sampled session.
SESSION_ID bigint(20) NO The ID of the sampled session.
For V4.2.x:
  • V4.2.5 BP4 and earlier versions:
    This field indicates the Server Session ID in all connection modes (direct mode/ODP mode).
  • V4.2.5 BP4 and later versions:
    • In direct mode:
      This field indicates the Server Session ID.
    • In ODP mode:
      • If the client_session_id_version = 2 parameter is set in ODP, this field indicates the Client Session ID.
      • If client_session_id_version = 1, this field indicates the Server Session ID.
SESSION_TYPE varchar(10) NO The session type:
  • FOREGROUND: a foreground session, that is, a user session
  • BACKGROUND: a background session. This value is not supported.
    		•
    SESSION_STATE varchar(7) NO The session state:
  • ON CPU: the session is not waiting and is executing an SQL statement.
  • WAITING: the session is waiting. For more information, see the EVENT field.
    		•
    SQL_ID varchar(32) NO The SQL ID.
    PLAN_ID bigint(20) NO The plan ID of the sampled SQL statement in the plan cache. This ID is used to associate the sampling point with the plan.
    EVENT varchar(64) NO The description of the waiting event.
    EVENT_NO bigint(20) NO The internal ID of the waiting event. This ID is used to join with other tables.
    EVENT_ID bigint(20) NO The ID of the current waiting event.

    Note

    This field is available starting with V4.2.2.
    P1TEXT varchar(64) NO The name of the first parameter of the waiting event. The name varies depending on the value of the EVENT field.
    P1 bigint(20) NO The value of the first parameter of the waiting event.
    P2TEXT varchar(64) NO The name of the second parameter of the waiting event. The name varies depending on the value of the EVENT field.
    P2 bigint(20) NO The value of the second parameter of the waiting event.
    P3TEXT varchar(64) NO The name of the third parameter of the waiting event. The name varies depending on the value of the EVENT field.
    P3 bigint(20) NO The value of the third parameter of the waiting event.
    WAIT_CLASS varchar(64) NO The type of the waiting event.
    WAIT_CLASS_ID bigint(20) NO The ID of the type of the waiting event. This ID is used to join with other tables.
    TIME_WAITED bigint(20) NO The total waiting time of the waiting event, in microseconds (us).
    SQL_PLAN_LINE_ID bigint(20) NO The ID of the SQL operator in the SQL plan at the time when the sample was taken.
    GROUP_ID bigint(20) NO The group information of the sampling point

    Note

    This field is available starting with V4.2.3.
    PLAN_HASH bigint(20) unsigned NO The plan_hash of the executed SQL statement

    Note

    This field is available starting with V4.2.4.
    THREAD_ID bigint(20) NO The ID of the thread to which the active session belongs

    Note

    This field is available starting with V4.2.4.
    STMT_TYPE bigint(20) NO The SQL type of the active session

    Note

    This field is available starting with V4.2.4.
    TIME_MODEL bigint(20) NO The time model information, which is a collection of data from all IN_XXX fields (such as IN_PARSE and IN_PL_PARSE).

    Note

    This field is available starting with V4.2.4.
    IN_PARSE varchar(1) NO Indicates whether the session is parsing an SQL statement at the time when the sample was taken.
    IN_PL_PARSE varchar(1) NO Indicates whether the session is parsing an SQL PL statement at the time when the sample was taken.
    IN_PLAN_CACHE varchar(1) NO Indicates whether the session is matching a plan in the plan cache at the time when the sample was taken.
    IN_SQL_OPTIMIZE varchar(1) NO Indicates whether the session is optimizing an SQL statement at the time when the sample was taken.
    IN_SQL_EXECUTION varchar(1) NO Indicates whether the session is executing an SQL statement at the time when the sample was taken.
    IN_PX_EXECUTION varchar(1) NO Indicates whether the session is executing an SQL statement in parallel at the time when the sample was taken. If the session is in this state, it must also be in the IN_SQL_EXECUTION state.
    IN_SEQUENCE_LOAD varchar(1) NO Indicates whether the session is fetching values from an auto-increment column or a sequence at the time when the sample was taken.
    IN_COMMITTING varchar(1) NO Indicates whether the sampling point is in the transaction commit phase.

    Note

    This field is available starting with V4.2.1.
    IN_STORAGE_READ varchar(1) NO Indicates whether the sampling point is in the storage read phase.

    Note

    This field is available starting with V4.2.1.
    IN_STORAGE_WRITE varchar(1) NO Indicates whether the current sampling point is in the storage write stage.

    Note

    This column is available starting with V4.2.1.
    IN_REMOTE_DAS_EXECUTION varchar(1) NO Indicates whether the current sampling point is in the DAS remote execution stage.

    Note

    This column is available starting with V4.2.1.
    IN_FILTER_ROWS varchar(1) NO Indicates whether the current sampling point is in the storage pushdown execution stage.

    Note

    This column is available starting with V4.2.2.
    IN_RPC_ENCODE varchar(1) NO Indicates whether the current SQL is undergoing serialization operations.

    Note

    This column is available starting with V4.2.5 for V4.2.x.
    IN_RPC_DECODE varchar(1) NO Indicates whether the current SQL is undergoing deserialization operations.

    Note

    This column is available starting with V4.2.5 for V4.2.x.
    IN_CONNECTION_MGR varchar(1) NO Indicates whether the current SQL is undergoing connection establishment operations.

    Note

    This column is available starting with V4.2.5 for V4.2.x.
    PROGRAM varchar(64) NO The name of the program being executed at the current sampling point:
    • Background thread: the thread name, such as observer or WrTimer.
    • Foreground thread:
      • For a regular request, it is user@client_ip (thread_name).
      • For inner_sql remote execution, it is INNER SQL REMOTE EXEC (thread_name).
      • For DAS remote execution, it is DAS REMOTE EXEC (thread_name).

    Note

    This column is available starting with V4.2.2.
    MODULE varchar(64) NO The MODULE value recorded at the time of sampling, set by the DBMS_APPLICATION_INFO.SET_MODULE package.

    Note

    This column can be set starting with V4.2.3.
    ACTION varchar(64) NO The ACTION value recorded at the time of sampling, set by the DBMS_APPLICATION_INFO.SET_ACTION package.

    Note

    This column can be set starting with V4.2.3.
    CLIENT_ID varchar(64) NO The CLIENT_ID value recorded at the time of sampling, set by the DBMS_APPLICATION_INFO.set_identifier package.

    Note

    This column can be set starting with V4.2.3.
    BACKTRACE varchar(512) NO An auxiliary debugging field used to record the code call stack at the time of the event. The value of this field is always NULL.
    TM_DELTA_TIME bigint(20) NO The time interval for calculating the time model, in microseconds.

    Note

    This column is available starting with V4.2.2.
    TM_DELTA_CPU_TIME bigint(20) NO The amount of time spent on the CPU during the TM_DELTA_TIME period in the past.

    Note

    This column is available starting with V4.2.2.
    TM_DELTA_DB_TIME bigint(20) NO The amount of time spent on database calls during the TM_DELTA_TIME period in the past.

    Note

    This column is available starting with V4.2.2.
    TOP_LEVEL_SQL_ID varchar(32) NO The ID of the top-level SQL statement.

    Note

    This column is available starting with V4.2.2.
    IN_PLSQL_COMPILATION varchar(1) NO The PL compilation status: Y/N.

    Note

    This column is available starting with V4.2.2.
    IN_PLSQL_EXECUTION varchar(1) NO The PL execution status: Y/N.

    Note

    This column is available starting with V4.2.2.
    PLSQL_ENTRY_OBJECT_ID bigint(20) NO The OBJECT ID of the top-level PL statement.

    Note

    This column is available starting with V4.2.2.
    PLSQL_ENTRY_SUBPROGRAM_ID bigint(20) NO The subprogram ID of the top-level PL statement.

    Note

    This column is available starting with V4.2.2.
    PLSQL_ENTRY_SUBPROGRAM_NAME varchar(32) NO The subprogram name of the top-level PL statement.

    Note

    This column is available starting with V4.2.2.
    PLSQL_OBJECT_ID bigint(20) NO The ID of the PL object being executed.

    Note

    This column is available starting with V4.2.2.
    PLSQL_SUBPROGRAM_ID bigint(20) NO The ID of the PL subprogram being executed.

    Note

    This column is available starting with V4.2.2.
    PLSQL_SUBPROGRAM_NAME varchar(32) NO The name of the PL subprogram currently being executed.

    Note

    This column is available starting with V4.2.2.
    BLOCKING_SESSION_ID bigint(20) NO If the current session is blocked, displays the session ID of the session that is blocking the current session. This column is effective only in lock conflict scenarios and displays the session ID of the session that holds the lock.

    Note

    • For V4.3.x, this column is available starting with V4.3.5.
    • For V4.2.x, this column is available starting with V4.2.3.
    TABLET_ID bigint(20) NO The ID of the tablet that is being processed by the current SQL statement.

    Note

    • For V4.3.x, this column is available starting with V4.3.5.
    • For V4.2.x, this column is available starting with V4.2.5.
    PROXY_SID bigint(20) NO The ID of the proxy session.

    Note

    • For V4.3.x, this column is available starting with V4.3.5.
    • For V4.2.x, this column is available starting with V4.2.5.
    TX_ID bigint(20) NO The ID of the current transaction.

    Note

    • For V4.3.x, this column is available starting with V4.3.5.
    • For V4.2.x, this column is available starting with V4.2.3.
    DELTA_READ_IO_REQUESTS bigint(20) NO The number of read requests between two sampling intervals.

    Note

    For V4.2.x, this column is available starting with V4.2.5 BP3.
    DELTA_READ_IO_BYTES bigint(20) NO The cumulative size of files read between two sampling intervals.

    Note

    For V4.2.x, this column is available starting with V4.2.5 BP3.
    DELTA_WRITE_IO_REQUESTS bigint(20) NO The number of write requests between two sampling intervals.

    Note

    For V4.2.x, this column is available starting with V4.2.5 BP3.
    DELTA_WRITE_IO_BYTES bigint(20) NO The cumulative size of files written between two sampling intervals.

    Note

    For V4.2.x, this column is available starting with V4.2.5 BP3.

    Sample query

    Query the active session history of the current OBServer node.

    obclient [oceanbase]> SELECT * FROM oceanbase.V$ACTIVE_SESSION_HISTORY limit 1 \G

    The query result is as follows:

    *************************** 1. row ***************************
                     SVR_IP: xx.xx.xx.xx
                   SVR_PORT: 2882
                  SAMPLE_ID: 1919434
                SAMPLE_TIME: 2025-03-04 10:44:17.593988
                     CON_ID: 1001
                    USER_ID: 0
                 SESSION_ID: -9223372036854729409
               SESSION_TYPE: BACKGROUND
              SESSION_STATE: ON CPU
                     SQL_ID: NULL
                    PLAN_ID: 0
                   TRACE_ID: NULL
                      EVENT:
                   EVENT_NO: 0
                   EVENT_ID: NULL
                     P1TEXT:
                         P1: 0
                     P2TEXT:
                         P2: 0
                     P3TEXT:
                         P3: 0
                 WAIT_CLASS: OTHER
              WAIT_CLASS_ID: 100
                TIME_WAITED: 0
           SQL_PLAN_LINE_ID: NULL
                   GROUP_ID: 0
                  PLAN_HASH: NULL
                  THREAD_ID: 82216
                  STMT_TYPE: NULL
                 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_FILTER_ROWS: N
              IN_RPC_ENCODE: N
              IN_RPC_DECODE: N
          IN_CONNECTION_MGR: N
                    PROGRAM: T1001_LogService
                     MODULE: LogRestoreService
                     ACTION: RemoteLogWriter
                  CLIENT_ID: NULL
                  BACKTRACE: NULL
              TM_DELTA_TIME: 1017436
          TM_DELTA_CPU_TIME: 1017436
           TM_DELTA_DB_TIME: 1017436
           TOP_LEVEL_SQL_ID: NULL
       IN_PLSQL_COMPILATION: N
         IN_PLSQL_EXECUTION: N
      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
        BLOCKING_SESSION_ID: NULL
                  TABLET_ID: NULL
                  PROXY_SID: -9223372036854729409
                      TX_ID: NULL
     DELTA_READ_IO_REQUESTS: 0
        DELTA_READ_IO_BYTES: 0
    DELTA_WRITE_IO_REQUESTS: 0
       DELTA_WRITE_IO_BYTES: 0
1 row in set (0.026 sec)

    References

    Last
    Next

    OceanBaseOceanBase

    OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

    Company

    About OceanBaseContact UsPartner

    Product

    OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

    Resources

    DocsBlogWhite PaperTrust Center

    Follow us

    LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

    © OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

    Contact Us