Note
This view is available starting with V4.2.2.
Purpose
The GV$OB_SQLSTAT view displays basic performance statistics of SQL statements in a cluster, with each SQL statement occupying one row (that is, each unique value of SQL_ID + Plan_Hash occupies one row). Columns that contain _DELTA indicate the increment of the statistical value from the last WR snapshot collection to the current time.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SVR_IP | varchar(46) | NO | Node IP |
| SVR_PORT | bigint(20) | NO | Node port number |
| TENANT_ID | bigint(20) | NO | Tenant ID |
| SQL_ID | varchar(32) | NO | SQL statement ID |
| PLAN_ID | bigint(20) | NO | Execution plan ID |
| PLAN_HASH | bigint(20) unsigned | NO | Hash value of the execution plan |
| PLAN_TYPE | bigint(20) | NO | For SQL plan caching, this field indicates the plan type:
For the PL object cache, this field indicates the type of the PL object:
|
| QUERY_SQL | longtext | NO | Full SQL text |
| SQL_TYPE | bigint(20) | NO | Statement type |
| MODULE | varchar(64) | YES | The application Module when the statement was first parsed, throughDBMS_APPLICATION_INFO.SET_MODULEPackage settings |
| ACTION | varchar(64) | YES | The application Action when this statement was first parsed, throughDBMS_APPLICATION_INFO.SET_ACTIONPackage settings |
| PARSING_DB_ID | bigint(20) | NO | Database ID during statement parsing |
| PARSING_DB_NAME | varchar(128) | NO | Database name during statement parsing |
| PARSING_USER_ID | bigint(20) | NO | User ID during statement parsing |
| EXECUTIONS_TOTAL | bigint(20) | NO | Total number of executions of this plan in the plan cache |
| EXECUTIONS_DELTA | bigint(20) | NO | Increment of the total number of executions of this plan in the plan cache |
| DISK_READS_TOTAL | bigint(20) | NO | Total number of disk reads |
| DISK_READS_DELTA | bigint(20) | NO | Increment in disk reads |
| BUFFER_GETS_TOTAL | bigint(20) | NO | Total number of logical reads from cache |
| BUFFER_GETS_DELTA | bigint(20) | NO | Increment in the number of logical reads from the cache |
| ELAPSED_TIME_TOTAL | bigint(20) | NO | The total time consumed by this statement. In parallel execution scenarios, this value is the cumulative value for each execution thread. |
| ELAPSED_TIME_DELTA | bigint(20) | NO | Incremental time consumed by this statement |
| CPU_TIME_TOTAL | bigint(20) | NO | Total CPU time consumed |
| CPU_TIME_DELTA | bigint(20) | NO | Increment of total CPU time consumed |
| CCWAIT_TOTAL | bigint(20) | NO | Total time consumed by concurrency wait events |
| CCWAIT_DELTA | bigint(20) | NO | Increment in total time spent on concurrency wait events |
| USERIO_WAIT_TOTAL | bigint(20) | NO | Total time spent waiting for I/O |
| USERIO_WAIT_DELTA | bigint(20) | NO | Incremental total duration of I/O waits |
| APWAIT_TOTAL | bigint(20) | NO | Total time consumed by application wait events |
| APWAIT_DELTA | bigint(20) | NO | Increment of the total time consumed by application-type wait events |
| PHYSICAL_READ_REQUESTS_TOTAL | bigint(20) | NO | Total number of physical read requests |
| PHYSICAL_READ_REQUESTS_DELTA | bigint(20) | NO | Increment in the number of physical read requests |
| PHYSICAL_READ_BYTES_TOTAL | bigint(20) | NO | Total bytes read physically |
| PHYSICAL_READ_BYTES_DELTA | bigint(20) | NO | Increment in physical read bytes |
| WRITE_THROTTLE_TOTAL | bigint(20) | NO | Total time throttled when writing to MemStore |
| WRITE_THROTTLE_DELTA | bigint(20) | NO | Increment of the total time throttled when writing to MemStore |
| ROWS_PROCESSED_TOTAL | bigint(20) | NO | Total number of rows processed by the statement |
| ROWS_PROCESSED_DELTA | bigint(20) | NO | Increment in the total number of rows processed by the statement |
| MEMSTORE_READ_ROWS_TOTAL | bigint(20) | NO | Total number of rows read from MemStore |
| MEMSTORE_READ_ROWS_DELTA | bigint(20) | NO | Increment in the total number of rows read from MemStore |
| MINOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | Total number of rows read from MINOR SSStore |
| MINOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | Increment in the total number of rows read from MINOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | bigint(20) | NO | Total number of rows read from MAJOR SSStore |
| MAJOR_SSSTORE_READ_ROWS_DELTA | bigint(20) | NO | Increment in the total number of rows read from MAJOR SSStore |
| RPC_TOTAL | bigint(20) | NO | Total number of RPCs |
| RPC_DELTA | bigint(20) | NO | Increment in the number of RPC calls |
| FETCHES_TOTAL | bigint(20) | NO | Total number of times result sets are retrieved |
| FETCHES_DELTA | bigint(20) | NO | Increment in the number of times the result set was obtained |
| RETRY_TOTAL | bigint(20) | NO | Total number of SQL retries |
| RETRY_DELTA | bigint(20) | NO | Increment in the number of SQL retries |
| PARTITION_TOTAL | bigint(20) | NO | Total number of partitions scanned during SQL execution |
| PARTITION_DELTA | bigint(20) | NO | Increment in the number of partitions scanned during SQL execution |
| NESTED_SQL_TOTAL | bigint(20) | NO | Total number of nested SQL executions, incremented by 1 for each additional sub-SQL execution |
| NESTED_SQL_DELTA | bigint(20) | NO | Increment of nested SQL execution |
| SOURCE_IP | varchar(46) | NO | Request source IP |
| SOURCE_PORT | bigint(20) | NO | Port number of the request source |
| ROUTE_MISS_TOTAL | bigint(20) | NO | Total number of times the requested partition was not correctly hit since the execution plan was generated |
| ROUTE_MISS_DELTA | bigint(20) | NO | Total number of times the request partition was not correctly hit since the last WR snapshot collection |
| FIRST_LOAD_TIME | timestamp(6) | YES | Execution plan generation time |
| PLAN_CACHE_HIT_TOTAL | bigint(20) | NO | Total number of Plan Cache hits since the execution plan was generated |
| PLAN_CACHE_HIT_DELTA | bigint(20) | NO | Number of times the Plan Cache has been hit since the last WR snapshot collection |
| MUTI_QUERY_TOTAL | bigint(20) | NO | Number of times this SQL has been in Muti Query since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| MUTI_QUERY_DELTA | bigint(20) | NO | Number of times this SQL has been in Muti Query since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| MUTI_QUERY_BATCH_TOTAL | bigint(20) | NO | Number of times this SQL Batch has been executed since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| MUTI_QUERY_BATCH_DELTA | bigint(20) | NO | Number of times this SQL Batch has been executed since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| FULL_TABLE_SCAN_TOTAL | bigint(20) | NO | The number of full table scans performed by the SQL since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| FULL_TABLE_SCAN_DELTA | bigint(20) | NO | The number of full table scans by this SQL since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| ERROR_COUNT_TOTAL | bigint(20) | NO | Number of times this SQL reported errors since the execution plan was generated
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
| ERROR_COUNT_DELTA | bigint(20) | NO | Number of times this SQL has reported errors since the last WR snapshot collection
NoteFor V4.2.5, this field was introduced starting from V4.2.5 BP7. |
The following table describes the values of the SQL_TYPE column in the view and the corresponding statements.
Numbers |
Statement type |
Numbers |
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
View the basic performance statistics of SQL statements in a cluster and display the first record.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQLSTAT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
TENANT_ID: 1002
SQL_ID: FD4390F00BA50EBCC4BBAE0C3A06BD8E
PLAN_ID: 133
PLAN_HASH: 10950220524135603512
PLAN_TYPE: 1
QUERY_SQL: SELECT column_value FROM __all_core_table WHERE TABLE_NAME = ? AND COLUMN_NAME = ? FOR UPDATE
SQL_TYPE: 1
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 227017
EXECUTIONS_DELTA: 312
DISK_READS_TOTAL: 54
DISK_READS_DELTA: 0
BUFFER_GETS_TOTAL: 1896
BUFFER_GETS_DELTA: 0
ELAPSED_TIME_TOTAL: 143219394
ELAPSED_TIME_DELTA: 247601
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: 54
PHYSICAL_READ_REQUESTS_DELTA: 0
PHYSICAL_READ_BYTES_TOTAL: 131546
PHYSICAL_READ_BYTES_DELTA: 0
WRITE_THROTTLE_TOTAL: 0
WRITE_THROTTLE_DELTA: 0
ROWS_PROCESSED_TOTAL: 227017
ROWS_PROCESSED_DELTA: 312
MEMSTORE_READ_ROWS_TOTAL: 264444
MEMSTORE_READ_ROWS_DELTA: 312
MINOR_SSSTORE_READ_ROWS_TOTAL: 334659
MINOR_SSSTORE_READ_ROWS_DELTA: 312
MAJOR_SSSTORE_READ_ROWS_TOTAL: 1545194
MAJOR_SSSTORE_READ_ROWS_DELTA: 2184
RPC_TOTAL: 0
RPC_DELTA: 0
FETCHES_TOTAL: 0
FETCHES_DELTA: 0
RETRY_TOTAL: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 227017
PARTITION_DELTA: 312
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-09-25 11:11:09.123018
PLAN_CACHE_HIT_TOTAL: 227016
PLAN_CACHE_HIT_DELTA: 312
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
1 row in set