【产品名称】oceanbase
【产品版本】3.1.2
【问题描述】
- __all_tenant_meta_table 有副本的信息，但是table_id和__all_table_v2 中table_id不匹配，我没办法通过两表关联查询某张表的副本位置（_all_table_v2中有table_name,__all_tenant_meta_table 中没有table_name）
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 , t2.data_size
, 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 (1009 ) AND t.table_type IN (3)
AND d.database_name = 'TPCC'
ORDER BY t.tenant_id, tg.tablegroup_name, d.database_name, t.table_name, t2.partition_id
;
其他常用sql
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, 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
;
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
;
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 ('server')
-- AND module NOT IN ('leader_coordinator')
ORDER BY gmt_create DESC
LIMIT 100;
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,2) req_mem_mb, plan_type, is_executor_rpc, is_inner_sql, TRANSACTION_HASH
FROM gv$sql_audit s
WHERE s.tenant_id=1001 and user_name='tpcc' -- AND ret_code <0
AND query_sql LIKE '%dual%'
ORDER BY request_time DESC
LIMIT 100;
SELECT s.tenant_id, svr_ip,plan_Id,sql_id,TYPE, d.database_name , query_sql, first_load_time, avg_exe_usec, slow_count,executions, slowest_exe_usec
FROM `gv$plan_cache_plan_stat` s LEFT JOIN `gv$database` d ON (s.tenant_id =d.tenant_id AND s.db_id =d.database_id )
WHERE s.tenant_id=1002 -- 改成具体的 tenant_id
AND d.database_name LIKE 'sysbenchdb%'
ORDER BY avg_exe_usec desc;
SELECT *
FROM `gv$plan_cache_plan_explain`
WHERE tenant_id=1001 AND ip = '172.30.158.213' AND port=2882 AND plan_id=542 ;