V$OB_SQLSTAT|V4.3.0| docs|Distributed Database

V$OB_SQLSTAT

Last Updated：2024-04-19 08:42:49 Updated

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	varchar(46)	NO	The IP address of the OBServer node.
SVR_PORT	bigint(20)	NO	The port number of the OBServer node.
TENANT_ID	bigint(20)	NO	The ID of the tenant.
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	For SQL plan caching, this column indicates the type of the plan. Valid values: `1`: indicates a local plan. `2`: indicates a remote plan. `3`: indicates a distributed plan. For PL object caching, this column indicates the type of the PL object. Valid values: `1`: indicates a procedure. `2`: indicates a function. `3`: indicates a package. `4`: indicates an anonymous block.
QUERY_SQL	longtext	NO	The full text of the SQL statement.
SQL_TYPE	bigint(20)	NO	The type of the SQL statement.
MODULE	varchar(64)	NO	The application module by which the statement was first parsed.
ACTION	varchar(64)	NO	The application action by which the statement was first parsed.
PARSING_DB_ID	bigint(20)	NO	The ID of the database where the statement was parsed.
PARSING_DB_NAME	varchar(128)	NO	The ID of the user who parsed the statement.
PARSING_USER_ID	bigint(20)	NO	The name of the database where the statement was parsed.
EXECUTIONS_TOTAL	bigint(20)	NO	The total number of executions of the plan in the plan cache.
EXECUTIONS_DELTA	bigint(20)	NO	The incremental number of executions of the plan 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 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	bigint(20)	NO	The incremental execution time of the statement.
CPU_TIME_TOTAL	bigint(20)	NO	The total CPU time.
CPU_TIME_DELTA	bigint(20)	NO	The incremental amount of CPU time.
CCWAIT_TOTAL	bigint(20)	NO	The total amount of time waited for events of the concurrency class.
CCWAIT_DELTA	bigint(20)	NO	The incremental amount of time waited for events of the concurrency class.
USERIO_WAIT_TOTAL	bigint(20)	NO	The total amount of time waited for events of the user I/O class.
USERIO_WAIT_DELTA	bigint(20)	NO	The incremental amount of time waited for events of the user I/O class.
APWAIT_TOTAL	bigint(20)	NO	The total amount of time waited for events of the application class.
APWAIT_DELTA	bigint(20)	NO	The incremental amount of time waited for events of the application class.
PHYSICAL_READ_REQUESTS_TOTAL	bigint(20)	NO	The total number of physical reads.
PHYSICAL_READ_REQUESTS_DELTA	bigint(20)	NO	The incremental number of physical reads.
PHYSICAL_READ_BYTES_TOTAL	bigint(20)	NO	The total size in bytes of physical reads.
PHYSICAL_READ_BYTES_DELTA	bigint(20)	NO	The incremental size in bytes of physical reads.
WRITE_THROTTLE_TOTAL	bigint(20)	NO	The total amount of time of throttling for writes to the MemStore.
WRITE_THROTTLE_DELTA	bigint(20)	NO	The incremental amount of time of throttling for writes 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 remote procedure call (RPC) requests.
RPC_DELTA	bigint(20)	NO	The incremental number of RPC requests.
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 retries of the SQL statement.
RETRY_DELTA	bigint(20)	NO	The incremental number of retries of the SQL statement.
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 executions of nested SQL statements. The value increases by 1 each time a nested SQL statement is executed.
NESTED_SQL_DELTA	bigint(20)	NO	The incremental number of executions of nested SQL statements.
SOURCE_IP	varchar(46)	NO	The IP address of the request source.
SOURCE_PORT	bigint(20)	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 [oceanbase]> SELECT * FROM oceanbase.V$OB_SQLSTAT LIMIT 1\G

The query result is as follows:

*************************** 1. row ***************************
                       SVR_IP: 172.xx.xxx.xxx
                     SVR_PORT: 2882
                    TENANT_ID: 1
                       SQL_ID: 0E7680C04FF2596BE3A3649C5FAC418D
                      PLAN_ID: 129
                    PLAN_HASH: 7420493073239164301
                    PLAN_TYPE: 1
                    QUERY_SQL: select @@version_comment limit ?
                     SQL_TYPE: 1
                       MODULE: NULL
                       ACTION: NULL
                PARSING_DB_ID: -1
              PARSING_DB_NAME:
              PARSING_USER_ID: 200001
             EXECUTIONS_TOTAL: 1
             EXECUTIONS_DELTA: 1
             DISK_READS_TOTAL: 0
             DISK_READS_DELTA: 0
            BUFFER_GETS_TOTAL: 0
            BUFFER_GETS_DELTA: 0
           ELAPSED_TIME_TOTAL: 1675
           ELAPSED_TIME_DELTA: 1675
               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: 0
 PHYSICAL_READ_REQUESTS_DELTA: 0
    PHYSICAL_READ_BYTES_TOTAL: 0
    PHYSICAL_READ_BYTES_DELTA: 0
         WRITE_THROTTLE_TOTAL: 0
         WRITE_THROTTLE_DELTA: 0
         ROWS_PROCESSED_TOTAL: 1
         ROWS_PROCESSED_DELTA: 1
     MEMSTORE_READ_ROWS_TOTAL: 0
     MEMSTORE_READ_ROWS_DELTA: 0
MINOR_SSSTORE_READ_ROWS_TOTAL: 0
MINOR_SSSTORE_READ_ROWS_DELTA: 0
MAJOR_SSSTORE_READ_ROWS_TOTAL: 0
MAJOR_SSSTORE_READ_ROWS_DELTA: 0
                    RPC_TOTAL: 0
                    RPC_DELTA: 0
                FETCHES_TOTAL: 0
                FETCHES_DELTA: 0
                  RETRY_TOTAL: 0
                  RETRY_DELTA: 0
              PARTITION_TOTAL: 0
              PARTITION_DELTA: 0
             NESTED_SQL_TOTAL: 0
             NESTED_SQL_DELTA: 0
                    SOURCE_IP: 172.xx.xxx.xxx
                  SOURCE_PORT: 2882
1 row in set

V$OB_SQLSTAT

Note

Purpose

Columns

Sample query

References