Note
This view was introduced in OceanBase Database V4.0.0.
Purpose
The GV$OB_SQL_AUDIT view displays the statistics about each SQL request on all OBServer nodes, such as the source and execution status. This view is tenant-specific, and you can query this view of other tenants only from the sys tenant.
Columns
Column |
Type |
Nullable? |
Description |
|
|---|---|---|---|---|
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node. | |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node. | |
| REQUEST_ID | bigint(20) | NO | The ID of the request. | |
| SQL_EXEC_ID | bigint(20) | NO | The ID of the current execution. | |
| TRACE_ID | varchar(128) | NO | The trace ID of the statement. | |
| SID | bigint(20) unsigned | NO | The ID of the session connection. | |
| CLIENT_IP | varchar(46) | NO | ||
| CLIENT_PORT | bigint(20) | NO | The port number of the client that sent the request. | |
| TENANT_ID | bigint(20) | NO | The ID of the tenant that sent the request. | |
| TENANT_NAME | varchar(64) | NO | The name of the tenant that sent the request. | |
| EFFECTIVE_TENANT_ID | bigint(20) | NO | The ID of the tenant. | |
| USER_ID | bigint(20) | NO | The ID of the user that sent the request. | |
| USER_NAME | varchar(64) | NO | The name of the user that sent the request. | |
| USER_GROUP | bigint(20) | YES | The ID of the resource group to which the user belongs. | |
| USER_CLIENT_IP | varchar(46) | NO | The IP address of the client that sent the request. | |
| DB_ID | bigint(20) unsigned | NO | The ID of the database. | |
| DB_NAME | varchar(128) | NO | The name of the database. | |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement.
Note
|
|
| QUERY_SQL | longtext | NO | The actual SQL statement. | |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. | |
| AFFECTED_ROWS | bigint(20) | NO | The number of rows affected. | |
| RETURN_ROWS | bigint(20) | NO | The number of rows returned. | |
| PARTITION_CNT | bigint(20) | NO | The number of partitions scanned by the request. | |
| RET_CODE | bigint(20) | NO | The return code of the execution. Valid values: 0: indicates that the statement is successfully executed without errors. |
|
| QC_ID | bigint(20) unsigned | NO | The ID of the scheduler in the parallel execution scenario. | |
| DFO_ID | bigint(20) | NO | The ID of the sub-plan being executed in the parallel execution scenario. | |
| SQC_ID | bigint(20) | NO | The ID of the local coordinator in the parallel execution scenario. | |
| WORKER_ID | bigint(20) | NO | The ID of the worker thread in the parallel execution scenario. | |
| EVENT | varchar(64) | NO | The name of the wait event with the longest wait time. | |
| P1TEXT | varchar(64) | NO | The first parameter of the wait event. | |
| P1 | bigint(20) unsigned | NO | The value of the first parameter of the wait event. | |
| P2TEXT | varchar(64) | NO | The second parameter of the wait event. | |
| P2 | bigint(20) unsigned | NO | The value of the second parameter of the wait event. | |
| P3TEXT | varchar(64) | NO | The third parameter of the wait event. | |
| P3 | bigint(20) unsigned | NO | The value of the third parameter of the wait event. | |
| LEVEL | bigint(20) | NO | The level of the wait event. | |
| WAIT_CLASS_ID | bigint(20) | NO | The ID of the class to which the wait event belongs. | |
| WAIT_CLASS# | bigint(20) | NO | The subscript of the class to which the wait event belongs. | |
| WAIT_CLASS | varchar(64) | NO | The name of the class to which the wait event belongs. | |
| STATE | varchar(19) | NO | The status of the wait event. | |
| WAIT_TIME_MICRO | bigint(20) | NO | The amount of wait time of the wait event, in microseconds. | |
| TOTAL_WAIT_TIME_MICRO | bigint(20) | NO | The total amount of wait time during execution, in microseconds. | |
| TOTAL_WAITS | bigint(20) | NO | The total number of waits during the execution. | |
| RPC_COUNT | bigint(20) | NO | The number of remote procedure calls (RPCs) sent. | |
| PLAN_TYPE | bigint(20) | NO | The type of the execution plan. Valid values: localremotedistribute |
|
| IS_INNER_SQL | tinyint(4) | NO | Indicates whether the request is an internal SQL request. | |
| IS_EXECUTOR_RPC | tinyint(4) | NO | Indicates whether the current request is an RPC request. | |
| IS_HIT_PLAN | tinyint(4) | NO | Indicates whether the plan cache is hit. | |
| REQUEST_TIME | bigint(20) | NO | The time when the execution starts, in microseconds. | |
| ELAPSED_TIME | bigint(20) | NO | The amount of time elapsed from when the request was received to when the execution of the request ended, in microseconds. | |
| NET_TIME | bigint(20) | NO | The amount of time consumed from RPC sending to request reception, in microseconds. | |
| NET_WAIT_TIME | bigint(20) | NO | The amount of time consumed from the reception of the request to the start of queuing, in microseconds. | |
| QUEUE_TIME | bigint(20) | NO | The amount of wait time of the request in the queue, in microseconds. | |
| DECODE_TIME | bigint(20) | NO | The amount of decoding time of the request after it left the queue, in microseconds. | |
| GET_PLAN_TIME | bigint(20) | NO | The amount of time elapsed from when the processing started to when the plan was obtained, in microseconds. | |
| EXECUTE_TIME | bigint(20) | NO | The amount of time consumed for plan execution, in microseconds. | |
| APPLICATION_WAIT_TIME | bigint(20) unsigned | NO | The total amount of wait time of Application wait events, in microseconds. | |
| CONCURRENCY_WAIT_TIME | bigint(20) unsigned | NO | The total amount of wait time of Concurrency wait events, in microseconds. | |
| USER_IO_WAIT_TIME | bigint(20) unsigned | NO | The total amount of wait time of User I/O wait events, in microseconds. | |
| SCHEDULE_TIME | bigint(20) unsigned | NO | The total amount of wait time of Schedule wait events, in microseconds. | |
| ROW_CACHE_HIT | bigint(20) | NO | The number of row cache hits. | |
| BLOOM_FILTER_CACHE_HIT | bigint(20) | NO | The number of Bloom filter cache hits. | |
| BLOCK_CACHE_HIT | bigint(20) | NO | The number of block cache hits. | |
| DISK_READS | bigint(20) | NO | The number of physical reads. | |
| RETRY_CNT | bigint(20) | NO | The number of retries. | |
| TABLE_SCAN | tinyint(4) | NO | Indicates whether the request contains a full table scan. | |
| CONSISTENCY_LEVEL | bigint(20) | NO | The consistency level. Valid values:
|
|
| MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | The number of rows read in the MemStore. | |
| SSSTORE_READ_ROW_COUNT | bigint(20) | NO | The number of rows read in the SSStore. | |
| DATA_BLOCK_READ_CNT | bigint(20) | NO | The number of data microblocks accessed. | |
| DATA_BLOCK_CACHE_HIT | bigint(20) | NO | The number of data microblock cache hits. | |
| INDEX_BLOCK_READ_CNT | bigint(20) | NO | The number of intermediate-layer microblocks accessed. | |
| INDEX_BLOCK_CACHE_HIT | bigint(20) | NO | The number of intermediate-layer microblock cache hits. | |
| BLOCKSCAN_BLOCK_CNT | bigint(20) | NO | The number of data microblocks scanned during a unilateral scan. | |
| BLOCKSCAN_ROW_CNT | bigint(20) | NO | The number of data rows scanned during a unilateral scan. | |
| PUSHDOWN_STORAGE_FILTER_ROW_CNT | bigint(20) | NO | The number of rows that remain after the filter condition is pushed down to and applied in the storage layer. | |
| REQUEST_MEMORY_USED | bigint(20) | NO | The memory consumed by the request. | |
| EXPECTED_WORKER_COUNT | bigint(20) | NO | The number of worker threads expected by the request. | |
| USED_WORKER_COUNT | bigint(20) | NO | The number of worker threads used by the request. | |
| SCHED_INFO | varchar(16384) | YES | The scheduling information about the request. | |
| FUSE_ROW_CACHE_HIT | bigint(20) | NO | At present, this column is not supported and its value is NULL by default. |
|
| PS_CLIENT_STMT_ID | bigint(20) | NO | The ID of the prepared statement on the client corresponding to the request. Valid values:
|
|
| PS_INNER_STMT_ID | bigint(20) | NO | The ID of the prepared statement in the database corresponding to the request. Valid values:
|
|
| TX_ID | bigint(20) | NO | The ID of the transaction corresponding to the request. | |
| SNAPSHOT_VERSION | bigint(20) | NO | The snapshot version used by the SQL statement. | |
| REQUEST_TYPE | bigint(20) | NO | The type of the request. Valid values: 0: indicates an invalid request.1: indicates an internal request.2: indicates a local request such as a local plan.3: indicates a remote request.4: indicates a distributed request.5: indicates an SQL PREPARE request.6: indicates an SQL Execute Stmt request. |
|
| IS_BATCHED_MULTI_STMT | tinyint(4) | NO | Indicates whether optimization has been performed for batch execution of multiple statements. | |
| OB_TRACE_INFO | varchar(4096) | NO | The trace information configured by the user. | |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the execution plan. | |
| LOCK_FOR_READ_TIME | bigint(20) | NO | The amount of time spent on waiting for locked data to be unlocked before it can be read, in microseconds. | |
| PARAMS_VALUE | longtext | NO | The prepared statement-bound parameter value. | |
| RULE_NAME | varchar(256) | NO | The name of the rule.
NoteThis column is available in OceanBase Database V4.1.0 and later. |
|
| PARTITION_HIT | tinyint(4) | NO |
NoteThis column is available in OceanBase Database V4.1.0 and later. |
|
| TX_INTERNAL_ROUTING | bigint(20) | NO | Specifies whether the SQL statement is internally routed. Valid values:
NoteThis column is available in OceanBase Database V4.1.0 and later. |
|
| TX_STATE_VERSION | bigint(20) unsigned | NO | The transaction status version of the SQL statement.
NoteThis column is available in OceanBase Database V4.1.0 and later. |
|
| FLT_TRACE_ID | varchar(1024) | NO | The trace ID of this record in end-to-end diagnostics. If this column is left empty, the record is not monitored in end-to-end diagnostics. The value is a universally unique identifier (UUID), which is different from a trace. The format is similar to 000600d6-a5de-038c-6c80-df07e4e79149.
NoteThis column is available in OceanBase Database V4.2.1 and later. |
|
| PL_TRACE_ID | varchar(128) | NO | The trace ID of the outer PL block of the current SQL statement. If the SQL statement does not have an outer PL block, the value of this column is NULL.
Note
|
|
| PLSQL_EXEC_TIME | bigint(20) | NO | The amount of time consumed in PL execution, in microseconds, which does not include the amount of time consumed in SQL execution.
Note
|
|
| TOTAL_MEMSTORE_READ_ROW_COUNT | bigint(20) | NO | The total number of rows read in the MemStore in the whole working process. This column is displayed only in a thread that contains the query_text field.
Note
|
|
| TOTAL_SSSTORE_READ_ROW_COUNT | bigint(20) | NO | The total number of rows read in the SSStore in the whole working process. This column is displayed only in a thread that contains the query_text field.
Note
|
|
| PROXY_USER | varchar(128) | NO |
Note
|
Sample query
Query the statistics about each SQL request on all OBServer nodes, such as the source and execution status.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQL_AUDIT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: 172.xx.xx.xx
SVR_PORT: 2882
REQUEST_ID: 1550000
SQL_EXEC_ID: 4802587
TRACE_ID: YB4XXXXXXXXX-000XXXXXXXXXXXXX-0-0
SID: 1
CLIENT_IP: 0.0.0.0
CLIENT_PORT: 0
TENANT_ID: 1
TENANT_NAME: sys
EFFECTIVE_TENANT_ID: 1
USER_ID: 200001
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 0.0.0.0
DB_ID: 201001
DB_NAME: oceanbase
SQL_ID: 32AB97A0126F566064F84DDDF4936F82
QUERY_SQL: SELECT * FROM __all_ls_meta_table WHERE tenant_id = 1 ORDER BY tenant_id, ls_id, svr_ip, svr_port
PLAN_ID: 1220
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: 0
TOTAL_WAITS: 0
RPC_COUNT: 0
PLAN_TYPE: 1
IS_INNER_SQL: 1
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 1
REQUEST_TIME: 1723110103686935
ELAPSED_TIME: 158
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 0
DECODE_TIME: 0
GET_PLAN_TIME: 87
EXECUTE_TIME: 71
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: 89728
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: 0
SNAPSHOT_VERSION: 1723110103586530000
REQUEST_TYPE: 1
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: NULL
PLAN_HASH: 17719762375429218827
LOCK_FOR_READ_TIME: 0
PARAMS_VALUE:
RULE_NAME:
PARTITION_HIT: 1
TX_INTERNAL_ROUTING: 0
TX_STATE_VERSION: 0
FLT_TRACE_ID:
PL_TRACE_ID: NULL
PLSQL_EXEC_TIME: 0
TOTAL_MEMSTORE_READ_ROW_COUNT: 0
TOTAL_SSSTORE_READ_ROW_COUNT: 0
PROXY_USER:
1 row in set