Note
This view is available starting with V4.0.0.
Overview
The V$OB_SQL_AUDIT view displays statistics such as the source and execution status of each SQL request on the current OBServer node. This view is partitioned by tenant. Except for the system tenant, other tenants cannot query across tenants.
Columns
Field |
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 | ID of the current execution |
| TRACE_ID | VARCHAR2(128) | NO | The trace ID of this statement |
| SID | NUMBER(38) | NO | For V4.2.x versions:
|
| 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
NoteStarting from V4.2.3, the meaning of this field has changed to be the same as that of 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 requester |
| USER_NAME | VARCHAR2(64) | NO | Username of the user who sent the request |
| USER_GROUP | NUMBER(38) | NO | Resource Group ID |
| USER_CLIENT_IP | VARCHAR2(32) | NO | IP address of the client that sent the request |
| DB_ID | NUMBER(38) | NO | Database ID |
| DB_NAME | VARCHAR2(128) | NO | Database Name |
| SQL_ID | VARCHAR2(32) | NO | The ID of the SQL statement.
NoteStarting from V4.2.3, for PL requests executed by a CALL statement or through an anonymous block, this field displays the actual generated MD5 code. |
| QUERY_SQL | CLOB | NO | Actual SQL statements |
| PLAN_ID | NUMBER(38) | NO | Execution Plan ID |
| AFFECTED_ROWS | NUMBER(38) | NO | Affected Rows |
| RETURN_ROWS | NUMBER(38) | NO | Rows returned |
| PARTITION_CNT | NUMBER(38) | NO | Number of partitions involved in the request |
| RET_CODE | NUMBER(38) | NO | Execution result return codes:
|
| QC_ID | NUMBER(38) | NO | Scheduler ID in parallel execution scenarios |
| DFO_ID | NUMBER(38) | NO | ID of the current subplan in parallel execution scenarios |
| SQC_ID | NUMBER(38) | NO | Local coordinator ID in parallel execution scenarios |
| WORKER_ID | NUMBER(38) | NO | Worker Thread ID in Parallel Execution Scenarios |
| EVENT | VARCHAR2(64) | NO | Longest Waiting Event Name |
| P1TEXT | VARCHAR2(64) | NO | Wait for Event Parameter 1 |
| P1 | NUMBER(38) | NO | Value of Parameter 1 for the Wait Event |
| P2TEXT | VARCHAR2(64) | NO | Wait for Event Parameter 2 |
| P2 | NUMBER(38) | NO | Value of Parameter 2 for the Wait Event |
| P3TEXT | VARCHAR2(64) | NO | Wait for Event Parameter 3 |
| P3 | NUMBER(38) | NO | Value of Parameter 3 for the Wait Event |
| LEVEL | NUMBER(38) | NO | Level of the Waiting Event |
| WAIT_CLASS_ID | NUMBER(38) | NO | Class ID of the waiting event |
| WAIT_CLASS# | NUMBER(38) | NO | Index of the class to which the event belongs |
| WAIT_CLASS | VARCHAR2(64) | NO | Class name of the event to be waited for |
| STATE | VARCHAR2(19) | NO | Status of the waiting event |
| WAIT_TIME_MICRO | NUMBER(38) | NO | The wait time for this event, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | NUMBER(38) | NO | Total wait time for the entire execution process, in microseconds |
| TOTAL_WAITS | NUMBER(38) | NO | Total Number of Waitings During Execution |
| RPC_COUNT | NUMBER(38) | NO | Number of RPCs Sent |
| PLAN_TYPE | NUMBER(38) | NO | Execution plan type:
|
| IS_INNER_SQL | NUMBER(38) | NO | Whether it is an internal SQL request |
| IS_EXECUTOR_RPC | NUMBER(38) | NO | Whether the current request is an RPC request |
| IS_HIT_PLAN | NUMBER(38) | NO | Whether the plan cache was hit |
| REQUEST_TIME | NUMBER(38) | NO | Start time point, in microseconds |
| ELAPSED_TIME | NUMBER(38) | NO | Total time consumed from request reception to execution completion, in microseconds |
| NET_TIME | NUMBER(38) | NO | Time taken from sending an RPC to receiving a response, in microseconds |
| NET_WAIT_TIME | NUMBER(38) | NO | Time elapsed from when a request is received until it enters the queue, in microseconds. |
| QUEUE_TIME | NUMBER(38) | NO | Waiting Time of Request in Queue, Unit: microseconds |
| DECODE_TIME | NUMBER(38) | NO | Time taken for decode after dequeuing, in microseconds |
| GET_PLAN_TIME | NUMBER(38) | NO | Time taken from process start to plan acquisition, in microseconds |
| EXECUTE_TIME | NUMBER(38) | NO | Time consumed in plan execution, in microseconds |
| APPLICATION_WAIT_TIME | NUMBER(38) | NO | Total Time of All Application-Type Events, Unit: microseconds |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | Total time for all concurrency events, in microseconds |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | Total Time of All User-I/O Events, Unit: microseconds |
| SCHEDULE_TIME | NUMBER(38) | NO | Time of all Schedule-type events, in microseconds |
| ROW_CACHE_HIT | NUMBER(38) | NO | Row Cache Hits |
| BLOOM_FILTER_CACHE_HIT | NUMBER(38) | NO | Number of Bloom Filter Cache Hits |
| BLOCK_CACHE_HIT | NUMBER(38) | NO | Block Cache Hits |
| DISK_READS | NUMBER(38) | NO | Physical Read Count |
| RETRY_CNT | NUMBER(38) | NO | Retry Count |
| TABLE_SCAN | NUMBER(38) | NO | Determine whether the request involves a full table scan |
| CONSISTENCY_LEVEL | NUMBER(38) | NO | Consistency level. Valid values:
|
| MEMSTORE_READ_ROW_COUNT | NUMBER(38) | NO | Number of Rows Read from MemStore |
| SSSTORE_READ_ROW_COUNT | NUMBER(38) | NO | Number of Consecutive Read Rows in Ssstore |
| REQUEST_MEMORY_USED | NUMBER(38) | NO | Memory Consumed by This Request |
| EXPECT_WORKER_COUNT | NUMBER(38) | NO | Number of Worker Threads Expected |
| USED_WORKER_COUNT | NUMBER(38) | NO | Actual Number of Worker Threads Used |
| SCHED_INFO | VARCHAR2(16384) | NO | Request Scheduling Information |
| PS_STMT_ID | NUMBER(38) | NO | Prepare ID of the request:
|
| REQUEST_TYPE | NUMBER(38) | NO | Type of the request. Valid values:
|
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Whether to Optimize Batch MultiStmt |
| OB_TRACE_INFO | VARCHAR2(4096) | NO | User-configured trace information |
| PLAN_HASH | NUMBER(38) | NO | Hash value of the execution plan |
| PARAMS_VALUE | CLOB | NO | Parameter Value Bound to PS |
| RULE_NAME | VARCHAR2(256) | NO | Rule Name
NoteThis field 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 | When transaction routing is enabled, if the transaction status changes after a statement is executed, the version number increments; if no change occurs, the version number remains unchanged.
Note
|
| FLT_TRACE_ID | VARCHAR2(1024) | NO | Indicates the trace ID for end-to-end tracing of this record. If empty, it means the record is not monitored by end-to-end tracing. This field is a UUID, which is different from a trace. Its format is similar to:000600d6-a5de-038c-6c80-df07e4e79149
NoteThis field is available starting with V4.2.1. |
| PL_TRACE_ID | VARCHAR2(128) | NO | Trace ID of the outermost PL in the current SQL statement. If no outermost PL exists, this value is NULL.
NoteThis field is available starting with V4.2.2. |
| PLSQL_EXEC_TIME | NUMBER(38) | NO | Execution time of PL statements (excluding SQL execution time), in microseconds
NoteThis field is available starting with V4.2.2. |
| FORMAT_SQL_ID | VARCHAR2(32) | NO | The MD5 value generated by formatting the SQL text.
NoteThis field is available starting with V4.2.3. |
| NETWORK_WAIT_TIME | NUMBER(38) | YES | Total time for all network events, in microseconds
NoteThis field is available starting with V4.2.3. |
| STMT_TYPE | VARCHAR2(128) | YES | The DML type is returned as needed:
NoteThis field is available starting with V4.2.3. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | NUMBER(38) | NO | Total number of rows read from the MemStore during the entire process (this variable is displayed only in the thread that executes the query_text).
NoteThis field is available starting with V4.2.3. |
| TOTAL_SSSTORE_READ_ROW_COUNT | NUMBER(38) | NO | Total number of rows read from SSSTORE during the entire process (this variable is displayed only in the thread that executes the query_text).
NoteThis field is available starting with V4.2.3. |
| PROXY_USER | VARCHAR2(128) | NO |
NoteThis field is available starting with V4.2.3. |
| SEQ_NUM | bigint(20) | NUMBER(38) | The sequence number of the statement in the transaction.
NoteThis field is available starting with V4.2.5 and defaults to NULL. |
| PLSQL_COMPILE_TIME | bigint(20) | NUMBER(38) | PL compilation time.
NoteThis field is available starting with V4.2.5 BP2 and defaults to NULL. |
| USER_CLIENT_PORT | bigint(20) | NUMBER(38) | Displays the client port number.
NoteThis field is available starting with V4.2.5 BP2. |
| TRANS_STATUS | varchar(256) | VARCHAR2(256) | Whether a transaction is explicitly started, implicitly started, or not started at all.
NoteThis field is available starting with V4.2.5 BP2. |
Sample query
Query the statistics of each SQL request on the current OBServer node, such as the source and execution status.
obclient [SYS]> SELECT * FROM SYS.V$OB_SQL_AUDIT WHERE ROWNUM <= 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: 172.xx.xx.xx
SVR_PORT: 2882
REQUEST_ID: 15859712
SQL_EXEC_ID: 234104855
TRACE_ID: YB4XXXXXXXXX-000XXXXXXXXXXXX-0-0
SID: 3221663218
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: 9CA2F8D24467EB1A28CA50EE09743A86
QUERY_SQL: SELECT * FROM __all_acquired_snapshot WHERE tenant_id = '0'
PLAN_ID: 289
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: 1722849834979839
ELAPSED_TIME: 131
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 0
DECODE_TIME: 0
GET_PLAN_TIME: 81
EXECUTE_TIME: 50
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: 82936
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: 1722849834663851000
REQUEST_TYPE: 1
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: NULL
PLAN_HASH: 15402238335871689439
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: 0
TOTAL_SSSTORE_READ_ROW_COUNT: 0
PROXY_USER: NULL
1 row in set