V$OB_SQL_AUDIT

2025-12-04 07:10:03  Updated

Note

This view is available starting with V4.0.0.

Purpose

The V$OB_SQL_AUDIT view displays the statistics of each SQL request, including the source and execution status, on the current OBServer node. This view is tenant-separated. Other tenants cannot query across tenants except the sys tenant.

Columns

Column Type Nullable Description
SVR_IP varchar(46) NO IP address
SVR_PORT bigint(20) NO Port number
REQUEST_ID bigint(20) NO Request ID
SQL_EXEC_ID bigint(20) NO Execution ID
TRACE_ID varchar(128) NO Trace ID of the statement
SID bigint(20) unsigned NO Session ID
CLIENT_IP varchar(46) NO
  • If the client connects to an OBServer node through ODP, this field indicates the IP address of the ODP that forwards the request.
  • If the client connects directly to an OBServer node, this field indicates the IP address of the client that sends the request.
    		•
    CLIENT_PORT bigint(20) NO Port number of the client that sends the request
    TENANT_ID bigint(20) NO Tenant ID

    Note

    This field indicates the effective tenant ID starting from V4.2.3.
    TENANT_NAME varchar(64) NO Tenant name
    EFFECTIVE_TENANT_ID bigint(20) NO Effective tenant ID
    USER_ID bigint(20) NO User ID of the user that sends the request
    USER_NAME varchar(64) NO User name of the user that sends the request
    USER_GROUP bigint(20) NO Resource group ID to which the user belongs
    USER_CLIENT_IP varchar(46) NO IP address of the client that sends the request
    DB_ID bigint(20) unsigned NO Database ID
    DB_NAME varchar(128) NO Database name
    SQL_ID varchar(32) NO ID of the SQL statement

    Note

    Starting from V4.2.3, for PL requests executed by using CALL statements or anonymous blocks, this field indicates the actual MD5 value.
    QUERY_SQL longtext NO Actual SQL statement
    PLAN_ID bigint(20) NO ID of the execution plan
    AFFECTED_ROWS bigint(20) NO Number of affected rows
    RETURN_ROWS bigint(20) NO Number of returned rows
    PARTITION_CNT bigint(20) NO Number of partitions involved in the request
    RET_CODE bigint(20) NO Execution result return code:
  • 0: no error. The statement is successfully executed.
  • Other return codes: OceanBase Database error codes. For more information about the error codes, see Error codes
    		•
    QC_ID bigint(20) unsigned NO ID of the scheduler in parallel execution scenarios
    DFO_ID bigint(20) NO ID of the current subplan in parallel execution scenarios
    SQC_ID bigint(20) NO ID of the local coordinator in parallel execution scenarios
    WORKER_ID bigint(20) NO ID of the worker thread in parallel execution scenarios
    EVENT varchar(64) NO Name of the longest waiting event
    P1TEXT varchar(64) NO Parameter 1 of the waiting event
    P1 bigint(20) unsigned NO The value of wait event parameter 1.
    P2TEXT varchar(64) NO The value of wait event parameter 2.
    P2 bigint(20) unsigned NO The value of wait event parameter 2.
    P3TEXT varchar(64) NO The value of wait event parameter 3.
    P3 bigint(20) unsigned NO The value of wait event parameter 3.
    LEVEL bigint(20) NO The level of the wait event.
    WAIT_CLASS_ID bigint(20) NO The ID of the class to which the wait event belongs.
    WAIT_CLASS# bigint(20) NO The index of the class to which the wait event belongs.
    WAIT_CLASS varchar(64) NO The name of the class to which the wait event belongs.
    STATE varchar(19) NO The state of the wait event.
    WAIT_TIME_MICRO bigint(20) NO The time that the wait event waited, in microseconds.
    TOTAL_WAIT_TIME_MICRO bigint(20) NO The total time of all waits during execution, in microseconds.
    TOTAL_WAITS bigint(20) NO The total number of waits during execution.
    RPC_COUNT bigint(20) NO The number of RPCs sent.
    PLAN_TYPE bigint(20) NO The execution plan type:
  • local
  • remote
  • distribute
    		•
    IS_INNER_SQL tinyint(4) NO Indicates whether the request is an internal SQL request.
    IS_EXECUTOR_RPC tinyint(4) NO Indicates whether the request is an RPC request.
    IS_HIT_PLAN tinyint(4) NO Indicates whether the plan cache was hit.
    REQUEST_TIME bigint(20) NO The start time of execution, in microseconds.
    ELAPSED_TIME bigint(20) NO The total time consumed from receiving the request to the end of execution, in microseconds.
    NET_TIME bigint(20) NO The time from sending the RPC to receiving the request, in microseconds.
    NET_WAIT_TIME bigint(20) NO The time from receiving the request to entering the queue, in microseconds.
    QUEUE_TIME bigint(20) NO The waiting time of the request in the queue, in microseconds.
    DECODE_TIME bigint(20) NO The time for decoding after the request is dequeued, in microseconds.
    GET_PLAN_TIME bigint(20) NO The time from the start of processing to obtaining the plan, in microseconds.
    EXECUTE_TIME bigint(20) NO The time consumed for plan execution, in microseconds.
    APPLICATION_WAIT_TIME bigint(20) unsigned NO The total time of all application events, in microseconds.
    CONCURRENCY_WAIT_TIME bigint(20) unsigned NO The total time of all concurrency events, in microseconds.
    USER_IO_WAIT_TIME bigint(20) unsigned NO The total time of all user I/O events, in microseconds.
    SCHEDULE_TIME bigint(20) unsigned NO The time of all schedule events, in microseconds.
    ROW_CACHE_HIT bigint(20) NO The number of row cache hits.
    BLOOM_FILTER_CACHE_HIT bigint(20) NO The number of Bloom filter cache hits.
    BLOCK_CACHE_HIT bigint(20) NO The number of block cache hits.
    DISK_READS bigint(20) NO The number of physical reads.
    RETRY_CNT bigint(20) NO The number of retries.
    TABLE_SCAN tinyint(4) NO Indicates whether the request contains a full table scan.
    CONSISTENCY_LEVEL bigint(20) NO The consistency level. Valid values:
    • -1 : invalid
    • 1 : read data stored in the SSTable
    • 2 : weak consistency read
    • 3 : strong consistency read
    MEMSTORE_READ_ROW_COUNT bigint(20) NO The number of rows read from the MemStore.
    SSSTORE_READ_ROW_COUNT bigint(20) NO The number of rows read from the SSStore.
    DATA_BLOCK_READ_CNT bigint(20) NO The number of data microblocks accessed.
    DATA_BLOCK_CACHE_HIT bigint(20) NO The number of data microblocks cached.
    INDEX_BLOCK_READ_CNT bigint(20) NO The number of intermediate microblocks accessed.
    INDEX_BLOCK_CACHE_HIT bigint(20) NO The number of intermediate microblocks cached.
    BLOCKSCAN_BLOCK_CNT bigint(20) NO The number of data microblocks scanned in one direction.
    BLOCKSCAN_ROW_CNT bigint(20) NO The number of data rows scanned in one direction.
    PUSHDOWN_STORAGE_FILTER_ROW_CNT bigint(20) NO The number of rows filtered by the storage filter pushed down.
    REQUEST_MEMORY_USED bigint(20) NO The amount of memory consumed by the request.
    EXPECTED_WORKER_COUNT bigint(20) NO The number of worker threads expected for the request.
    USED_WORKER_COUNT bigint(20) NO The number of worker threads actually used by the request.
    SCHED_INFO varchar(16384) NO The scheduling information of the request.
    FUSE_ROW_CACHE_HIT bigint(20) NO This column is not supported. The value is NULL by default.
    PS_CLIENT_STMT_ID bigint(20) NO The Prepare ID corresponding to the request:
    • -1 : the SQL statement does not use the PS protocol.
    • A value other than -1 : the SQL statement uses the PS protocol, and the value is the unique identifier returned by the PS protocol for the statement on the client.
    PS_INNER_STMT_ID bigint(20) NO The Prepare ID corresponding to the request:
    • -1 : the SQL statement does not use the PS protocol.
    • A value other than -1 : the SQL statement uses the PS protocol, and the value is the unique identifier returned by the PS protocol for the statement internally.
    TX_ID bigint(20) NO The transaction ID corresponding to the request.
    SNAPSHOT_VERSION bigint(20) NO The read snapshot version used by the SQL statement.
    REQUEST_TYPE bigint(20) NO The type of the request:
  • 0: invalid
  • 1: an internal request
  • 2: a local request, such as a Local plan
  • 3: a remote request
  • 4: a distributed request
  • 5: a Prepare request for SQL
  • 6: an Execute Stmt request for SQL
    		•
    IS_BATCHED_MULTI_STMT tinyint(4) NO Whether to optimize Batch Multi Stmt.
    OB_TRACE_INFO varchar(4096) NO The trace information set by the user.
    PLAN_HASH bigint(20) unsigned NO The hash value of the execution plan.
    LOCK_FOR_READ_TIME bigint(20) NO The time consumed in waiting for a lock when reading data, in microseconds.
    PARAMS_VALUE longtext NO The parameter values bound by PS.
    RULE_NAME varchar(256) NO The name of the rule.

    Note

    This column is available starting with V4.1.0.
    PARTITION_HIT tinyint(4) NO
    • If the DAS execution plan is obtained, 1 indicates that the local partition is hit, and 0 indicates that it is not hit.
    • If the DAS plan is not obtained, the default value is 1.

    Note

    This column is available starting with V4.1.0.
    TX_INTERNAL_ROUTING bigint(20) NO Indicates whether the transaction routing is enabled for the current transaction:
    • 0: transaction routing is not enabled. Statements can only be routed to the OBServer node where the transaction started.
    • 1: transaction routing is enabled. Statements within the transaction can be routed optimally by the proxy.

    Note

    • This column is available starting with V4.1.0.
    • OBProxy supports this feature starting with V4.1.0.
    TX_STATE_VERSION bigint(20) unsigned NO When transaction routing is enabled, the version number increments if the transaction state changes after executing a statement. If the transaction state does not change, the version number remains unchanged

    Note

    • This column was introduced in V4.1.0.
    • OBProxy supports this column starting from V4.1.0.
    FLT_TRACE_ID varchar(1024) NO The trace ID for full-link tracing of the record. If this field is empty, it indicates that the record is not being monitored by full-link tracing. This field is a UUID, which is different from Trace. Its format is similar to: 000600d6-a5de-038c-6c80-df07e4e79149

    Note

    This column was introduced in V4.2.1.
    PL_TRACE_ID varchar(128) NO The trace ID of the outer PL for the current SQL statement. If there is no outer PL, this value is NULL

    Note

    This column was introduced in V4.2.2.
    PLSQL_EXEC_TIME bigint(20) NO The time spent on PL execution (excluding SQL execution time), in microseconds

    Note

    This column was introduced in V4.2.2.
    FORMAT_SQL_ID varchar(32) NO The MD5 value generated from the formatted SQL text for the record

    Note

    This column was introduced in V4.2.3.
    NETWORK_WAIT_TIME bigint(20) unsigned YES The total time for all network-related events, in microseconds

    Note

    This column was introduced in V4.2.3.
    STMT_TYPE varchar(128) YES The DML type is returned as follows:
    • The DDL type is returned as DDL_STMT.
    • The DCL type is returned as DCL_STMT.
    • The TCL type is returned as TCL_STMT.
    • Other types are returned as OTHER_STMT.

    Note

    This column was introduced in V4.2.3.
    TOTAL_MEMSTORE_READ_ROW_COUNT bigint(20) NO The total number of rows read from MemStore during the entire process (only displayed in the thread that shows query_text).

    Note

    This column was introduced in V4.2.3.
    TOTAL_SSSTORE_READ_ROW_COUNT bigint(20) NO The total number of rows read from SSSTORE during the entire process (only displayed in the thread that shows query_text).

    Note

    This column was introduced in V4.2.3.
    PROXY_USER varchar(128) NO
    • If the user logs in as a proxy user, the name of the proxy user is returned.
    • If the user logs in as a non-proxy user, this field is empty.

    Note

    This column was introduced in V4.2.3.
    SEQ_NUM bigint(20) NUMBER(38) The sequence number of the statement in the transaction.

    Note

    This column was introduced in V4.2.5. The default value is NULL.
    PLSQL_COMPILE_TIME bigint(20) NUMBER(38) The time spent on PL compilation.

    Note

    This column was introduced in V4.2.5 BP2. The default value is NULL.
    USER_CLIENT_PORT bigint(20) NUMBER(38) The client port number.

    Note

    This column was introduced in V4.2.5 BP2.
    TRANS_STATUS varchar(256) VARCHAR2(256) The status of the transaction, which can be either explicitly or implicitly started or not started.

    Note

    This column was introduced in V4.2.5 BP2.

    Sample query

    Query the source, execution status, and other statistics of each SQL request on the current OBServer node.

    obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SQL_AUDIT LIMIT 1\G

    The query result is as follows:

    *************************** 1. row ***************************
                         SVR_IP: 172.xx.xx.xx
                       SVR_PORT: 2882
                     REQUEST_ID: 93264
                    SQL_EXEC_ID: 1118575
                       TRACE_ID: YB4XXXXXXXXX-000XXXXXXXXXXXXX-0-0
                            SID: 3221633127
                      CLIENT_IP: 0.0.0.0
                    CLIENT_PORT: 0
                      TENANT_ID: 1002
                    TENANT_NAME: sys
            EFFECTIVE_TENANT_ID: 1002
                        USER_ID: 200001
                      USER_NAME: root
                     USER_GROUP: 0
                 USER_CLIENT_IP: 0.0.0.0
                          DB_ID: 201001
                        DB_NAME: oceanbase
                         SQL_ID: B7A6FA97FEC98C06F9586D23935AC4C6
                      QUERY_SQL: START TRANSACTION
                        PLAN_ID: 0
                  AFFECTED_ROWS: 0
                    RETURN_ROWS: 0
                  PARTITION_CNT: 0
                       RET_CODE: 0
                          QC_ID: 0
                         DFO_ID: 0
                         SQC_ID: 0
                      WORKER_ID: 0
                          EVENT:
                         P1TEXT:
                             P1: 0
                         P2TEXT:
                             P2: 0
                         P3TEXT:
                             P3: 0
                          LEVEL: 0
                  WAIT_CLASS_ID: 100
                    WAIT_CLASS#: 0
                     WAIT_CLASS: OTHER
                          STATE: MAX_WAIT TIME ZERO
                WAIT_TIME_MICRO: 0
          TOTAL_WAIT_TIME_MICRO: 0
                    TOTAL_WAITS: 0
                      RPC_COUNT: 0
                      PLAN_TYPE: 0
                   IS_INNER_SQL: 1
                IS_EXECUTOR_RPC: 0
                    IS_HIT_PLAN: 0
                   REQUEST_TIME: 1722835002609834
                   ELAPSED_TIME: 118
                       NET_TIME: 0
                  NET_WAIT_TIME: 0
                     QUEUE_TIME: 0
                    DECODE_TIME: 0
                  GET_PLAN_TIME: 108
                   EXECUTE_TIME: 10
          APPLICATION_WAIT_TIME: 0
          CONCURRENCY_WAIT_TIME: 0
              USER_IO_WAIT_TIME: 0
                  SCHEDULE_TIME: 0
                  ROW_CACHE_HIT: 0
         BLOOM_FILTER_CACHE_HIT: 0
                BLOCK_CACHE_HIT: 0
                     DISK_READS: 0
                      RETRY_CNT: 0
                     TABLE_SCAN: 0
              CONSISTENCY_LEVEL: -1
        MEMSTORE_READ_ROW_COUNT: 0
         SSSTORE_READ_ROW_COUNT: 0
            DATA_BLOCK_READ_CNT: 0
           DATA_BLOCK_CACHE_HIT: 0
           INDEX_BLOCK_READ_CNT: 0
          INDEX_BLOCK_CACHE_HIT: 0
            BLOCKSCAN_BLOCK_CNT: 0
              BLOCKSCAN_ROW_CNT: 0
PUSHDOWN_STORAGE_FILTER_ROW_CNT: 0
            REQUEST_MEMORY_USED: 131072
          EXPECTED_WORKER_COUNT: 0
              USED_WORKER_COUNT: 0
                     SCHED_INFO: NULL
             FUSE_ROW_CACHE_HIT: 0
              PS_CLIENT_STMT_ID: -1
               PS_INNER_STMT_ID: -1
                          TX_ID: 16061
               SNAPSHOT_VERSION: 0
                   REQUEST_TYPE: 1
          IS_BATCHED_MULTI_STMT: 0
                  OB_TRACE_INFO: NULL
                      PLAN_HASH: 0
             LOCK_FOR_READ_TIME: 0
                   PARAMS_VALUE:
                      RULE_NAME:
                  PARTITION_HIT: 1
            TX_INTERNAL_ROUTING: 0
               TX_STATE_VERSION: 0
                   FLT_TRACE_ID:
                    PL_TRACE_ID: NULL
                PLSQL_EXEC_TIME: 0
                  FORMAT_SQL_ID:
              NETWORK_WAIT_TIME: 0
                      STMT_TYPE: START_TRANS
  TOTAL_MEMSTORE_READ_ROW_COUNT: 0
   TOTAL_SSSTORE_READ_ROW_COUNT: 0
                     PROXY_USER:
1 row in set

    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