OceanBase 4.X 日常运维 常用SQL

---- OceanBase 4.X 

---- 2024年8月23日10:12:14

2.x 、3.x  和 4.x性能视图改进

DBA VS CDB
DBA 看的是当前租户的视角。CDB看的是全局视角。
OB 对应的租户级别的数据字典中。CDB看全局过滤Tenant_ID 较方便。
OB特有的系统租户对应的数据字典只有 DBA_xxx 而无对应的CDB_xxx

复制代码
select * from oceanbase.DBA_OB_SERVERS;
select * from oceanbase.GV$OB_SERVERS;
select * from oceanbase.DBA_OB_TENANTS;
select * from oceanbase.CDB_OB_MAJOR_COMPACTION;
select * from oceanbase.__all_virtual_server_compaction_progress where status !='FINISH';
select * from oceanbase.__all_virtual_tablet_compaction_progress limit 100;
select * from oceanbase.CDB_OB_TABLET_CHECKSUM_ERROR_INFO limit 100;
select * from oceanbase.CDB_OB_COLUMN_CHECKSUM_ERROR_INFO limit 100;
select * from oceanbase.__all_virtual_compaction_diagnose_info limit 100;
select * from oceanbase.__all_server_event_history where gmt_create > ${from_time} and gmt_create < ${to_time} order by gmt_create desc;
SELECT * FROM oceanbase.__all_tablet_checksum WHERE tablet_id = 1 and ls_id = 1 order by gmt_create desc limit 3;
select * from oceanbase.__all_virtual_tablet_meta_table a join oceanbase.CDB_OB_MAJOR_COMPACTION b where a.tenant_id=b.TENANT_ID and a.compaction_scn<b.GLOBAL_BROADCAST_SCN limit 100;
select * from oceanbase.__all_virtual_dag_warning_history where gmt_create > ${from_time} and gmt_create < ${to_time} order by gmt_create desc;
select * from oceanbase.CDB_OB_TRANSFER_TASKS;
select * from oceanbase.CDB_OB_TRANSFER_TASK_HISTORY where gmt_create > ${from_time} and gmt_create < ${to_time} order by gmt_create desc;
select * from oceanbase.__all_virtual_ha_diagnose;
select * from oceanbase.DBA_OB_UNITS;
select * from oceanbase.CDB_OB_LS;
select *from oceanbase.__all_virtual_log_stat;
select * from oceanbase.__all_rootservice_event_history where gmt_create > ${from_time} and gmt_create < ${to_time} order by gmt_create desc;
select * from oceanbase.__all_virtual_obj_lock;
select * from oceanbase.__all_virtual_ddl_task_status;
select * from oceanbase.__all_virtual_ls_recovery_stat;
select * from oceanbase.CDB_OB_BALANCE_JOBS;
select * from oceanbase.__all_virtual_disk_stat;
select * from oceanbase.v$ob_ls_log_restore_status
select * from oceanbase.__all_virtual_log_restore_source;
select * from oceanbase.__all_virtual_obj_lock where op_type = 'OUT_TRANS_LOCK' and (tenant_id, owner_id) not in (select tenant_id,table_lock_owner_id as owner_id from oceanbase.__all_virtual_transfer_task union (select tenant_id, task_id as owner_id from oceanbase.__all_virtual_ddl_task_status)) order by create_timestamp asc;
select * from oceanbase.V$OB_ARCHIVE_DEST_STATUS;
select * from oceanbase.CDB_OB_LS_LOG_ARCHIVE_PROGRESS;
select * from oceanbase.CDB_OB_LS_REPLICA_TASKS;
select * from oceanbase.V$OB_LS_REPLICA_TAS K_PLAN;
select * from oceanbase.__all_virtual_tenant_memstore_info;
select * from oceanbase.__all_virtual_dag;
SELECT * FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE CTX_CREATE_TIME < date_sub(now(), INTERVAL 600 SECOND) AND ROLE = 'LEADER' AND ACTION = 'START';
SELECT * FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE LAST_REQUEST_TIME < date_sub(now(), INTERVAL 600 SECOND) AND ROLE = 'LEADER' AND ACTION <> 'START';
SELECT * FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE CTX_CREATE_TIME < date_sub(now(), INTERVAL 600 SECOND) AND STATE = 'INIT';
SELECT * FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE CTX_CREATE_TIME < date_sub(now(), INTERVAL 600 SECOND) AND (STATE = 'PREPARE' OR STATE = 'REDO COMPLETE' OR STATE ='PRECOMMIT');
select * from oceanbase.GV$OB_LOCKS;
select a.svr_ip, a.svr_port, a.tenant_id, a.ls_id, b.end_scn, a.unsubmitted_log_scn, a.pending_cnt from oceanbase.__all_virtual_replay_stat a join oceanbase.__all_virtual_log_stat b on a.svr_ip=b.svr_ip and a.svr_port=b.svr_port and a.tenant_id=b.tenant_id and a.ls_id = b.ls_id and a.role='FOLLOWER'
select * from oceanbase.__all_virtual_replay_stat;
select * from oceanbase.CDB_OB_ARCHIVE_DEST;
select * from oceanbase.CDB_OB_ARCHIVELOG;
select * from oceanbase.__all_virtual_archive_stat;
select * from oceanbase.CDB_OB_RESTORE_PROGRESS;
select * from oceanbase.CDB_OB_RESTORE_HISTORY where START_TIMESTAMP > ${from_time} and START_TIMESTAMP < ${to_time} order by START_TIMESTAMP desc;
select * from oceanbase.__all_virtual_ls_meta_table;
select * from oceanbase.__all_virtual_server_schema_info;
select * from oceanbase.__all_virtual_ls_restore_progress;
select * from oceanbase.cdb_ob_backup_tasks;
select * from oceanbase.__all_virtual_backup_schedule_task;
select * from oceanbase.CDB_OB_BACKUP_JOB_HISTORY where START_TIMESTAMP > ${from_time} and START_TIMESTAMP < ${to_time} order by START_TIMESTAMP desc;
select * from oceanbase.__all_virtual_backup_ls_task_history where gmt_create > ${from_time} and gmt_create < ${to_time} order by gmt_create desc;
select * from oceanbase.__all_virtual_backup_ls_task_info;
select *,LOG_DISK_IN_USE/LOG_DISK_SIZE as percentage from oceanbase.gv$ob_units order by percentage desc;
select *,(end_lsn-base_lsn)/1024/1024 as RECYCLE_GAP_MB from oceanbase.__all_virtual_log_stat order by RECYCLE_GAP_MB desc;
(SELECT TENANT_ID, LS_ID FROM oceanbase.GV$OB_LOG_STAT GROUP BY TENANT_ID, LS_ID) EXCEPT (SELECT TENANT_ID, LS_ID FROM oceanbase.GV$OB_LOG_STAT WHERE ROLE = 'LEADER');
SELECT * FROM oceanbase.GV$OB_LOG_STAT WHERE IN_SYNC='NO';
SELECT * FROM oceanbase.CDB_OB_LS_LOCATIONS WHERE REBUILD = "TRUE" limit 100;
SELECT * FROM oceanbase.GV$OB_LOG_STAT WHERE ROLE = 'LEADER' AND DEGRADED_LIST != '';
SELECT * FROM oceanbase.GV$OB_LOG_STAT WHERE ROLE = 'LEADER' AND (LENGTH(PAXOS_MEMBER_LIST) - LENGTH(REPLACE(PAXOS_MEMBER_LIST, ',', '')) + 1) != PAXOS_REPLICA_NUM;


cat /proc/`pidof observer`/task/*/stack|grep nfs
复制代码

 

---- 如有侵权,请联系删除

posted @   上帝_BayaiM  阅读(52)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示