Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The GV$OB_SQLSTAT view displays the basic performance statistics of SQL statements in the cluster. Each row indicates one SQL statement. To be specific, each row is uniquely identified by SQL_ID and Plan_Hash. A column whose name contains _DELTA indicates the incremental value since a Workload Repository (WR) snapshot was last collected.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | NUMBER | NO | The port number of the OBServer node. |
| TENANT_ID | NUMBER | NO | The ID of the tenant. |
| SQL_ID | VARCHAR2(32) | NO | The ID of the SQL statement. |
| PLAN_ID | NUMBER | NO | The ID of the execution plan. |
| PLAN_HASH | NUMBER unsigned | NO | The hash value of the execution plan. |
| PLAN_TYPE | NUMBER | NO | For SQL plan caching, this column indicates the type of the plan. Valid values:
For PL object caching, this column indicates the type of the PL object. Valid values:
|
| QUERY_SQL | CLOB | NO | The full text of the SQL statement. |
| SQL_TYPE | NUMBER | NO | The type of the SQL statement. |
| MODULE | VARCHAR2(64) | YES | The application module by which the statement was first parsed. |
| ACTION | VARCHAR2(64) | YES | The application action by which the statement was first parsed. |
| PARSING_DB_ID | NUMBER | NO | The ID of the database where the statement was parsed. |
| PARSING_DB_NAME | VARCHAR2(128) | NO | The name of the database where the statement was parsed. |
| PARSING_USER_ID | NUMBER | NO | The ID of the user who parsed the statement. |
| EXECUTIONS_TOTAL | NUMBER | NO | The total number of executions of the plan in the plan cache. |
| EXECUTIONS_DELTA | NUMBER | NO | The incremental number of executions of the plan in the plan cache. |
| DISK_READS_TOTAL | NUMBER | NO | The total number of disk reads. |
| DISK_READS_DELTA | NUMBER | NO | The incremental number of disk reads. |
| BUFFER_GETS_TOTAL | NUMBER | NO | The total number of logical reads from the cache. |
| BUFFER_GETS_DELTA | NUMBER | NO | The incremental number of logical reads from the cache. |
| ELAPSED_TIME_TOTAL | NUMBER | NO | The total execution time of the statement. If the statement is executed by multiple threads in parallel, this value is the sum of the execution time taken by each thread. |
| ELAPSED_TIME_DELTA | NUMBER | NO | The incremental execution time of the statement. |
| CPU_TIME_TOTAL | NUMBER | NO | The total CPU time. |
| CPU_TIME_DELTA | NUMBER | NO | The incremental amount of CPU time. |
| CCWAIT_TOTAL | NUMBER | NO | The total amount of time waited for events of the concurrency class. |
| CCWAIT_DELTA | NUMBER | NO | The incremental amount of time waited for events of the concurrency class. |
| USERIO_WAIT_TOTAL | NUMBER | NO | The total amount of time waited for events of the user I/O class. |
| USERIO_WAIT_DELTA | NUMBER | NO | The incremental amount of time waited for events of the user I/O class. |
| APWAIT_TOTAL | NUMBER | NO | The total amount of time waited for events of the application class. |
| APWAIT_DELTA | NUMBER | NO | The incremental amount of time waited for events of the application class. |
| PHYSICAL_READ_REQUESTS_TOTAL | NUMBER | NO | The total number of physical reads. |
| PHYSICAL_READ_REQUESTS_DELTA | NUMBER | NO | The incremental number of physical reads. |
| PHYSICAL_READ_BYTES_TOTAL | NUMBER | NO | The total size in bytes of physical reads. |
| PHYSICAL_READ_BYTES_DELTA | NUMBER | NO | The incremental size in bytes of physical reads. |
| WRITE_THROTTLE_TOTAL | NUMBER | NO | The total amount of time of throttling for writes to the MemStore. |
| WRITE_THROTTLE_DELTA | NUMBER | NO | The incremental amount of time of throttling for writes to the MemStore. |
| ROWS_PROCESSED_TOTAL | NUMBER | NO | The total number of rows processed by the statement. |
| ROWS_PROCESSED_DELTA | NUMBER | NO | The incremental number of rows processed by the statement. |
| MEMSTORE_READ_ROWS_TOTAL | NUMBER | NO | The total number of rows read from the MemStore. |
| MEMSTORE_READ_ROWS_DELTA | NUMBER | NO | The incremental number of rows read from the MemStore. |
| MINOR_SSSTORE_READ_ROWS_TOTAL | NUMBER | NO | The total number of rows read from the minor SSStore. |
| MINOR_SSSTORE_READ_ROWS_DELTA | NUMBER | NO | The incremental number of rows read from the minor SSStore. |
| MAJOR_SSSTORE_READ_ROWS_TOTAL | NUMBER | NO | The total number of rows read from the major SSStore. |
| MAJOR_SSSTORE_READ_ROWS_DELTA | NUMBER | NO | The incremental number of rows read from the major SSStore. |
| RPC_TOTAL | NUMBER | NO | The total number of remote procedure call (RPC) requests. |
| RPC_DELTA | NUMBER | NO | The incremental number of RPC requests. |
| FETCHES_TOTAL | NUMBER | NO | The total number of result set fetches. |
| FETCHES_DELTA | NUMBER | NO | The incremental number of result set fetches. |
| RETRY_TOTAL | NUMBER | NO | The total number of retries of the SQL statement. |
| RETRY_DELTA | NUMBER | NO | The incremental number of retries of the SQL statement. |
| PARTITION_TOTAL | NUMBER | NO | The total number of partitions scanned by the SQL statement. |
| PARTITION_DELTA | NUMBER | NO | The incremental number of partitions scanned by the SQL statement. |
| NESTED_SQL_TOTAL | NUMBER | NO | The total number of executions of nested SQL statements. The value increases by 1 each time a nested SQL statement is executed. |
| NESTED_SQL_DELTA | NUMBER | NO | The incremental number of executions of nested SQL statements. |
| SOURCE_IP | CHAR(46) | NO | The IP address of the request source. |
| SOURCE_PORT | NUMBER | NO | The port number of the request source. |
The following table lists the values in the SQL_TYPE column and the corresponding statements.
Value |
Statement type |
Value |
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
obclient [SYS]> SELECT * FROM SYS.GV$OB_SQLSTAT WHERE ROWNUM = 1\G
The query result is as follows:
*************************** 1. row ***************************
SVR_IP: 172.xx.xxx.xxx
SVR_PORT: 2882
TENANT_ID: 1004
SQL_ID: 17605A1DA6B6A2150E9FBCA5D4C7653A
PLAN_ID: 25
PLAN_HASH: 12254811659671915614
PLAN_TYPE: 1
QUERY_SQL: SELECT row_id, column_name, column_value FROM __all_core_table WHERE table_name = ? ORDER BY row_id, column_name
MODULE: NULL
ACTION: NULL
PARSING_DB_ID: 201001
PARSING_DB_NAME: oceanbase
PARSING_USER_ID: 200001
EXECUTIONS_TOTAL: 33796
EXECUTIONS_DELTA: 5072
DISK_READS_TOTAL: 29
DISK_READS_DELTA: 14
BUFFER_GETS_TOTAL: 253909
BUFFER_GETS_DELTA: 38017
ELAPSED_TIME_TOTAL: 6049268
ELAPSED_TIME_DELTA: 994076
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: 29
PHYSICAL_READ_REQUESTS_DELTA: 14
PHYSICAL_READ_BYTES_TOTAL: 209769
PHYSICAL_READ_BYTES_DELTA: 92311
WRITE_THROTTLE_TOTAL: 0
WRITE_THROTTLE_DELTA: 0
ROWS_PROCESSED_TOTAL: 33795
ROWS_PROCESSED_DELTA: 5072
MEMSTORE_READ_ROWS_TOTAL: 33754
MEMSTORE_READ_ROWS_DELTA: 5040
MINOR_SSSTORE_READ_ROWS_TOTAL: 62907
MINOR_SSSTORE_READ_ROWS_DELTA: 8559
MAJOR_SSSTORE_READ_ROWS_TOTAL: 15638
MAJOR_SSSTORE_READ_ROWS_DELTA: 5072
RPC_TOTAL: 2
RPC_DELTA: 0
FETCHES_TOTAL: 0
FETCHES_DELTA: 0
RETRY_TOTAL: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 33796
PARTITION_DELTA: 5072
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: 172.xx.xxx.xxx
SOURCE_PORT: 2882
1 row in set