OceanBase 4.x MySQL租户运维常用SQL（一）

黄尖，从事运维DBA工作，主要负责Oracle和OceanBase的运维，数据库技术爱好者。

本文是对OB4.x版本的MySQL租户的常用SQL进行了一些整理，目前是第一部分。

集群信息类

查看集群信息

show parameters like '%cluster%'

或

select * from oceanbase.v$ob_parameters where scope = 'CLUSTER' and name like ('%cluster%');

查看集群的租户信息

select *+ READ_CONSISTENCY(WEAK) */ * from oceanbase.dba_ob_tenants where tenant_type in ('SYS','USER');

查看集群的zone信息

select *+ READ_CONSISTENCY(WEAK) */ * from dba_ob_zones;

查看集群的observer信息

select *+ READ_CONSISTENCY(WEAK) */ * from dba_ob_servers;  

租户信息类

查看变量信息

show global variables like '%autocommit%'

或

select *+ READ_CONSISTENCY(WEAK) */ * from information_schema.global_variables where variable_name like '%autocommit%';  

查看租户基本信息

select *+ READ_CONSISTENCY(WEAK) */ * from oceanbase.dba_ob_tenants;

查看对应 Unit 的配置。

select *+ READ_CONSISTENCY(WEAK) */ * from oceanbase.dba_ob_units;

查看租户对应的资源池）。

select *+ READ_CONSISTENCY(WEAK) */ * from oceanbase.dba_ob_resource_pools --where tenant_id = 'xxx';

基本信息及空间类

集群级别资源分配

select *+ READ_CONSISTENCY(WEAK) */
a.zone, 
a.svr_ip,
a.svr_ip, 
b.status,
cpu_capacity,
cpu_assigned_max,
cpu_capacity-cpu_assigned_max as cpu_free,
round(memory_limit 1024/1024/1024 ,2) as memory_total_gb,
round((memory_limit-mem_capacity) 1024/1024/1024 ,2) as system_memory_gb,
round(mem_assigned 1024/1024/1024 ,2) as mem_assigned_gb,
round((mem_capacity-mem_assigned) 1024/1024/1024 ,2) as memory_free_gb,
round(log_disk_capacity 1024/1024/1024 ,2) as log_disk_capacity_gb,
round(log_disk_assigned 1024/1024/1024 ,2) as log_disk_assigned_gb,
round((log_disk_capacity-log_disk_assigned) 1024/1024/1024 ,2) as log_disk_free_gb,
round((data_disk_capacity 1024/1024/1024 ),2) as data_disk_gb,
round((data_disk_in_use 1024/1024/1024 ),2) as data_disk_used_gb,
round((data_disk_capacity-data_disk_in_use) 1024/1024/1024 ,2) as data_disk_free_gb 
from gv$ob_servers a 
join oceanbase.DBA_OB_SERVERS b on a.zone=b.zone\G;

查看租户信息及空间（需要登录SYS租户）

select *+ READ_CONSISTENCY(WEAK) */ 
dot.tenant_name, 
dou.zone, 
dou.svr_ip observer,
cotl.replica_type, 
dou.max_cpu cpu, 
concat(round(dou.memory_size/1024/1024/1024,0), 'G') memory_size, 
concat(round(dou.log_disk_size/1024/1024/1024,0), 'G') log_disk_size, 
concat(round(sum(cotr.data_size)/1024/1024/1024, 2), 'G') data_size, 
concat(round(sum(cotr.required_size)/1024/1024/1024, 2), 'G') required_size, 
dot.unit_num 
from dba_ob_tenants dot, 
dba_ob_units dou, 
cdb_ob_table_locations cotl, 
cdb_ob_tablet_replicas cotr 
where tenant_name = 'SYS'
and cotl.role = 'leader' --是否只查询主副本
and dot.tenant_id = dou.tenant_id 
and dot.tenant_id = cotl.tenant_id 
and dou.svr_ip = cotl.svr_ip 
and cotl.tenant_id = cotr.tenant_id 
and cotl.svr_ip = cotr.svr_ip 
and cotl.ls_id = cotr.ls_id 
and cotl.tablet_id = cotr.tablet_id 
group by dot.tenant_id, cotl.svr_ip 
order by dot.tenant_id, zone, cotl.svr_ip;

查询数据库信息及空间

select *+ READ_CONSISTENCY(WEAK) */ 
dod.database_name, 
dod.in_recyclebin, 
dod.collation, 
dod.read_only, 
dod.comment, 
sum(case when dotl.table_type = 'USER TABLE' then 1 else 0 end) table_count, 
concat(round(sum(dotr.data_size)/1024/1024/1024, 2), 'G') datasize_primary_replica 
concat(round(sum(dotr.required_size)/1024/1024/1024, 2), 'G') requiredsize_primary_replica 
from dba_ob_databases dod, 
dba_ob_table_locations dotl, 
dba_ob_tablet_replicas dotr 
where dod.database_name = 'xxx' 
and dotl.role = 'leader' --是否只查询主副本
and dod.database_name = dotl.database_name 
and dotl.ls_id = dotr.ls_id 
and dotl.svr_ip = dotr.svr_ip 
and dotl.tablet_id = dotr.tablet_id 
group by dod.database_name;

查看表信息及空间大小

select *+ READ_CONSISTENCY(WEAK) */ 
dotl.database_name,
dotl.table_name, 
concat(round(sum(dotr.data_size/1024/1024/1024), 2), 'G') datasize_primary_replica , 
concat(round(sum(dotr.required_size)/1024/1024/1024, 2), 'G') requiredsize_primary_replica 
from 
dba_ob_table_locations dotl, 
dba_ob_tablet_replicas dotr
where  dotl.ls_id = dotr.ls_id 
and dotl.svr_ip = dotr.svr_ip 
and dotl.tablet_id = dotr.tablet_id 
and dotl.table_type = 'USER TABLE' 
and dotl.database_name = 'db_xxx' 
and dotl.table_name = 'tab_xxx'
and dotl.role = 'leader' --是否只查询主副本
group by dotl.table_name

查看索引空间大小

select *+ READ_CONSISTENCY(WEAK) */ 
dotl.database_name,
dotl2.table_name, 
dotl.index_name,
concat(round(sum(dotr.data_size/1024/1024/1024), 2), 'G') datasize_primary_replica , 
concat(round(sum(dotr.required_size)/1024/1024/1024, 2), 'G') requiredsize_primary_replica 
from 
dba_ob_table_locations dotl, 
dba_ob_tablet_replicas dotr, 
dba_ob_table_locations dotl2 
where  dotl.ls_id = dotl2.ls_id 
and dotl.svr_ip = dotl2.svr_ip 
and dotl.role = dotl2.role 
and dotl.data_table_id = dotl2.table_id 
and dotl.ls_id = dotr.ls_id 
and dotl.svr_ip = dotr.svr_ip 
and dotl.tablet_id = dotr.tablet_id 
and dotl.table_type = 'INDEX' 
and dotl.index_name = 'idx_xxx'
and dotl.database_name = 'db_xxx' 
and dotl2.table_name = 'tab_xxx'
and dotl.role = 'leader' --是否只查询主副本
group by dotl.index_name;

查看表的LOB(TEXT)及空间大小

select *+ READ_CONSISTENCY(WEAK) */ 
dotl.database_name,
dotl2.table_name,
dotl.table_name lob_name,
concat(round(sum(dotr.data_size/1024/1024/1024), 2), 'G') datasize_primary_replica , 
concat(round(sum(dotr.required_size)/1024/1024/1024, 2), 'G') requiredsize_primary_replica 
from 
dba_ob_table_locations dotl, 
dba_ob_tablet_replicas dotr, 
dba_ob_table_locations dotl2 
where  dotl.ls_id = dotl2.ls_id 
and dotl.svr_ip = dotl2.svr_ip 
and dotl.role = dotl2.role 
and dotl.data_table_id = dotl2.table_id 
and dotl.ls_id = dotr.ls_id 
and dotl.svr_ip = dotr.svr_ip 
and dotl.tablet_id = dotr.tablet_id 
and dotl.table_type = 'LOB AUX TABLE' 
and dotl.database_name = 'db_xxx' 
and dotl2.table_name = 'tab_xxx'
and dotl.role = 'leader' --是否只查询主副本
group by dotl.table_name;

会话类

查看所有会话

select /*+ READ_CONSISTENCY(WEAK) */ * oceanbase.gv$ob_processlist;

查看活跃会话

select /*+ READ_CONSISTENCY(WEAK) */ * from oceanbase.gv$ob_processlist where command in ('Query');

查看所有行锁阻塞

select /*+ READ_CONSISTENCY(WEAK) */ 
concat(case block
when 0 then 'Holder:' 
when 1 then 'Waiter:'
end, op.svr_ip, ':', op.id) as sess,
ol.id1, ol.id2, ol.lmode, ol.request, ol.type, round(ol.ctime/1000/1000,0) ctime, op.info, op.command, if(op.sql_id = '', 'NULL', op.sql_id) sql_iddowner, do.object_name
from gv$ob_locks ol, gv$ob_processlist op, dba_objects do,
(select id1, id2, type from gv$ob_locks where type = 'TR' and block = 1) l 
where ol.id1 = l.id1 and ol.id2= l.id2
and ol.trans_id = op.trans_id
and ol.id1 = do.data_object_id
order by id1, id2, ctime desc, block asc;

其它类

查看表的副本分布

select /*+ READ_CONSISTENCY(WEAK) */  * from oceanbase.dba_ob_table_locations;

闪回查询

select * from test AS OF SNAPSHOT time_to_usec('2024-07-15 12:00:00') * 1000;

查看耗时操作

select /*+ READ_CONSISTENCY(WEAK) */  * from oceanbase.gv$session_longops;