OB 常用 SQL（欢迎提问，定期更新）

obpilot · 2021 年8 月 17 日 17:46

[版本] 社区版和企业版都可以

[需求1] 查看机器资源状态

select a.zone,concat(a.svr_ip,’:’,a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free, round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb, usec_to_time(b.last_offline_time) last_offline_time, usec_to_time(b.start_service_time) start_service_time, b.status, usec_to_time(b.stop_time) stop_time, b.build_version from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) order by a.zone, a.svr_ip ; ±------±------------------±----------±---------±-------------±------------±---------------------------±---------------------------±-------±---------------------------±------------------------------------------------------------------------------------+ | zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | last_offline_time | start_service_time | status | stop_time | build_version | ±------±------------------±----------±---------±-------------±------------±---------------------------±---------------------------±-------±---------------------------±------------------------------------------------------------------------------------+ | zone1 | 11.166.85.38:2882 | 22 | 1 | 24 | 7 | 1970-01-01 08:00:00.000000 | 2021-08-10 04:01:20.307242 | active | 1970-01-01 08:00:00.000000 | 3.1.2_20210705184323-9ef0e2bedb953c5bf0a4a21d6419845d25e0d719(Jul 5 2021 19:13:56) | ±------±------------------±----------±---------±-------------±------------±---------------------------±---------------------------±-------±---------------------------±------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

备注：

cpu_total , mem_total_gb 表示该 OB 节点可分配的总资源。
cpu_free , mem_free_gb 表示该 OB 节点可分配的剩余资源。但是这个不是特别精确，因为有默认租户分配资源的时候 max_cpu 跟 min_cpu、max_memory 跟 min_memory 不对等。
status 表示节点状态。inactive，表示节点已经掉线或者进程退出了。节点掉线常见原因是节点时钟偏差过大、网络延时过大、clog空间盘满（used大于等于95%）。如果进程刚刚启动，则是进程在跟 rootserver 通信沟通。正常情况下启动几秒就能变 active，异常宕机后的再启动可能需要几十秒变 acitve，最长不超过2分钟。
start_service_time 表示节点开始提供服务时间。如果是默认值 1970-1-1 ，则表示进程还在应用clog、刷新schema等。通常如果要应用的clog不多的时候，这个几秒钟就好了。如果是此前内存中大量数据还没有合并落盘就宕机了，这个恢复时间就长一些，可能会要十几分钟。
stop_service_time 表示停止服务时间。如果是默认值 1970-1-1 ，表示没有停止服务。如果时间大于默认值（在当前时间附近），表示手动发起过 stop server 或 stop zone 命令。则需要手动发起 start server 或者 start zone 命令恢复服务。
只有这三个列都正常了，节点才是正常的。observer 重启过程请参考官网文档：OceanBase 企业级分布式关系数据库

obpilot · 2021 年8 月 17 日 17:52

[需求2] 查看机器资源分配细节

select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb, 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

;


+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 |          7 |          5 |       1 | zone1 | 11.166.85.38:2882 |         1 | sys         |
| mysql_pool         | my_unit_config   |       8 |       8 |          5 |          5 |    1002 | zone1 | 11.166.85.38:2882 |      1002 | obmysql     |
| my_pool2           | my_unit_config   |       8 |       8 |          5 |          5 |    1003 | zone1 | 11.166.85.38:2882 |      1003 | oboracle    |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
3 rows in set (0.01 sec)

备注：

默认情况下 sys_unit_config 的 min_cpu 和 min_memory 跟 max_cpu 和 max_memory 不相等，导致上面第一个需求查询里统计的剩余可用 cpu 和 memory 有少量的误差。这里先纠正过来： alter resource unit sys_unit_config min_cpu=5,min_memory='7G';
如果 tenant_name 是空（NULL），表示该资源池没有关联到租户。则这个资源池就没法被利用，需要删除（回收）掉。
每个租户在每个zone 只能有一个资源池，在不同的zone 可以分别有一个资源池，也可以共用一个资源池（资源规格一样）。不同 zone 有多个资源池的时候，每个资源池的资源规格可以不等。

obpilot · 2021 年8 月 17 日 17:56

[需求3] 查看 OB 集群发生了什么事件？

SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip,name3,value3,name4,value4

FROM __all_rootservice_event_history

WHERE 1 = 1   

 --  AND module IN ('daily_merge')

 -- AND module NOT IN ('leader_coordinator')

 -- and event not like '%finish_backup%'

ORDER BY gmt_create DESC

LIMIT 100;

OB 的事件类型非常多，涉及到集群节点掉线/重启、新增/删除、ddl、分区切换、分区迁移、转储/合并、备份/恢复等等。所以，OB不正常了，首先看[需求1] 那个 SQL和这个 SQL 。

obpilot · 2021 年8 月 17 日 18:01

[需求4] 观察OB 合并进度

SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip,name3,value3,name4,value4

FROM __all_rootservice_event_history

WHERE 1 = 1   

   AND module IN ('daily_merge')

 -- AND module NOT IN ('leader_coordinator')

 -- and event not like '%finish_backup%'

ORDER BY gmt_create DESC

LIMIT 100;


SELECT ZONE,svr_ip,major_version,ss_store_count,merged_ss_store_count,modified_ss_store_count,merge_start_time,merge_finish_time,merge_process

FROM __all_virtual_partition_sstable_image_info

order by major_version desc ;
+-------+--------------+---------------+----------------+-----------------------+-------------------------+----------------------------+----------------------------+---------------+
| ZONE  | svr_ip       | major_version | ss_store_count | merged_ss_store_count | modified_ss_store_count | merge_start_time           | merge_finish_time          | merge_process |
+-------+--------------+---------------+----------------+-----------------------+-------------------------+----------------------------+----------------------------+---------------+
| zone1 | 11.166.85.38 |            19 |           1843 |                  1843 |                     108 | 2021-08-17 02:00:21.176881 | 2021-08-17 02:03:16.836808 |            52 |
| zone1 | 11.166.85.38 |            18 |           1712 |                  1712 |                      92 | 2021-08-16 19:53:25.177119 | 2021-08-16 19:55:34.677565 |           100 |
+-------+--------------+---------------+----------------+-----------------------+-------------------------+----------------------------+----------------------------+---------------+
2 rows in set (0.05 sec)

备注：

先查合并的事件。集群会有一笔开始合并（merging）的事件。然后每个zone 会两笔开始合并（set_zone_merging 和 start_merge)和两笔结束合并（merge_succeed 和 all_partition_merged )的事件。然后集群层面结束合并（global_merged）的事件。
__all_virtual_partition_sstable_image_info 会记录最近2次的合并事件记录（开始和结束时间），包括正在合并中的版本算一次。

obpilot · 2021 年8 月 17 日 18:11

[Q5] 查看某个实例（租户）下库表分区主副本的位置和大小

SELECT  t.tenant_id, a.tenant_name, t.table_name, d.database_name,  tg.tablegroup_name , t.part_num , t2.partition_id, t2.ZONE, t2.svr_ip , round(t2.data_size/1024/1024/1024) data_size_gb
	, a.primary_zone	  , IF(t.locality = '' OR t.locality IS NULL, a.locality, t.locality) AS locality	
FROM oceanbase.__all_tenant AS a  
	JOIN oceanbase.__all_virtual_database AS d ON ( a.tenant_id = d.tenant_id  ) 
	JOIN oceanbase.__all_virtual_table AS t  ON (t.tenant_id = d.tenant_id AND t.database_id = d.database_id)	
	JOIN oceanbase.__all_virtual_meta_table t2 ON (t.tenant_id = t2.tenant_id AND (t.table_id=t2.table_id OR t.tablegroup_id=t2.table_id)  AND t2.ROLE IN (1) )
	LEFT JOIN oceanbase.__all_virtual_tablegroup AS tg ON (t.tenant_id = tg.tenant_id and  t.tablegroup_id = tg.tablegroup_id) 
WHERE a.tenant_id IN (1006 ) AND  t.table_type IN (3)  
-- AND d.database_name = 'T_FUND60PUB' 
-- and table_name in ('BMSQL_HISTORY')
ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
;

备注：

role : 1 表示主副本； 2 表示备副本
tablegroup_name 为空，表示没有用到表分组。

obpilot · 2021 年8 月 17 日 18:15

[Q6] 查看集群节点的可用空间大小和实际分配大小、剩余空间。

select svr_ip, round(total_size/1024/1024/1024) total_size_gb, round(free_size/1024/1024/1024) free_size_gb, round(used_size/1024/1024/1024) used_size_gb from __all_virtual_disk_stat;

+--------------+---------------+--------------+--------------+
| svr_ip       | total_size_gb | free_size_gb | used_size_gb |
+--------------+---------------+--------------+--------------+
| 11.166.85.38 |           100 |           96 |            4 |
+--------------+---------------+--------------+--------------+
1 row in set (0.00 sec)

备注：

通常合并之后这个视图数据才会更新。

obpilot · 2021 年8 月 17 日 18:19

[Q7] 查看某个租户近期执行过的 SQL

SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.client_Ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query_sql, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time, round(s.request_memory_used/1024/1024/1024,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql, TRANSACTION_HASH 
FROM gv$sql_audit s
WHERE 1=1  and s.tenant_id = 1003
-- and user_name='sys' 
-- AND ret_code = 0
--  AND query_sql  LIKE '%dual%'
-- and query_sql like '%TBTATRANSACCOUNTSYNC%' 
-- and query_sql like '%SELECT%'
ORDER BY request_time DESC
LIMIT 100;

备注：

默认集群的 SQL 审计是开启的。查看参数 enable_sql_audit值。如果关闭了，需要打开才能使用这个 SQL 查询。
ret_code 为0 表示 SQL 正常，如果业务 SQL 报错了，这里会记录错误号（小于0）。
视图的定义可以查看官网文档。这里给出的常用字段。包含影响行数、返回行数、执行时间、等待时间、排队时间等等。

obpilot · 2021 年8 月 17 日 18:24

[Q8] 查看租户的增量内存使用情况

SELECT tenant_id, ip, round(active/1024/1024/1024) active_gb, round(total/1024/1024/1024) total_gb, round(freeze_trigger/1024/1024/1024) freeze_trg_gb, round(mem_limit/1024/1024/1024) mem_limit_gb
    , freeze_cnt , round((active/freeze_trigger),2) freeze_pct, round(total/mem_limit, 2) mem_usage
FROM `gv$memstore`
WHERE tenant_id =1003
ORDER BY tenant_id, ip;

+-----------+--------------+-----------+----------+---------------+--------------+------------+------------+-----------+
| tenant_id | ip           | active_gb | total_gb | freeze_trg_gb | mem_limit_gb | freeze_cnt | freeze_pct | mem_usage |
+-----------+--------------+-----------+----------+---------------+--------------+------------+------------+-----------+
|      1003 | 11.166.85.38 |         0 |        0 |             2 |            4 |          0 |       0.13 |      0.05 |
+-----------+--------------+-----------+----------+---------------+--------------+------------+------------+-----------+
1 row in set (0.01 sec)

备注：

total_gb 表示总内存，active_gb 表示当前未转储的内存，freeze_trg_gb 表示内存转储的阈值，mem_limit_gb 表示能用的最大的增量内存（超过就报内存不足），freeze_cnt 表示转储次数。freeze_pct表示未转储的内存占转储阈值的比例，mem_usage 表示增量内存总的使用比例（到100% 就报内存不足）。

obpilot · 2021 年8 月 18 日 17:40

[Q9] 查看分区副本延时

select t.table_id, t.table_name,  p.partition_idx,  usec_to_time(min_log_service_ts), TIME_TO_SEC( now())-TIME_TO_SEC(usec_to_time(min_log_service_ts))  delta_time
from __all_virtual_partition_info p join __all_virtual_table t on (p.tenant_id=t.tenant_id and p.table_id=t.table_id)
  join __all_virtual_meta_table p2 on (p.tenant_id=p2.tenant_id and p.table_id=p2.table_id and p.partition_idx=p2.partition_id)
WHERE p.tenant_id=1003 and p2.role in (2) ;

备注：

role 是副本的角色，1 表示主副本， 2 表示备副本。
OB 里主备同步的粒度不是实例，而是分区级别（分区是表的子集）。
由于三副本是强同步（多数派成功即返回），备副本的延时通常都很小。

obpilot · 2021 年8 月 26 日 22:22

[Q10] 查看租户所有会话

select id, user, host,tenant,db, command, svr_ip, proxy_sessid,user_client_ip
from __all_virtual_processlist 
where proxy_sessid is not null 
;

备注：

proxy_sessid : obproxy的会话id。如果有值，表示这个会话是通过 OBPROXY 连接过来的。host 里的 IP 就是 OBPROXY 的 IP；如果为NULL，表示这个会话是直连 OBSERVER 的。
svr_ip 表示这个会话发生在哪个 OBSERVER 上。不管这个会话是通过 OBPROXY 还是直连 OBSERVER 的。
host 表示 OBSERVER 会话的客户端。如果这个会话是通过 OBPROXY 建立的，这个地址就是 OBPROXY 的地址；如果这个会话是直连 OBSERVER 的，这个地址就是发起直连会话的客户端地址。
user_client_ip 表示这个会话的实际客户端。如果这个会话是通过 OBPROXY 建立的，这个地址就是向 OBPROXY 发起连接的客户端；如果这个会话是直连 OBSERVER 的，则这个地址跟 host 是一致的。

有趣的是，有些客户端有多个 IP 的时候，host 和 user_client_ip会显示跟 OBPROXY 或 OBSERVER 能建立连接的那个 IP 。

Amber_Moe · 2021 年9 月 7 日 14:24

请问查看数据分布的语句该怎么写啊

obpilot · 2021 年9 月 7 日 14:48

上面的 [Q5] 查看某个实例（租户）下库表分区主副本的位置和大小就是看数据分布。

obpilot · 2021 年9 月 17 日 10:26

我这里的round 有四舍五入，可能会放大实际值。大家留意。 😓️

obpilot · 2021 年10 月 27 日 17:37

[Q10] 查看隐藏参数

select * from __all_virtual_sys_parameter_stat where name like '%_hash_area_size%';
+-------+----------+---------------+----------+-----------------+-----------+-------+--------------+--------------------------------------------------------------------------+-------------+----------+---------------+--------+---------+-------------------+
| zone  | svr_type | svr_ip        | svr_port | name            | data_type | value | value_strict | info                                                                     | need_reboot | section  | visible_level | scope  | source  | edit_level        |
+-------+----------+---------------+----------+-----------------+-----------+-------+--------------+--------------------------------------------------------------------------+-------------+----------+---------------+--------+---------+-------------------+
| zone1 | observer | 172.20.249.53 |     2882 | _hash_area_size | NULL      | 100M  | NULL         | size of maximum memory that could be used by HASH JOIN. Range: [4M,+∞)   |        NULL | OBSERVER | NULL          | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 172.20.249.56 |     3882 | _hash_area_size | NULL      | 100M  | NULL         | size of maximum memory that could be used by HASH JOIN. Range: [4M,+∞)   |        NULL | OBSERVER | NULL          | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 172.20.249.55 |     2882 | _hash_area_size | NULL      | 100M  | NULL         | size of maximum memory that could be used by HASH JOIN. Range: [4M,+∞)   |        NULL | OBSERVER | NULL          | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 172.20.249.55 |     3882 | _hash_area_size | NULL      | 100M  | NULL         | size of maximum memory that could be used by HASH JOIN. Range: [4M,+∞)   |        NULL | OBSERVER | NULL          | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 172.20.249.53 |     3882 | _hash_area_size | NULL      | 100M  | NULL         | size of maximum memory that could be used by HASH JOIN. Range: [4M,+∞)   |        NULL | OBSERVER | NULL          | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 172.20.249.56 |     2882 | _hash_area_size | NULL      | 100M  | NULL         | size of maximum memory that could be used by HASH JOIN. Range: [4M,+∞)   |        NULL | OBSERVER | NULL          | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+---------------+----------+-----------------+-----------+-------+--------------+--------------------------------------------------------------------------+-------------+----------+---------------+--------+---------+-------------------+
6 rows in set (0.178 sec)

snowofsummer · 2022 年3 月 25 日 08:27

收藏了

linlin.xll · 2022 年3 月 25 日 09:58

收藏了

不知道起啥名字 · 2022 年3 月 26 日 23:57

赞，已收藏

SuperTiger · 2022 年3 月 27 日 16:25

赞，已收藏

usehash · 2022 年4 月 14 日 09:40

666

SuperTiger · 2022 年4 月 14 日 10:29

继续学习~~~