Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The V$OB_SQLSTAT view displays the basic performance statistics of SQL statements on the current OBServer node. 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) | NO | The application module by which the statement was first parsed. |
| ACTION | VARCHAR2(64) | NO | 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 spent on wait events of the concurrency class. |
| CCWAIT_DELTA | NUMBER | NO | The incremental amount of time spent on wait events of the concurrency class. |
| USERIO_WAIT_TOTAL | NUMBER | NO | The total amount of time spent on wait events of the user I/O class. |
| USERIO_WAIT_DELTA | NUMBER | NO | The incremental amount of time spent on wait events of the user I/O class. |
| APWAIT_TOTAL | NUMBER | NO | The total amount of time spent on wait events of the application class. |
| APWAIT_DELTA | NUMBER | NO | The incremental amount of time spent on wait 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 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.V$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: 33970
EXECUTIONS_DELTA: 5246
DISK_READS_TOTAL: 29
DISK_READS_DELTA: 14
BUFFER_GETS_TOTAL: 254779
BUFFER_GETS_DELTA: 38887
ELAPSED_TIME_TOTAL: 6078657
ELAPSED_TIME_DELTA: 1023465
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: 33969
ROWS_PROCESSED_DELTA: 5246
MEMSTORE_READ_ROWS_TOTAL: 33928
MEMSTORE_READ_ROWS_DELTA: 5214
MINOR_SSSTORE_READ_ROWS_TOTAL: 63081
MINOR_SSSTORE_READ_ROWS_DELTA: 8733
MAJOR_SSSTORE_READ_ROWS_TOTAL: 15812
MAJOR_SSSTORE_READ_ROWS_DELTA: 5246
RPC_TOTAL: 2
RPC_DELTA: 0
FETCHES_TOTAL: 0
FETCHES_DELTA: 0
RETRY_TOTAL: 0
RETRY_DELTA: 0
PARTITION_TOTAL: 33970
PARTITION_DELTA: 5246
NESTED_SQL_TOTAL: 0
NESTED_SQL_DELTA: 0
SOURCE_IP: 172.xx.xxx.xxx
SOURCE_PORT: 2882
1 row in set