oceanabse企业版运维常用sql总结

1.前提条件

需要登录root@sys并且进入oceanabses库
示例 ：mysql -h127.1  -P2881 -uroot@sys  -p*****  -A oceanbase
 

2.常用SQL总结

1.集群状态

SELECT
  svr_ip,
  CASE with_rootserver WHEN 1 THEN 'Yes' ELSE 'No' END AS rs,
  zone,
  CASE WHEN start_service_time = 0 THEN NULL ELSE CAST(usec_to_time(start_service_time) AS DATETIME) END AS start_service_time,
  CASE WHEN stop_time = 0 THEN NULL ELSE CAST(usec_to_time(stop_time) AS DATETIME) END AS stop_time,
  CASE WHEN last_offline_time = 0 THEN NULL ELSE CAST(usec_to_time(last_offline_time) AS DATETIME) END AS last_offline_time,
  status,
  SUBSTR(build_version, 1, INSTR(build_version, '-') - 1) AS build_version
FROM __all_server;

可以查看当前集群所有server状态信息，RS节点，启动时间，停止时间，OB版本等等。
 

2.合并状态，IDC信息等等

select * from __all_zone;

3.合并使用时间

SELECT 
    value1 AS data_version, 
    MIN(gmt_create) AS merge_begin_time,
    MAX(gmt_create) AS merge_end_time,
    TIMEDIFF(MAX(gmt_create), MIN(gmt_create)) AS merge_cost_time
FROM 
    __all_rootservice_event_history
WHERE 
    module IN ('major_freeze', 'freeze', 'daily_merge') 
    AND gmt_create >= '2019-09-01 00:00:00' 
    AND event NOT IN ('add_set_zone_merging_op', 'idle', 'indexing') 
    AND value1 REGEXP '^[0-9]*$'
GROUP BY 
    value1
ORDER BY 
    merge_begin_time DESC;

可以查看到合并版本，合并开始时间结束时间，使用时间等等

4.查看OB版本号

select version();
show variables like '%version%';
status

5.当前请求的server

select host_ip();

6.oracle租户查询分区表,分区键字段,分区键数值

SELECT tp.table_name,tp.partition_name,tp.tablespace_name,tp.PARTITION_POSITION,pkc.COLUMN_NAME,HIGH_VALUE FROM DBA_TAB_PARTITIONS tp INNER JOIN DBA_PART_KEY_COLUMNS pkc ON tp.table_name = pkc.NAME;

7.oracle租户查询无主键表

SELECT OWNER,TABLE_NAME FROM DBA_TABLES A WHERE NOT EXISTS (SELECT * FROM DBA_CONSTRAINTS B WHERE A.TABLE_NAME = B.TABLE_NAME AND B.CONSTRAINT_TYPE = 'P' );

8.查看每一个租户规格

SELECT
    t1.tenant_name,
    t2.name,
    t4.group_id AS unit_group,
    t4.unit_id,
    t5.zone,
    t5.svr_ip,
    t3.min_cpu,
    t3.max_cpu,
    ROUND(t3.min_memory / (1024 * 1024 * 1024)) AS min_memory,
    ROUND(t3.max_memory / (1024 * 1024 * 1024)) AS max_memory
FROM
    __all_tenant t1
JOIN
    __all_resource_pool t2 ON t2.tenant_id = t1.tenant_id
JOIN
    __all_unit_config t3 ON t3.unit_config_id = t2.unit_config_id
JOIN
    __all_unit t4 ON t4.resource_pool_id = t2.resource_pool_id
JOIN
    __all_server t5 ON t5.svr_ip = t4.svr_ip;

9.查找sql来源和计划

select * from gv$sql_audit where query_sql like '%order by%’;

10.查看集群所有节点CPU，磁盘使用情况

SELECT t1.svr_ip, t1.svr_port, 
       ROUND(t1.total_size/(1024*1024*1024), 2) AS total_GB,
       ROUND(t1.free_size/(1024*1024*1024), 2) AS free_GB,
       ROUND((t1.total_size-t1.free_size)/(1024*1024*1024), 2) AS used_GB,
       t2.with_rootserver,
       t3.cpu_total, t3.cpu_assigned AS cpu_ass, 
       t3.cpu_assigned_percent AS cpu_ass_pct,
       ROUND(t3.mem_total/1024/1024/1024, 2) AS mem_total_GB,
       ROUND(t3.mem_assigned/1024/1024/1024, 2) AS mem_ass_GB,
       t3.mem_assigned_percent AS mem_ass_pct,
       t3.unit_num
FROM __all_virtual_disk_stat AS t1
JOIN __all_server AS t2 ON t1.svr_ip = t2.svr_ip
JOIN __all_virtual_server_stat AS t3 ON t1.svr_ip = t3.svr_ip
ORDER BY t1.svr_ip ASC;  -- ASC表示升序排序