Note
- This view is available starting with V4.3.5 for V4.3.x.
- This view is available starting with V4.2.2 for V4.2.x.
Purpose
The V$OB_SQLSTAT view displays the basic performance statistics of SQL statements on the current node. Each SQL statement is represented by a row, identified by the unique combination of SQL_ID and Plan_Hash. The columns with _DELTA in their names indicate the incremental values from the last WR snapshot collection to the current time.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | The IP address of the node. |
| SVR_PORT | bigint(20) | NO | The port number of the node. |
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement. |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the execution plan. |
| PLAN_TYPE | bigint(20) | NO | The type of the SQL plan, which is valid for SQL plan caching. The value can be 1, 2, or 3.
The type of the PL object, which is valid for PL object caching. The value can be 1, 2, 3, or 4.
|
| QUERY_SQL | longtext | NO | The full text of the SQL statement. |
| SQL_TYPE | bigint(20) | NO | The type of the statement. |
| MODULE | varchar(64) | NO | The application module of the statement when it was first parsed. |
| ACTION | varchar(64) | NO | The application action of the statement when it was first parsed. |
| PARSING_DB_ID | bigint(20) | NO | The database ID when the statement was parsed. |
| PARSING_DB_NAME | varchar(128) | NO | The database name when the statement was parsed. |
| PARSING_USER_ID | bigint(20) | NO | The user ID when the statement was parsed. |
| EXECUTIONS_TOTAL | bigint(20) | NO | The total number of times the plan was executed in the plan cache. |
| EXECUTIONS_DELTA | bigint(20) | NO | The incremental number of times the plan was executed in the plan cache. |
| DISK_READS_TOTAL | bigint(20) | NO | The total number of disk reads. |
| DISK_READS_DELTA | bigint(20) | NO | The incremental number of disk reads. |
| BUFFER_GETS_TOTAL | bigint(20) | NO | The total number of logical reads from the cache. |
| BUFFER_GETS_DELTA | bigint(20) | NO | The incremental number of logical reads from the cache. |
| ELAPSED_TIME_TOTAL | bigint(20) | NO | The total time consumed by the statement. For parallel execution, this value is the sum of the values for each execution thread. |
| ELAPSED_TIME_DELTA | bigint(20) | NO | The incremental time consumed by the statement. |
| CPU_TIME_TOTAL | bigint(20) | NO | The total CPU time consumed. |
| CPU_TIME_DELTA | bigint(20) | NO | The incremental CPU time consumed. |
| CCWAIT_TOTAL | bigint(20) | NO | The total time consumed by concurrency-related wait events. |
| CCWAIT_DELTA | bigint(20) | NO | The incremental time consumed by concurrency-related wait events. |
| USERIO_WAIT_TOTAL | bigint(20) | NO | The total time consumed by I/O waits. |
| USERIO_WAIT_DELTA | bigint(20) | NO | The incremental time consumed by I/O waits. |
| APWAIT_TOTAL | bigint(20) | NO | The total time consumed by application-related wait events. |
| APWAIT_DELTA | bigint(20) | NO | The incremental time consumed by application-related wait events. |
| PHYSICAL_READ_REQUESTS_TOTAL | bigint(20) | NO | The total number of physical read requests. |
| PHYSICAL_READ_REQUESTS_DELTA | bigint(20) | NO | The incremental number of physical read requests. |
| PHYSICAL_READ_BYTES_TOTAL | bigint(20) | NO | The total number of bytes read. |
| PHYSICAL_READ_BYTES_DELTA | bigint(20) | NO | The incremental number of bytes read. |
| WRITE_THROTTLE_TOTAL | bigint(20) | NO | The total time spent on throttling when writing to the MemStore. |
| WRITE_THROTTLE_DELTA | bigint(20) | NO | The incremental time spent on throttling when writing to the MemStore. |
| ROWS_PROCESSED_TOTAL | bigint(20) | NO | The total number of rows processed by the statement. |
| ROWS_PROCESSED_DELTA | bigint(20) | NO | The incremental number of rows processed by the statement. |
| MEMSTORE_READ_ROWS_TOTAL | bigint(20) | NO | The total number of rows read from the MemStore. |
| MEMSTORE_READ_ROWS_DELTA | bigint(20) | NO | The incremental number of rows read from the MemStore. |
| MINOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | The total number of rows read from the MINOR SSStore. |
| MINOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | The incremental number of rows read from the MINOR SSStore. |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | The total number of rows read from the MAJOR SSStore. |
| MAJOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | The incremental number of rows read from the MAJOR SSStore. |
| RPC_TOTAL | bigint(20) | NO | The total number of RPC calls. |
| RPC_DELTA | bigint(20) | NO | The incremental number of RPC calls. |
| FETCHES_TOTAL | bigint(20) | NO | The total number of result set fetches. |
| FETCHES_DELTA | bigint(20) | NO | The incremental number of result set fetches. |
| RETRY_TOTAL | bigint(20) | NO | The total number of SQL retries. |
| RETRY_DELTA | bigint(20) | NO | The incremental number of SQL retries. |
| PARTITION_TOTAL | bigint(20) | NO | The total number of partitions scanned by the SQL statement. |
| PARTITION_DELTA | bigint(20) | NO | The incremental number of partitions scanned by the SQL statement. |
| NESTED_SQL_TOTAL | bigint(20) | NO | The total number of nested SQL statements executed. Each time a sub-SQL statement is executed, the value increases by 1. |
| NESTED_SQL_DELTA | bigint(20) | NO | The incremental number of nested SQL statements executed. |
| SOURCE_IP | varchar(46) | NO | The IP address of the request source. |
| SOURCE_PORT | bigint(20) | NO | The port number of the request source. |
| ROUTE_MISS_TOTAL | bigint(20) | NO | The total number of times that no partition is hit since the OBServer started.
Note
|
| ROUTE_MISS_DELTA | bigint(20) | NO | The number of times that no partition is hit since the last WR snapshot collection.
Note
|
| FIRST_LOAD_TIME | timestamp(6) | NO | The time when the plan was generated.
Note
|
| PLAN_CACHE_HIT_TOTAL | bigint(20) | NO | The total number of times that the Plan Cache is hit since the OBServer started.
Note
|
| PLAN_CACHE_HIT_DELTA | bigint(20) | NO | The number of times that the Plan Cache is hit since the last WR snapshot collection.
Note
|
| MUTI_QUERY_TOTAL | bigint(20) | NO | The number of times that the SQL statement is in a Muti Query since the execution plan was generated.
NoteFor V4.4.x, this field is available starting with V4.4.2. |
| MUTI_QUERY_DELTA | bigint(20) | NO | The number of times that the SQL statement is in a Muti Query since the last WR snapshot collection.
NoteFor V4.4.x, this field is available starting with V4.4.2. |
| MUTI_QUERY_BATCH_TOTAL | bigint(20) | NO | The number of times that the SQL batch is executed since the execution plan was generated.
NoteFor V4.4.x, this field is available starting with V4.4.2. |
| MUTI_QUERY_BATCH_DELTA | bigint(20) | NO | The number of times the SQL batch has been executed since the last WR snapshot was captured.
NoteThis column is available starting with V4.4.2 for V4.4.x. |
| FULL_TABLE_SCAN_TOTAL | bigint(20) | NO | The number of full table scans since the execution plan was generated.
NoteThis column is available starting with V4.4.2 for V4.4.x. |
| FULL_TABLE_SCAN_DELTA | bigint(20) | NO | The number of full table scans since the last WR snapshot was captured.
NoteThis column is available starting with V4.4.2 for V4.4.x. |
| ERROR_COUNT_TOTAL | bigint(20) | NO | The number of errors since the execution plan was generated.
NoteThis column is available starting with V4.4.2 for V4.4.x. |
| ERROR_COUNT_DELTA | bigint(20) | NO | The number of errors since the last WR snapshot was captured.
NoteThis column is available starting with V4.4.2 for V4.4.x. |
| LATEST_ACTIVE_TIME | timestamp(6) | NO | The last active time of the SQL ID + Plan Hash pair on a node.
NoteThis column is available starting with V4.4.2 for V4.4.x. |
The values of the SQL_TYPE column and the corresponding statements are shown in the table below.
Number |
Statement type |
Number |
Statement type |
|---|---|---|---|
| 1 | SELECT | 2 | INSERT |
| 3 | REPLACE | 4 | DELETE |
| 5 | UPDATE | 6 | MERGE |
| 7 | EXPLAIN | 8 | CREATE TENANT |
| 9 | DROP TENANT | 10 | LOCK TENANT |
| 11 | MODIFY TENANT | 12 | CHANGE TENANT |
| 13 | CREATE RESOURCE POOL | 14 | DROP RESOURCE POOL |
| 15 | ALTER RESOURCE POOL | 16 | SPLIT RESOURCE POOL |
| 17 | CREATE RESOURCE UNIT | 18 | ALTER RESOURCE UNIT |
| 19 | DROP RESOURCE UNIT | 20 | CREATE TABLE |
| 21 | DROP TABLE | 22 | ALTER TABLE |
| 23 | CREATE INDEX | 24 | DROP INDEX |
| 25 | CREATE VIEW | 26 | ALTER VIEW |
| 27 | DROP VIEW | 28 | HELP |
| 29 | SHOW TABLES | 30 | SHOW DATABASES |
| 31 | SHOW COLUMNS | 32 | SHOW VARIABLES |
| 33 | SHOW TABLE | 34 | SHOW SCHEMA |
| 35 | SHOW CREATE DATABASE | 36 | SHOW CREATE TABLE |
| 37 | SHOW CREATE VIEW | 38 | SHOW CREATE PROCEDURE |
| 39 | SHOW CREATE FUNCTION | 40 | SHOW PARAMETERS |
| 41 | SHOW SERVER STATUS | 42 | SHOW INDEXES |
| 43 | SHOW WARNINGS | 44 | SHOW ERRORS |
| 45 | SHOW PROCESSLIST | 46 | SHOW CHARSET |
| 47 | SHOW COLLATION | 48 | SHOW TABLEGROUPS |
| 49 | SHOW STATUS | 50 | SHOW TENANT |
| 51 | SHOW CREATE TENANT | 52 | SHOW TRACE |
| 53 | SHOW ENGINES | 54 | SHOW PRIVILEGES |
| 55 | SHOW PROCEDURE STATUS | 56 | SHOW FUNCTION STATUS |
| 57 | SHOW GRANTS | 58 | CREATE USER |
| 59 | DROP USER | 60 | SET PASSWORD |
| 61 | LOCK USER | 62 | RENAME USER |
| 63 | GRANT OBJECT | 64 | REVOKE OBJECT |
| 65 | PREPARE | 66 | SET |
| 67 | EXECUTE | 68 | DEALLOCATE |
| 69 | START TRANS | 70 | END TRANS |
| 71 | KILL | 72 | ALTER SYSTEM |
| 73 | ALTER SYSTEM SETTP | 74 | CHANGE OBI |
| 75 | SWITCH MASTER | 76 | ALTER SYSTEM SERVER |
| 77 | BOOTSTRAP | 78 | CS DISKMAINTAIN |
| 79 | TABLET | 80 | REPORT REPLICA |
| 81 | SWITCH ROOTSERVER | 82 | SWITCH UPDATESERVER |
| 83 | CLUSTER MANAGER | 84 | FREEZE |
| 85 | FLUSH CACHE | 86 | FLUSH KVCACHE |
| 87 | FLUSH ILOGCACHE | 88 | DROP MEMTABLE |
| 89 | CLEAR MEMTABLE | 90 | PRINT ROOT TABLE |
| 91 | ADD UPDATESERVER | 92 | DELETE UPDATESERVER |
| 93 | CHECK ROOT TABLE | 94 | CLEAR ROOT TABLE |
| 95 | REFRESH SCHEMA | 96 | CREATE DATABASE |
| 97 | USE DATABASE | 98 | ADMIN SERVER |
| 99 | ADMIN ZONE | 100 | SWITCH REPLICA ROLE |
| 103 | RECYCLE REPLICA | 104 | MERGE |
| 105 | ALTER DATABASEE | 106 | DROP DATABASE |
| 107 | CREATE TABLEGROUP | 108 | DROP TABLEGROUP |
| 109 | ALTER TABLEGROUP | 110 | TRUNCATE TABLE |
| 111 | RENAME TABLE | 112 | CREATE TABLE LIKE |
| 113 | SET NAMES | 114 | CLEAR LOCATION CACHE |
| 115 | RELOAD GTS | 116 | RELOAD UNIT |
| 117 | RELOAD SERVER | 118 | RELOAD ZONE |
| 119 | CLEAR MERGE ERROR | 120 | MIGRATE UNIT |
| 121 | UPGRADE VIRTUAL SCHEMA | 122 | RUN JOB |
| 123 | EMPTY QUERY | 124 | CREATE OUTLINE |
| 125 | ALTER OUTLINE | 126 | DROP OUTLINE |
| 129 | SWITCH RS ROLE | 130 | FLASHBACK TENANT |
| 131 | FLASHBACK DATABASE | 132 | FLASHBACK TABLE |
| 133 | FLASHBACK | 134 | PURGE RECYCLEBIN |
| 135 | PURGE TENANT | 136 | PURGE DATABASE |
| 137 | PURGE TABLE | 138 | PURGE INDEX |
| 139 | SHOW RECYCLEBIN | 140 | UPGRADE |
| 141 | CREATE ROUTINE | 142 | DROP ROUTINE |
| 143 | ALTER ROUTINE | 144 | CALL |
| 145 | ANONYMOUS BLOCK | 146 | CREATE PACKAGE |
| 147 | CREATE PACKAGE BODY | 148 | ALTER PACKAGE |
| 149 | DROP PACKAGE | 150 | REFRESH TIME ZONE INFO |
| 151 | CANCEL TASK | 154 | SET DISK VALID |
| 155 | CREATE SYNONYM | 156 | DROP SYNONYM |
| 157 | CLEAR BALANCE TASK | 158 | BUILD INDEX SSTABLE |
| 159 | ANALYZE | 160 | SHOW CREATE TABLEGROUP |
| 161 | LOAD DATA | 162 | STMT TYPE MAX |
| 163 | CREATE FUNCTION | 164 | DROP FUNCTION |
| 165 | CREATE SEQUENCE | 166 | ALTER SEQUENCE |
| 167 | DROP SEQUENCE | 168 | SET TABLE COMMENT |
| 169 | SET COLUMN COMMENT | 170 | SWITCHOVER |
| 171 | CREATE TYPE | 172 | DROP TYPE |
| 173 | ALTER DISKGROUP ADD DISK | 174 | ALTER DISKGROUP DROP DISK |
| 175 | DROP CLUSTER | 176 | CREATE CLUSTER |
| 177 | ALTER SYSTEM SET PARAMETER | 178 | ALTER TABLE |
| 179 | OPTIMIZE TENANT | 180 | OPTIMIZE ALL |
| 181 | CREATE SAVEPOINT | 182 | ROLLBACK SAVEPOINT |
| 183 | RELEASE SAVEPOINT | 184 | CREATE KEYSTORE |
| 185 | ALTER KEYSTORE | 186 | CREATE TABLESPACE |
| 187 | DROP TABLESPACE | 188 | CREATE TRIGGER |
| 189 | DROP TRIGGER | 190 | FLASHBACK TABLE TO SCN |
| 191 | CREATE ROLE | 192 | DROP ROLE |
| 193 | ALTER ROLE | 194 | SET ROLE |
| 195 | SYSTEM GRANT | 196 | SYSTEM REVOKE |
| 197 | USER PROFILE | 198 | ALTER USER PROFILE |
| 199 | AUDIT | 200 | LOGON |
| 201 | LOGOFF | 202 | ALTER CLUSTER |
| 203 | ARCHIVELOG | 204 | BACKUP DATABASE |
| 205 | BACKUP MANAGE | 206 | RESTORE TENANT |
| 207 | REFRESH MEMORY STAT | 208 | PHYSICAL RESTORE TENANT |
| 209 | ALTER TABLESPACE | 210 | ROLLING UPGRADE |
| 211 | GRANT ROLE | 212 | REVOKE ROLE |
| 213 | MERGE RESOURCE POOL | 214 | SHOW TRIGGERS |
| 215 | CREATE DBLINK | 216 | DROP DBLINK |
| 217 | CREATE PUBLIC SYNONYM | 218 | DROP PUBLIC SYNONYM |
| 219 | CREATE PROFILE | 220 | ALTER PROFILE |
| 221 | DROP PROFILE | 222 | ALTER USER |
| 223 | XA START | 224 | XA END |
| 225 | XA PREPARE | 226 | XA COMMIT |
| 227 | XA ROLLBACK | 228 | BACKUP BACKUPSET |
| 229 | BACKUP ARCHIVELOG | 230 | ALTER TRIGGER |
| 231 | ALTER USER PRIMARY ZONE | 232 | SHOW CREATE TRIGGER |
| 233 | DIAGNOSTICS | 234 | BACKUP CLEAN |
| 235 | DELETE BACKUP POLICY | 236 | SHOW QUERY RESPONSE TIME |
| 250 | ENABLE SQL THROTTLE | 251 | DISABLE SQL THROTTLE |
| 252 | BACKUP SET ENCRYPTION | 253 | BACKUP SET DECRYPTION |
| 254 | RECOVERY | 255 | CREATE RESTORE POINT |
| 256 | DROP RESTORE POINT | 257 | RUN UPGRADE JOB |
| 258 | STOP UPGRADE JOB | 259 | FLUSH DAG WARNINGS |
| 261 | BACKUP BACKUPPIECE | 262 | ADD RESTORE SOURCE |
| 263 | CLEAR RESTORE SOURCE | 264 | SHOW RESTORE PREVIEW |
| 265 | SET REGION NETWORK BANDWIDTH | 266 | CREATE DIRECTORY |
| 267 | DROP DIRECTORY | 268 | LOCK TABLE |
| 269 | ALTER RESOURCE TENANT | 270 | ARCHIVE TENANT |
| 271 | DISCONNECT CLUSTER | 272 | WASH MEMORY FRAGMENTATION |
| 273 | CREATE CONTEXT | 274 | DROP CONTEXT |
| 275 | CHECKPOINT SLOG | 276 | REFRESH IO CALIBRATION |
| 277 | INSERT ALL | 278 | PHYSICAL RESTORE STANDBY TENANT |
| 279 | RECOVER | 280 | ADD ARBITRATION SERVICE |
| 281 | REMOVE ARBITRATION SERVICE | 282 | REPLACE ARBITRATION SERVICE |
| 283 | SHOW SEQUENCES | 284 | BACKUP KEY |
| 285 | CREATE STANDBY TENANT | 286 | CANCEL RESTORE |
| 287 | TABLE TTL | 288 | RECOVER TABLE |
| 289 | CANCEL RECOVER TABLE | 290 | CREATE TENANT SNAPSHOT |
| 291 | DROP TENANT SNAPSHOT | 292 | ALTER SYSTEM RESET PARAMETER |
| 293 | CLONE TENANT | 294 | CANCEL CLONE |
| 295 | CREATE MLOG | 296 | DROP MLOG |
Sample query
The following example queries the basic performance statistics of SQL statements on the current node and displays the first record.
obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SQLSTAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
TENANT_ID: 1
SQL_ID: 7222544C4715703B717FAFF8E69B4480
PLAN_ID: 74
PLAN_HASH: 15514170908265371714
PLAN_TYPE: 1
QUERY_SQL: SELECT MAX(schema_version) as version, host_ip() as myip, rpc_port() as myport FROM __all_ddl_operation
SQL_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 1305742
EXECUTIONS_DELTA: 1054
DISK_READS_TOTAL: 148
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 943
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 141887190
ELAPSED_TIME_DELTA: 114371
CPU_TIME_TOTAL: 0
CPU_TIME_DELTA: 0
CCWAIT_TOTAL: 0
CCWAIT_DELTA: 0
USERIO_WAIT_TOTAL: 0
USERIO_WAIT_DELTA: 0
APWAIT_TOTAL: 0
APWAIT_DELTA: 0
PHYSICAL_READ_REQUESTS_TOTAL: 148
PHYSICAL_READ_REQUESTS_DELTA: 0
PHYSICAL_READ_BYTES_TOTAL: 173940
PHYSICAL_READ_BYTES_DELTA: 0
WRITE_THROTTLE_TOTAL: 0
WRITE_THROTTLE_DELTA: 0
ROWS_PROCESSED_TOTAL: 1305742
ROWS_PROCESSED_DELTA: 1054
MEMSTORE_READ_ROWS_TOTAL: 63308
MEMSTORE_READ_ROWS_DELTA: 0
MINOR_SSSTORE_READ_ROWS_TOTAL: 306
MINOR_SSSTORE_READ_ROWS_DELTA: 0
MAJOR_SSSTORE_READ_ROWS_TOTAL: 1294316
MAJOR_SSSTORE_READ_ROWS_DELTA: 1054
RPC_TOTAL: 0
RPC_DELTA: 0
FETCHES_TOTAL: 0
FETCHES_DELTA: 0
RETRY_TOTAL: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 1305742
PARTITION_DELTA: 1054
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: xx.xx.xx.xx
SOURCE_PORT: 2882
ROUTE_MISS_TOTAL: 0
ROUTE_MISS_DELTA: 0
FIRST_LOAD_TIME: 2025-12-19 16:47:11.871495
PLAN_CACHE_HIT_TOTAL: 1305741
PLAN_CACHE_HIT_DELTA: 1054
MUTI_QUERY_TOTAL: 0
MUTI_QUERY_DELTA: 0
MUTI_QUERY_BATCH_TOTAL: 0
MUTI_QUERY_BATCH_DELTA: 0
FULL_TABLE_SCAN_TOTAL: 0
FULL_TABLE_SCAN_DELTA: 0
ERROR_COUNT_TOTAL: 0
ERROR_COUNT_DELTA: 0
LATEST_ACTIVE_TIME: 2026-01-01 08:00:00.000000
1 row in set (0.016 sec)