Note
This view is available starting with V4.0.0.
Purpose
The GV$OB_SQL_AUDIT view displays the statistics of each SQL request, including the source and execution status, 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 | 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 | For V4.2.x:
|
| 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, this 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 | 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.
NoteStarting from V4.2.3, for PL requests executed by using anonymous blocks or CALL statements, this field displays the actual MD5 value. |
| QUERY_SQL | CLOB | NO | Actual SQL statement.
NoteStarting from V4.2.5 BP2, this field is visible only to the SYS tenant. |
| 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 | Execution result return code: |
| QC_ID | NUMBER(38) | NO | In parallel execution scenarios, the ID of the scheduler. |
| DFO_ID | NUMBER(38) | NO | In parallel execution scenarios, the ID of the current subplan. |
| SQC_ID | NUMBER(38) | NO | In parallel execution scenarios, the ID of the local coordinator. |
| WORKER_ID | NUMBER(38) | NO | In parallel execution scenarios, the ID of the worker thread. |
| EVENT | VARCHAR2(64) | YES | The name of the longest waiting event. |
| P1TEXT | VARCHAR2(64) | YES | The value of parameter 1 of the waiting event. |
| P1 | NUMBER(38) | YES | The value of parameter 1 of the waiting event. |
| P2TEXT | VARCHAR2(64) | YES | The value of parameter 2 of the waiting event. |
| P2 | NUMBER(38) | YES | The value of parameter 2 of the waiting event. |
| P3TEXT | VARCHAR2(64) | YES | The value of parameter 3 of the waiting event. |
| P3 | NUMBER(38) | YES | The value of parameter 3 of the waiting event. |
| LEVEL | NUMBER(38) | YES | The level of the waiting event. |
| WAIT_CLASS_ID | NUMBER(38) | YES | The class ID of the waiting event. |
| WAIT_CLASS# | NUMBER(38) | YES | The index of the class of the waiting event. |
| WAIT_CLASS | VARCHAR2(68) | YES | The name of the class of the waiting event. |
| STATE | VARCHAR2(19) | YES | The state of the waiting event. |
| WAIT_TIME_MICRO | NUMBER(38) | YES | The time that the waiting event waits, in microseconds. |
| TOTAL_WAIT_TIME_MICRO | NUMBER(38) | YES | The total time of all waits in the execution process, in microseconds. |
| TOTAL_WAITS | NUMBER(38) | YES | The total number of waits in the execution process. |
| 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 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 the execution, in microseconds. |
| ELAPSED_TIME | NUMBER(38) | NO | The total time consumed from receiving the request to the end of the 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 the request is dequeued, in microseconds. |
| GET_PLAN_TIME | NUMBER(38) | NO | The time from the start of the process to obtaining the plan, in microseconds. |
| EXECUTE_TIME | NUMBER(38) | NO | The time consumed for executing the plan, 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 times that the row cache is hit. |
| BLOOM_FILTER_CACHE_HIT | NUMBER(38) | YES | The number of times that the Bloom Filter cache is hit. |
| BLOCK_CACHE_HIT | NUMBER(38) | YES | The number of times that the block cache is hit. |
| 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 the MemStore. |
| SSSTORE_READ_ROW_COUNT | NUMBER(38) | YES | The number of rows read from the SsStore. |
| DATA_BLOCK_READ_CNT | bigint(20) | YES | The number of data microblocks accessed. |
| DATA_BLOCK_CACHE_HIT | bigint(20) | YES | The number of data microblocks that hit the cache. |
| INDEX_BLOCK_READ_CNT | bigint(20) | YES | The number of index microblocks accessed. |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | YES | The number of index microblocks that hit the cache. |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | YES | The number of data microblocks scanned in one direction. |
| BLOCKSCAN_ROW_CNT | bigint(20) | YES | The number of rows scanned in one direction. |
| 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 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) | The client Prepare ID corresponding to the request. This field records the client Prepare ID for the request.
|
| PS_INNER_STMT_ID | bigint(20) | NUMBER(38) | 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. Valid values: |
| IS_BATCHED_MULTI_STMT | NUMBER(38) | NO | Whether to optimize Batch Multi Stmt. |
| 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 statement. |
| RULE_NAME | VARCHAR2(256) | NO | The name of the rule.
NoteThis field is available starting with V4.1.0. |
| TX_INTERNAL_ROUTING | NUMBER | NO | Indicates whether the 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 execution of a statement if the transaction state changes. Otherwise, the version number remains unchanged.
Note
|
| FLT_TRACE_ID | VARCHAR2(1024) | NO | This field indicates the trace ID of the full-link trace for the record. If this field is empty, it indicates that the record is not monitored by the full-link trace. This field is a UUID, which is different from the trace ID. The format is similar to 000600d6-a5de-038c-6c80-df07e4e79149.
NoteThis field is available starting with V4.2.1. |
| PL_TRACE_ID | VARCHAR2(128) | YES | This field indicates the trace ID of the outer PL for the current SQL statement. If there is no outer PL, this field is NULL.
NoteThis field is available starting with V4.2.2. |
| PLSQL_EXEC_TIME | NUMBER(38) | NO | This field indicates the execution time of the PL, excluding the SQL execution time. The unit is microseconds.
NoteThis field is available starting with V4.2.2. |
| FORMAT_SQL_ID | VARCHAR2(32) | NO | This field indicates the MD5 value generated by the Format SQL text for the record.
NoteThis field is available starting with V4.2.3. |
| NETWORK_WAIT_TIME | NUMBER(38) | YES | This field indicates the total time of all network-related events. The unit is microseconds.
NoteThis field is available starting with V4.2.3. |
| STMT_TYPE | VARCHAR2(128) | YES | This field indicates the type of the DML statement. The value is returned as follows:
NoteThis field is available starting with V4.2.3. |
| TOTAL_MEMSTORE_READ_ROW_COUNT | NUMBER(38) | NO | This field indicates the total number of rows read from the MemStore during the entire operation. This variable is displayed only in the thread that shows the query text.
NoteThis field is available starting with V4.2.3. |
| TOTAL_SSSTORE_READ_ROW_COUNT | NUMBER(38) | NO | This field indicates the total number of rows read from the SSSTORE during the entire operation. This variable is displayed only in the thread that shows the query text.
NoteThis field is available starting with V4.2.3. |
| PROXY_USER | VARCHAR2(128) | YES |
NoteThis field is available starting with V4.2.3. |
| SEQ_NUM | NUMBER(38) | NO | This field indicates the sequence number of the statement in the transaction.
NoteThis field is available starting with V4.2.5. The default value is NULL. |
| PLSQL_COMPILE_TIME | NUMBER(38) | NO | This field indicates the compilation time of the PL.
NoteThis field is available starting with V4.2.5 BP2. The default value is NULL. |
| USER_CLIENT_PORT | NUMBER(38) | NO | This field indicates the client port number.
NoteThis field is available starting with V4.2.5 BP2. The default value is NULL. |
| TRANS_STATUS | VARCHAR2(256) | NO | This field indicates whether a transaction is started, whether an implicit transaction is started, or whether no transaction is started.
NoteThis field is available starting with V4.2.5 BP2. The default value is NULL. |
Sample query
Query the source, execution status, and other statistical information 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)