Note
This view is available starting with V4.0.0.
Purpose
The GV$OB_SQL_AUDIT view displays the source and execution status of each SQL request on all OBServer nodes. This view is tenant-separated. Only the system tenant can query the view. Other tenants cannot query the view across tenants.
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 | For V4.2.x:
|
| CLIENT_IP | varchar(46) | NO | |
| CLIENT_PORT | bigint(20) | NO | Port number of the client that sends the request. |
| TENANT_ID | bigint(20) | NO | Tenant ID.
NoteStarting from V4.2.3, this field indicates the same value as the |
| TENANT_NAME | varchar(64) | NO | Tenant name. |
| EFFECTIVE_TENANT_ID | bigint(20) | NO | Tenant ID. |
| USER_ID | bigint(20) | NO | User ID of the user who sends the request. |
| USER_NAME | varchar(64) | NO | Username of the user who sends the request. |
| USER_GROUP | bigint(20) | YES | ID of the resource group 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.
NoteStarting from V4.2.3, for PL requests executed by using anonymous blocks and for PL requests executed by using the CALL statement, this field displays the actual MD5 value. |
| QUERY_SQL | longtext | NO | The actual SQL statement.
NoteThis column is available starting with V4.2.5 BP2. Only the SYS tenant can view this column. |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. |
| AFFECTED_ROWS | bigint(20) | NO | The number of affected rows. |
| RETURN_ROWS | bigint(20) | NO | The number of returned rows. |
| PARTITION_CNT | bigint(20) | NO | The number of partitions involved in the request. |
| RET_CODE | bigint(20) | NO | The return code of the execution result: |
| QC_ID | bigint(20) unsigned | NO | The scheduler ID in parallel execution scenarios. |
| DFO_ID | bigint(20) | NO | The ID of the current subplan in parallel execution scenarios. |
| SQC_ID | bigint(20) | NO | The ID of the local coordinator in parallel execution scenarios. |
| WORKER_ID | bigint(20) | NO | The ID of the worker thread in parallel execution scenarios. |
| EVENT | varchar(64) | NO | The name of the longest waiting event. |
| P1TEXT | varchar(64) | NO | Parameter 1 of the waiting event. |
| P1 | bigint(20) unsigned | NO | The value of parameter 1 of the waiting event. |
| P2TEXT | varchar(64) | NO | Parameter 2 of the waiting event. |
| P2 | bigint(20) unsigned | NO | The value of parameter 2 of the waiting event. |
| P3TEXT | varchar(64) | NO | Parameter 3 of the waiting event. |
| P3 | bigint(20) unsigned | NO | The value of parameter 3 of the waiting event. |
| LEVEL | bigint(20) | NO | The level of the waiting event. |
| WAIT_CLASS_ID | bigint(20) | NO | The ID of the class to which the waiting event belongs. |
| WAIT_CLASS# | bigint(20) | NO | The index of the class to which the waiting event belongs. |
| WAIT_CLASS | varchar(64) | NO | The name of the class to which the waiting event belongs. |
| Column | Type | Nullable | Description |
| WAIT_TIME_MICRO | bigint(20) | NO | The time that the wait event waits for, 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 type of the execution plan. Valid values: |
| 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 is hit. |
| REQUEST_TIME | bigint(20) | NO | The start time of execution, in microseconds. |
| ELAPSED_TIME | bigint(20) | NO | The total time from when the request is received to when execution ends, in microseconds. |
| NET_TIME | bigint(20) | NO | The time from when the RPC is sent to when the request is received, in microseconds. |
| NET_WAIT_TIME | bigint(20) | NO | The time from when the request is received to when it enters 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 leaves the queue, in microseconds. |
| GET_PLAN_TIME | bigint(20) | NO | The time from when execution starts to when the plan is obtained, 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_io 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 | Bloom Filter cache hit count |
| BLOCK_CACHE_HIT | bigint(20) | NO | Block cache hit count |
| DISK_READS | bigint(20) | NO | Physical read count |
| RETRY_CNT | bigint(20) | NO | Retry count |
| TABLE_SCAN | tinyint(4) | NO | Indicates whether the request contains a full table scan. |
| CONSISTENCY_LEVEL | bigint(20) | NO | Consistency level. Valid values:
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | Number of rows read from the MemStore. |
| SSSTORE_READ_ROW_COUNT | bigint(20) | NO | Number of rows read from the SSStore. |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | Number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | Number of data microblocks cached. |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | Number of intermediate microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | Number of intermediate microblocks cached. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | Number of data microblocks scanned in one direction. |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | Number of data rows scanned in one direction. |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | Number of rows filtered by the storage filter pushed down. |
| REQUEST_MEMORY_USED | bigint(20) | NO | Amount of memory consumed by the request. |
| EXPECTED_WORKER_COUNT | bigint(20) | NO | Expected number of worker threads for the request. |
| USED_WORKER_COUNT | bigint(20) | NO | Actual number of worker threads used for the request. |
| SCHED_INFO | varchar(16384) | YES | Scheduling information for the request. |
| FUSE_ROW_CACHE_HIT | bigint(20) | NO | This field is not supported. The field is set to NULL by default. |
| PS_CLIENT_STMT_ID | bigint(20) | NUMBER(38) | The ID of the client Prepare ID corresponding to the request.
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | This field records the internal (database internal) 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 | bigint(20) | NO | The type of the request: |
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether Batch Multi Stmt optimization is performed. |
| 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 spent waiting for a lock when reading data, in microseconds. |
| PARAMS_VALUE | longtext | NO | The parameter values bound to the PS. |
| RULE_NAME | varchar(256) | NO | The name of the rule.
NoteThis field is available starting with V4.1.0. |
| PARTITION_HIT | tinyint(4) | NO |
NoteThis field is available starting with V4.1.0. |
| TX_INTERNAL_ROUTING | bigint(20) | NO | Indicates whether transaction routing is enabled for the current transaction:
Note
|
| TX_STATE_VERSION | bigint(20) unsigned | NO | If transaction routing is enabled, the version number increments when the transaction state changes after executing the statement; otherwise, the version number remains unchanged.
Note
|
| FLT_TRACE_ID | varchar(1024) | NO | The trace_id for full-link tracing of this record. If it is empty, it indicates that the record is not monitored by full-link tracing. This field is a UUID, which is different from the trace. Its format is similar to: 000600d6-a5de-038c-6c80-df07e4e79149
NoteThis field is available starting with V4.2.1. |
| PL_TRACE_ID | varchar(128) | NO | The trace ID of the outer PL of the current SQL statement (NULL if there is no outer PL).
NoteThis field is available starting with V4.2.2. |
| PLSQL_EXEC_TIME | bigint(20) | NO | The time spent executing the PL, excluding the time spent executing the SQL statement, in microseconds.
NoteThis field is available starting with V4.2.2. |
| FORMAT_SQL_ID | varchar(32) | NO | The MD5 value generated by the Format SQL text for this record.
NoteThis field is available starting with V4.2.3. |
| NETWORK_WAIT_TIME | bigint(20) unsigned | YES | The total time of all Network events, in microseconds.
NoteThis field is available starting with V4.2.3. |
| STMT_TYPE | varchar(128) | YES | Type |
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | The total number of rows read from the MemStore during the entire process. (This variable is only visible in the threads that display query text.)
NoteThis column is available starting with V4.2.3. |
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NO | Total number of rows read from the SSSTORE. (This variable is displayed only in threads that display the query text.)
NoteThis view is available starting with V4.2.3. |
| PROXY_USER | varchar(128) | YES |
NoteThis column is available starting with V4.2.3. |
| SEQ_NUM | bigint(20) | NO | The sequence number of the statement within the transaction.
NoteThis column is available starting with V4.2.5. The column defaults to NULL. |
| PLSQL_COMPILE_TIME | bigint(20) | NO | The PL/SQL compile time.
NoteThis column is available starting with V4.2.5 BP2. The default value of this column is NULL. |
| USER_CLIENT_PORT | bigint(20) | NO | The port number of the client.
NoteThis column is available starting with V4.2.5 BP2. The default value is NULL. |
| TRANS_STATUS | varchar(256) | NO | This view displays whether a transaction is explicitly opened, implicitly opened, or not opened.
NoteThis view is available starting with V4.2.5 BP2. The default value of this column is NULL. |
Sample query
Query the statistics of each SQL request, such as the source and execution status, on all OBServer nodes.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQL_AUDIT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: 172.30.xxx.xxx
SVR_PORT: 2882
REQUEST_ID: 47000
SQL_EXEC_ID: 732749
TRACE_ID: YB42AC1E87E3-00062A8B80571A88-0-0
SID: 1
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: 23
USER_CLIENT_IP: 0.0.0.0
DB_ID: 201001
DB_NAME: oceanbase
SQL_ID: A983677D94124CE9D95B9830B6DA55F9
QUERY_SQL:
PLAN_ID: 308
AFFECTED_ROWS: 0
RETURN_ROWS: 1
PARTITION_CNT: 1
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: 1688
TOTAL_WAITS: 2
RPC_COUNT: 2
PLAN_TYPE: 2
IS_INNER_SQL: 1
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 1
REQUEST_TIME: 1735635045195774
ELAPSED_TIME: 1819
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 0
DECODE_TIME: 0
GET_PLAN_TIME: 37
EXECUTE_TIME: 1782
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: 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: 2234752
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: 3738
SNAPSHOT_VERSION: 1735635045195353000
REQUEST_TYPE: 1
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: NULL
PLAN_HASH: 12693347183203454687
LOCK_FOR_READ_TIME: 0
PARAMS_VALUE:
RULE_NAME:
PARTITION_HIT: 0
TX_INTERNAL_ROUTING: 0
TX_STATE_VERSION: 0
FLT_TRACE_ID: 00062a8b-6e46-9e97-401a-d83b36f4555c
PL_TRACE_ID: NULL
PLSQL_EXEC_TIME: 0
FORMAT_SQL_ID:
NETWORK_WAIT_TIME: 0
STMT_TYPE: SELECT
TOTAL_MEMSTORE_READ_ROW_COUNT: 0
TOTAL_SSSTORE_READ_ROW_COUNT: 0
PROXY_USER:
SEQ_NUM: 124561
PLSQL_COMPILE_TIME: 0
USER_CLIENT_PORT: 0
TRANS_STATUS: Enable committable transaction
1 row in set (0.013 sec)