对使用OB过程中常用的一些SQL进行了整理，对应的版本是 4.x，会持续的更新。后面也会输出一些 4.x 版本常用的操作。

集群信息

查看版本

show variables like 'version_comment';

查看集群ID和集群名

show parameters like '%cluster%';

查看当前服务器信息

select * from DBA_OB_SERVERS;

查看集群的zone信息

SELECT * FROM dba_ob_zones;

查看集群支持的字符集

select * from information_schema.collations;

租户信息

查看租户创建基本信息

show create tenant xxx;

查看对应 Unit 的配置。

SELECT * FROM oceanbase.dba_ob_units;

查看租户对应的资源池（可以加 tenant_id 筛选）。

SELECT * FROM oceanbase.dba_ob_resource_pools;

查看租户基本信息。

SELECT * FROM oceanbase.dba_ob_tenants;

当前集群内的租户

select tenant_id,tenant_name,primary_zone,compatibility_mode from oceanbase.__all_tenant;

RS 相关

切换rs leader

alter system switch rootservice leader zone='z1';

查看 RS 任务

select * from __all_rootservice_event_history order by 1 desc limit 10;

查看 rs 列表

show parameters like '%rootservice_list%';

查看 rs leader，WITH_ROOTSERVER=yes

SELECT * FROM oceanbase.DBA_OB_SERVERS;

资源分配

资源分配查询

服务器资源分配

select * from GV$OB_SERVERS;

各租户资源分配

select t1.name resource_pool_name, t2.`name` unit_config_name, 
t2.max_cpu, t2.min_cpu, 
round(t2.memory_size/1024/1024/1024,2) mem_size_gb,
round(t2.log_disk_size/1024/1024/1024,2) log_disk_size_gb, t2.max_iops, 
t2.min_iops, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,
t4.tenant_id, t4.tenant_name
from __all_resource_pool t1
join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id;

查看 observer 内存、磁盘配置大小

select zone,svr_ip,svr_port,name,value 
from __all_virtual_sys_parameter_stat 
where 
name in ('memory_limit','memory_limit_percentage','system_memory','datafile_size','datafile_disk_percentage') 
order by svr_ip,svr_port;

容量使用统计

默认情况下统计的是三/多副本的大小，可以通过增加 role 来获取单副本大小。

统计租户的大小

select t.tenant_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
-- and t1.role='leader'
group by t.tenant_name
order by 3 desc;

统计库的大小

select t1.database_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
-- and t1.role='leader'
and t.tenant_name='test1'
group by t1.database_name
order by 3 desc;

统计表/索引的大小

select t1.table_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
-- and t1.role='leader'
and t.tenant_name='test1'
and t1.database_name='sbtest'
and t1.table_name='sbtest1'
group by t1.table_name
order by 3 desc;

统计表对应的分区大小

select t1.table_name,t1.partition_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
and t1.role='leader'
and t.tenant_name='test1'
and t1.database_name='sbtest'
and t1.table_name='sbtest1_part'
group by t1.table_name,t1.partition_name;

内存占用

租户内存参数：

show parameters where name in ('memstore_limit_percentage','freeze_trigger_percentage');

租户内存持有：

select TENANT_ID,SVR_IP,SVR_PORT,HOLD/1024/1024/1024,FREE/1024/1024/1024 
from oceanbase.GV$OB_TENANT_MEMORY 
where tenant_id =1002;

租户内存模块占用：

select * from V$OB_MEMORY where tenant_id=1002;

memstore占用：

select * from V$OB_MEMSTORE where tenant_id=1002;

总体实际占用的memory 大小以及大小限制：

select * from oceanbase.GV$OB_SERVERS;

memory_limit字段代表实际的memory_limit大小。

MEM_CAPACITY 是 memory_limit - system_memory

• 内存资源：包括两个配置，MIN_MEMORY和MAX_MEMORY，他们含义如下：
• MIN_MEMORY：表示为租户分配的最小内存规格，observer上，所有租户的MIN_MEMORY的总和不能超过物理可用内存大小MEM_CAPACITY
• MAX_MEMORY：表示为租户分配的最大内存规格，observer上，所有租户的MAX_MEMORY的总和不能超过物理可用内存的超卖值：MEM_CAPACITY * resource_hard_limit