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
---- 如有侵权,请联系删除
【欢迎关注公众号】:database运维
分类:
BayaiM_OceanBase
标签:
OceanBase
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库