Note
This view is available starting with V4.0.0.
Purpose
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 tenant-separated. You can query only the views of the current tenant.
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 indicates the effective tenant ID, starting from OceanBase Database V4.2.3. |
| 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 who sends the request |
| USER_NAME | VARCHAR2(64) | NO | User name of the user who sends the request |
| USER_GROUP | NUMBER(38) | NO | 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
NoteStarting from OceanBase Database V4.2.3, this field indicates the MD5 hash of the actual SQL statement for PL requests executed by using anonymous blocks or CALL statements. |
| QUERY_SQL | CLOB | NO | Actual SQL statement |
| 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 | ID of the scheduler in parallel execution scenarios |
| DFO_ID | NUMBER(38) | NO | ID of the subplan being executed in parallel execution scenarios |
| SQC_ID | NUMBER(38) | NO | ID of the local coordinator in parallel execution scenarios |
| WORKER_ID | NUMBER(38) | NO | ID of the worker thread in parallel execution scenarios |
| EVENT | VARCHAR2(64) | NO | Name of the longest waiting event |
| P1TEXT | VARCHAR2(64) | NO | Parameter 1 of the waiting event |
| P1 | NUMBER(38) | NO | Value of parameter 1 of the waiting event |
| P2TEXT | VARCHAR2(64) | NO | Parameter 2 of the waiting event |
| P2 | NUMBER(38) | NO | The value of the second parameter of the wait event. |
| P3TEXT | VARCHAR2(64) | NO | The third parameter of the wait event. |
| P3 | NUMBER(38) | NO | The value of the third parameter of the wait event. |
| LEVEL | NUMBER(38) | NO | The level of the wait event. |
| WAIT_CLASS_ID | NUMBER(38) | NO | The class ID of the wait event. |
| WAIT_CLASS# | NUMBER(38) | NO | The index of the class to which the wait event belongs. |
| WAIT_CLASS | VARCHAR2(64) | NO | The class to which the wait event belongs. |
| STATE | VARCHAR2(19) | NO | The state of the wait event. |
| WAIT_TIME_MICRO | NUMBER(38) | NO | The time for which the wait event is waiting, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | NUMBER(38) | NO | The total time for all waits during execution, in microseconds. |
| TOTAL_WAITS | NUMBER(38) | NO | The total number of waits during execution. |
| RPC_COUNT | NUMBER(38) | NO | 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 current 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 waiting time of the request in the queue, in microseconds. |
| DECODE_TIME | NUMBER(38) | NO | The time for decoding after dequeuing, 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) | NO | The total time for all application-related events, in microseconds. |
| CONCURRENCY_WAIT_TIME | NUMBER(38) | NO | The total time for all concurrency-related events, in microseconds. |
| USER_IO_WAIT_TIME | NUMBER(38) | NO | The total time for all user I/O-related events, in microseconds. |
| SCHEDULE_TIME | NUMBER(38) | NO | The time for all schedule-related events, in microseconds. |
| ROW_CACHE_HIT | NUMBER(38) | NO | The number of row cache hits. |
| BLOOM_FILTER_CACHE_HIT | NUMBER(38) | NO | The number of Bloom filter cache hits. |
| BLOCK_CACHE_HIT | NUMBER(38) | NO | The number of block cache hits. |
| DISK_READS | NUMBER(38) | NO | 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) | NO | The number of rows read from the MemStore. |
| SSSTORE_READ_ROW_COUNT | NUMBER(38) | NO | The number of rows read from the ssstore. |
| REQUEST_MEMORY_USED | NUMBER(38) | NO | The amount of memory consumed by the request. |
| EXPECT_WORKER_COUNT | NUMBER(38) | NO | The expected number of worker threads for the request. |
| USED_WORKER_COUNT | NUMBER(38) | NO | The actual number of worker threads used by the request. |
| SCHED_INFO | VARCHAR2(16384) | NO | The scheduling information for the request. |
| PS_STMT_ID | NUMBER(38) | NO | The Prepare ID corresponding to the request: -1: indicates that the SQL statement does not use the PS protocol.-1: indicates that the SQL statement uses the PS protocol, and the value is the unique identifier returned by the PS protocol for the statement. |
| TRANSACTION_HASH | NUMBER(38) | NO | The hash value of the transaction corresponding to the request. |
| 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 by 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 | When transaction routing is enabled, the version number increments after the transaction state changes following the execution of a statement. If the transaction state does not change, the version number remains unchanged.
Note
|
| FLT_TRACE_ID | VARCHAR2(1024) | NO | The trace ID for the full-link tracing of this record. If this field 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 column is available starting with V4.2.1. |
| PL_TRACE_ID | VARCHAR2(128) | NO | The trace ID of the outer PL for the current SQL statement. If there is no outer PL, this value is NULL.
NoteThis column is available starting with V4.2.2. |
| PLSQL_EXEC_TIME | NUMBER(38) | NO | The time consumed by the 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 of all Network events, in microseconds.
NoteThis column is available starting with V4.2.3. |
| STMT_TYPE | VARCHAR2(128) | YES | The DML type is returned as needed:
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 shows 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 shows the query text.
NoteThis column is available starting with V4.2.3. |
| PROXY_USER | VARCHAR2(128) | NO |
NoteThis column is available starting with V4.2.3. |
| SEQ_NUM | bigint(20) | NUMBER(38) | The sequence number of the statement in the transaction.
NoteThis column is available starting with V4.2.5. By default, this column is NULL. |
| PLSQL_COMPILE_TIME | bigint(20) | NUMBER(38) | The PL/SQL compilation time.
NoteThis column is available starting with V4.2.5 BP2. By default, this column is NULL. |
| USER_CLIENT_PORT | bigint(20) | NUMBER(38) | The client port number.
NoteThis column is available starting with V4.2.5 BP2. |
| TRANS_STATUS | varchar(256) | VARCHAR2(256) | The transaction status. This column indicates whether the transaction is explicitly started, implicitly started, or not started.
NoteThis column is available starting with V4.2.5 BP2. |
Sample query
Query the statistics of each SQL request on the current OBServer node, including 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