oceanbase 常用视图查询记录

测试环境：1-1-1 OceanBase 企业版3.2.4.4

查看租户的 Context 和 Modudle 内存

SELECT
tenant_id,
svr_ip,
ctx_id,
ctx_name,
mod_name,
count,
round(HOLD / 1024 / 1024, 2) hold_mb,
round(used / 1024 / 1024, 2) used_mb
FROM
oceanbase.__all_virtual_memory_info
WHERE
tenant_id = 1
ORDER BY used DESC
LIMIT 10;

查看租户 memstore 内存使用情况

V3 版本用视图 gv$memstore

V4 该视图变成了gv$ob_memstore

SELECTtenant_id,ip ,round(active / 1024 / 1024, 2) active_mb,round(total / 1024 / 1024, 2) total_mb,round(freeze_trigger / 1024 / 1024, 2 ) freeze_trg_mb,round(mem_limit / 1024 / 1024, 2) mem_limit_mb,freeze_cnt
FROMoceanbase.gv$memstore m
WHEREm.tenant_id = 1
ORDER BY ip ;

查看表在 MemStore 中内存占用

V3 版本用视图 gv$memstore_info

V4 该视图变成了gv$ob_memstore_info

SELECTip, t.table_name,usec_to_time(base_version) base_version_time,round(used / 1024 / 1024, 1) AS used_mb,hash_items,btree_items,is_active
FROMgv$memstore_info m
JOIN gv$table t ON(m.tenant_id = t.tenant_idAND m.table_id = t.table_id)
WHEREt.tenant_id = 1AND t.database_name IN ('test')
ORDER BYm.ip,m.base_version DESC ;

每个表的分区在的转储和合并历史

SELECTm.svr_ip,t.table_name,TYPE,ACTION,usec_to_time(version) version,start_time,end_time ,macro_block_count blocks
FROMgv$merge_info m
JOIN gv$table t ON(m.tenant_id = t.tenant_idAND m.table_id = t.table_id)
WHEREm.tenant_id = 1AND t.table_name IN ('test_table')AND t.database_name = 'test'
ORDER BYt.table_name,start_time DESC ;


SELECTusec_to_time(request_time) req_time,tenant_name,svr_ip,user_name,db_name,ret_code,trace_id
FROMgv$sql_audit
WHEREtenant_id = 1AND user_name = 'root'AND ret_code IN (-4030,-4013)
ORDER BYrequest_id DESC
LIMIT 10
;

OB 的内存 Context 中，除了 MemStore 外，KVCache、PlanCache、WorkArea 等内存都可以通过视图查看


SELECTtenant_id,svr_ip,cache_name,priority,round(cache_size / 1024 / 1024, 2) cache_mb,round(cache_store_size / 1024 / 1024, 2) cache_store_mb,round(cache_map_size / 1024 / 1024, 2) cache_map_mb,kv_cnt,hit_ratio
FROMoceanbase.__all_virtual_kvcache_info
WHEREtenant_id = 1
ORDER BY total_hit_cnt DESC ;

SELECTtenant_id,svr_ip,sql_num,round(mem_used / 1024 / 1024, 2) mem_used_mb,round(mem_hold / 1024 / 1024, 2) mem_hold_mb,round(mem_limit / 1024 / 1024, 2) mem_limit_mb
FROMoceanbase.gv$plan_cache_stat
WHEREtenant_id = 1
ORDER BYmem_used DESC 
;