Note
This view is available starting with V4.0.0.
Purpose
The GV$OB_SQL_AUDIT view displays statistics such as the source and execution status of each SQL request on all OBServer nodes. This view is tenant-separated, and only the system tenant can query it. Other tenants cannot query it across tenants.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| SVR_IP | VARCHAR2(46) | NO | IP address |
| SVR_PORT | NUMBER(38) | NO | Port number |
| REQUEST_ID | NUMBER(38) | NO | Request ID |
| SQL_EXEC_ID | NUMBER(38) | NO | Execution ID |
| TRACE_ID | VARCHAR2(128) | NO | Trace ID of the statement |
| SID | NUMBER(38) | NO | Session ID |
| CLIENT_IP | VARCHAR2(46) | NO | |
| CLIENT_PORT | NUMBER(38) | NO | Port number of the client that sends the request |
| TENANT_ID | NUMBER(38) | NO | Tenant ID
NoteThis field is available starting with V4.2.3. In this version, the field indicates the same value as the |
| EFFECTIVE_TENANT_ID | NUMBER(38) | NO | Tenant ID |
| TENANT_NAME | VARCHAR2(64) | NO | Tenant name |
| USER_ID | NUMBER(38) | NO | User ID of the user that sends the request |
| USER_NAME | VARCHAR2(64) | NO | User name of the user that sends the request |
| USER_GROUP | NUMBER(38) | YES | ID of the resource group to which the user belongs |
| USER_CLIENT_IP | VARCHAR2(32) | NO | IP address of the client that sends the request |
| DB_ID | NUMBER(38) | NO | Database ID |
| DB_NAME | VARCHAR2(128) | NO | Database name |
| SQL_ID | VARCHAR2(32) | NO | ID of the SQL statement
NoteThis field is available starting with V4.2.3. For PL requests executed by using the CALL statement or anonymous blocks, the field indicates the actual MD5 value generated. |
| QUERY_SQL | CLOB | NO | Actual SQL statement.
NoteThis field is available starting with V4.2.5 BP2. Only the SYS tenant can view this field. |
| PLAN_ID | NUMBER(38) | NO | Execution plan ID |
| AFFECTED_ROWS | NUMBER(38) | NO | Number of affected rows |
| RETURN_ROWS | NUMBER(38) | NO | Number of returned rows |
| PARTITION_CNT | NUMBER(38) | NO | Number of partitions involved in the request |
| RET_CODE | NUMBER(38) | NO | Return code of the execution result: |
| QC_ID | NUMBER(38) | NO | In parallel execution, the ID of the scheduler. |
| DFO_ID | NUMBER(38) | NO | In parallel execution, the ID of the current subplan. |
| SQC_ID | NUMBER(38) | NO | In parallel execution, the ID of the local coordinator. |
| WORKER_ID | NUMBER(38) | NO | In parallel execution, the ID of the worker thread. |
| EVENT | VARCHAR2(64) | YES | Name of the longest waiting event |
| P1TEXT | VARCHAR2(64) | YES | Parameter 1 of the waiting event |
| P1 | NUMBER(38) | YES | Value of parameter 1 of the waiting event |
| P2TEXT | VARCHAR2(64) | YES | Parameter 2 of the waiting event |
| P2 | NUMBER(38) | YES | The value of parameter 2 of the wait event. |
| P3TEXT | VARCHAR2(64) | YES | The value of parameter 3 of the wait event. |
| P3 | NUMBER(38) | YES | The value of parameter 3 of the wait event. |
| LEVEL | NUMBER(38) | YES | The level of the wait event. |
| WAIT_CLASS_ID | NUMBER(38) | YES | The ID of the class to which the wait event belongs. |
| WAIT_CLASS# | NUMBER(38) | YES | The index of the class to which the wait event belongs. |
| WAIT_CLASS | VARCHAR2(64) | YES | The name of the class to which the wait event belongs. |
| STATE | VARCHAR2(19) | YES | The status of the wait event. |
| WAIT_TIME_MICRO | NUMBER(38) | YES | The time that the wait event waits, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | NUMBER(38) | YES | The total time of all waits during execution, in microseconds. |
| TOTAL_WAITS | NUMBER(38) | YES | The total number of waits during execution. |
| RPC_COUNT | NUMBER(38) | YES | The number of RPCs sent. |
| PLAN_TYPE | NUMBER(38) | NO | The execution plan type. |
| IS_INNER_SQL | NUMBER(38) | NO | Indicates whether the request is an internal SQL request. |
| IS_EXECUTOR_RPC | NUMBER(38) | NO | Indicates whether the request is an RPC request. |
| IS_HIT_PLAN | NUMBER(38) | NO | Indicates whether the plan cache is hit. |
| REQUEST_TIME | NUMBER(38) | NO | The start time of execution, in microseconds. |
| ELAPSED_TIME | NUMBER(38) | NO | The total time consumed from receiving the request to the end of execution, in microseconds. |
| NET_TIME | NUMBER(38) | NO | The time from sending the RPC to receiving the request, in microseconds. |
| NET_WAIT_TIME | NUMBER(38) | NO | The time from receiving the request to entering the queue, in microseconds. |
| QUEUE_TIME | NUMBER(38) | NO | The time the request waits in the queue, in microseconds. |
| DECODE_TIME | NUMBER(38) | NO | The time for decoding after the request leaves the queue, in microseconds. |
| GET_PLAN_TIME | NUMBER(38) | NO | The time from the start of processing to obtaining the plan, in microseconds. |
| EXECUTE_TIME | NUMBER(38) | NO | The time consumed for plan execution, in microseconds. |
| APPLICATION_WAIT_TIME | NUMBER(38) | YES | The total time of all Application events, in microseconds. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | YES | The total time of all Concurrency events, in microseconds. |
| USER_IO_WAIT_TIME | NUMBER(38) | YES | The total time of all user_io events, in microseconds. |
| SCHEDULE_TIME | NUMBER(38) | YES | The time of all Schedule events, in microseconds. |
| ROW_CACHE_HIT | NUMBER(38) | YES | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | NUMBER(38) | YES | The number of Bloom filter cache hits. |
| BLOCK_CACHE_HIT | NUMBER(38) | YES | The number of block cache hits. |
| DISK_READS | NUMBER(38) | YES | The number of physical reads. |
| RETRY_CNT | NUMBER(38) | NO | The number of retries. |
| TABLE_SCAN | NUMBER(38) | NO | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | NUMBER(38) | NO | The consistency level. Valid values:
|
| MEMSTORE_READ_ROW_COUNT | NUMBER(38) | YES | The number of rows read from Memstore. |
| SSSTORE_READ_ROW_COUNT | NUMBER(38) | YES | The number of rows read from Ssstore. |
| DATA_BLOCK_READ_CNT | bigint(20) | YES | The number of microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | YES | The number of data microblocks that are hit in the cache. |
| INDEX_BLOCK_READ_CNT | bigint(20) | YES | The number of intermediate microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | YES | The number of intermediate microblocks that are hit in the cache. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | YES | The number of data microblocks scanned in one-sided scans. |
| BLOCKSCAN_ROW_CNT | bigint(20) | YES | The number of rows scanned in one-sided scans. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | YES | The number of rows filtered by the storage filter pushed down. |
| REQUEST_MEMORY_USED | NUMBER(38) | NO | The amount of memory consumed by the request. |
| EXPECT_WORKER_COUNT | NUMBER(38) | NO | The number of worker threads expected for the request. |
| USED_WORKER_COUNT | NUMBER(38) | NO | The number of worker threads actually used for the request. |
| SCHED_INFO | VARCHAR2(16384) | YES | The scheduling information of the request. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | This column records the client Prepare ID corresponding to the request:
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | This column records the internal (database) Prepare ID corresponding to the request:
|
| 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 | NUMBER(38) | NO | The type of the request: |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Indicates whether the Batch Multi Stmt optimization is performed. |
| OB_TRACE_INFO | VARCHAR2(4096) | NO | The trace information set by the user. |
| PLAN_HASH | NUMBER(38) | NO | The hash value of the execution plan. |
| PARAMS_VALUE | CLOB | NO | The parameter values bound to the PS. |
| RULE_NAME | VARCHAR2(256) | NO | The name of the rule.
NoteThis column is available starting with V4.1.0. |
| TX_INTERNAL_ROUTING | NUMBER | NO | Indicates whether transaction routing is enabled for the current transaction:
Note
|
| TX_STATE_VERSION | NUMBER(38) | NO | If transaction routing is enabled and the transaction state changes after executing a statement, the version number increments; otherwise, it remains unchanged.
Note
|
| FLT_TRACE_ID | VARCHAR2(1024) | NO | The trace ID for full-link tracing of the record. If it is empty, it indicates that the record is not monitored by full-link tracing. This column is a UUID, different from the trace. Its format is similar to: 000600d6-a5de-038c-6c80-df07e4e79149
NoteThis column is available starting with V4.2.1. |
| PL_TRACE_ID | VARCHAR2(128) | YES | The trace ID of the outer PL of the current SQL statement (NULL if there is no outer PL).
NoteThis column is available starting with V4.2.2. |
| PLSQL_EXEC_TIME | NUMBER(38) | NO | The time consumed by PL execution (excluding SQL execution time), in microseconds.
NoteThis column is available starting with V4.2.2. |
| FORMAT_SQL_ID | VARCHAR2(32) | NO | The MD5 value generated by the Format SQL text for this record.
NoteThis column is available starting with V4.2.3. |
| NETWORK_WAIT_TIME | NUMBER(38) | YES | The total time for all Network events, in microseconds.
NoteThis column is available starting with V4.2.3. |
| STMT_TYPE | VARCHAR2(128) | YES | The type of DML statement. The return value is as follows:
NoteThis column is available starting with V4.2.3. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | NUMBER(38) | NO | The total number of rows read from the MemStore during the entire process. This variable is displayed only in the thread that displays the query text.
NoteThis column is available starting with V4.2.3. |
| TOTAL_SSSTORE_READ_ROW_COUNT | NUMBER(38) | NO | The total number of rows read from the SSSTORE during the entire process. This variable is displayed only in the thread that displays the query text.
NoteThis column is available starting with V4.2.3. |
| PROXY_USER | VARCHAR2(128) | YES |
NoteThis column is available starting with V4.2.3. |
| SEQ_NUM | NUMBER(38) | NO | The sequence number of the statement in the transaction.
NoteThis column is available starting with V4.2.5. The default value is NULL. |
| PLSQL_COMPILE_TIME | NUMBER(38) | NO | The time taken to compile the PL statement.
NoteThis column is available starting with V4.2.5 BP2. The default value is NULL. |
| USER_CLIENT_PORT | NUMBER(38) | NO | The client port number.
NoteThis column is available starting with V4.2.5 BP2. The default value is NULL. |
| TRANS_STATUS | VARCHAR2(256) | NO | The status of the transaction. This column displays whether the transaction is explicitly started, implicitly started, or not started.
NoteThis column is available starting with V4.2.5 BP2. The default value is NULL. |
Sample query
Query the statistics such as the source and execution status of each SQL request on all OBServer nodes.
obclient [SYS]> SELECT * FROM SYS.GV$OB_SQL_AUDIT WHERE ROWNUM <= 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: 172.xx.xxx.xxx
SVR_PORT: 2882
REQUEST_ID: 110855
SQL_EXEC_ID: 886176
TRACE_ID: YB42AC1E87E4-00062A8B794ADFEA-0-0
SID: 3222235558
CLIENT_IP: 0.0.0.0
CLIENT_PORT: 0
TENANT_ID: 1004
EFFECTIVE_TENANT_ID: 1004
TENANT_NAME: sys
USER_ID: 200001
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 0.0.0.0
DB_ID: 201001
DB_NAME: oceanbase
SQL_ID: 735537F7B5DB7C4E0E946C9B26108560
QUERY_SQL: NULL
PLAN_ID: 434
AFFECTED_ROWS: 0
RETURN_ROWS: 0
PARTITION_CNT: 1
RET_CODE: 0
QC_ID: 0
DFO_ID: 0
SQC_ID: 0
WORKER_ID: 0
EVENT: NULL
P1TEXT: NULL
P1: 0
P2TEXT: NULL
P2: 0
P3TEXT: NULL
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: 1
IS_INNER_SQL: 1
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 1
REQUEST_TIME: 1735633919399301
ELAPSED_TIME: 207
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 0
DECODE_TIME: 0
GET_PLAN_TIME: 113
EXECUTE_TIME: 94
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: 3
MEMSTORE_READ_ROW_COUNT: 1
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: 2169344
EXPECTED_WORKER_COUNT: 0
USED_WORKER_COUNT: 0
SCHED_INFO: NULL
PS_CLIENT_STMT_ID: -1
PS_INNER_STMT_ID: -1
TX_ID: 0
SNAPSHOT_VERSION: 1735633919374794000
REQUEST_TYPE: 1
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: NULL
PLAN_HASH: 1170003787160719964
PARAMS_VALUE: NULL
RULE_NAME: NULL
TX_INTERNAL_ROUTING: 0
TX_STATE_VERSION: 0
FLT_TRACE_ID: NULL
PL_TRACE_ID: NULL
PLSQL_EXEC_TIME: 0
FORMAT_SQL_ID: NULL
NETWORK_WAIT_TIME: 0
STMT_TYPE: SELECT
TOTAL_MEMSTORE_READ_ROW_COUNT: 1
TOTAL_SSSTORE_READ_ROW_COUNT: 0
PROXY_USER: NULL
SEQ_NUM: 1
PLSQL_COMPILE_TIME: 0
USER_CLIENT_PORT: 0
TRANS_STATUS: Transaction not opened
1 row in set (0.016 sec)