--- title: 常用视图汇总-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 常用视图汇总相关的常见问题和使用技巧，帮助您快速解决 常用视图汇总的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 常用视图汇总 更新时间：2025-08-25 01:51 ## 运维相关视图 下边展示了 OceanBase 数据库运维过程中的常用视图。 | 模块 | 视图名 | 视图说明 | 备注 | | --- | --- | --- | --- | | 集群运维 | SYS 租户：[oceanbase.DBA_OB_ZONES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219612) | 该视图用于展示所有 Zone 的信息。 | | 集群运维 | SYS 租户：[oceanbase.CDB_OB_LS_LOCATIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219626) | 该视图用于展示所有租户的日志流（ LS ）副本分布信息。 | 也可以通过该视图获取集群 Root Server 相关信息。 | | 集群运维 | SYS 租户：[oceanbase.DBA_OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219633) | 该视图用于展示所有 OBServer 节点的信息。 | | 集群运维 | - SYS 租户：[GV$OB_SERVER_SCHEMA_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219713) - MySQL 租户：[GV$OB_SERVER_SCHEMA_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219862) - Oracle 租户：[GV$OB_SERVER_SCHEMA_INFO](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219092) | 该视图用于展示所有 OBServer 节点上的 Schema 信息。 | 可以通过该视图获取 Schema 未刷新的租户个数。 | | 集群运维 | SYS 租户：[oceanbase.DBA_OB_ROOTSERVICE_EVENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219542) | 该视图用于展示 Root Service 的事件历史。 | | 集群运维 | - SYS 租户：information_schema.COLLATIONS - MySQL 租户：[information_schema.COLLATIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219999) | 该视图用于展示展示每个字符集的比对方法信息。 | | 合并管理 | - SYS 租户：[oceanbase.DBA_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219497) - MySQL 租户：[oceanbase.DBA_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219981) - Oracle 租户：[DBA_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219315) | 该视图用于展示当前租户的合并全局信息。 | | 合并管理 | SYS 租户：[oceanbase.CDB_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219476) | 该视图用于在系统租户下展示所有租户的合并全局信息。 | | 合并管理 | - SYS 租户：[GV$OB_TABLET_COMPACTION_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219775) - MySQL 租户：[GV$OB_TABLET_COMPACTION_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219844) - Oracle 租户：[GV$OB_TABLET_COMPACTION_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219096) | 该视图用于展示 TABLET 级 COMPACTION 的历史信息。 | | 租户管理 | - SYS 租户：[oceanbase.DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219511) - MySQL 租户：[oceanbase.DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220071) - Oracle 租户：[DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219430) | 该视图用于展示租户的基本信息。 | - 通过系统租户查询此视图时，可以展示所有租户的信息，包括系统租户、用户创建的租户（用户租户），以及 Meta 租户。用户可以根据 TENANT_TYPE 列来区分租户类型。 - 通过普通租户查询此视图时，只展示本租户的信息。 | | 租户管理 | SYS 租户：[oceanbase.DBA_OB_UNIT_CONFIGS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219545) | 该视图用于展示所有租户的 Unit 规格信息。 | | 租户管理 | SYS 租户：[oceanbase.DBA_OB_RESOURCE_POOLS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219522) | 该视图用于展示所有租户的资源池信息。 | 需要 join 其他视图 | | 租户管理 | SYS 租户：[oceanbase.DBA_OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219556) | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 | | 租户管理 | SYS 租户：[oceanbase.DBA_OB_TENANT_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219515) | 该视图用于展示租户级别的任务信息。 | | 租户管理 | SYS 租户：[oceanbase.CDB_OB_TABLET_REPLICAS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219518) | 该视图用于展示本租户的所有 TABLET 副本信息。 | 每个 TABLET 副本由联合键唯一确定：``。 | | 租户管理 | - SYS 租户：[oceanbase.DBA_OB_TABLET_REPLICAS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219508) - MySQL 租户：[oceanbase.DBA_OB_TABLET_REPLICAS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220055) - Oracle 租户：[DBA_OB_TABLET_REPLICAS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219365) | 该视图用于展示本租户的所有 TABLET 副本信息。 | 每个 TABLET 副本由联合键唯一确定：``。 | | 租户管理 | SYS 租户：[oceanbase.CDB_OB_TABLE_LOCATIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219660) | 该视图用于展示表或者分区所在的位置，包括：系统表、用户表、索引表等。 | | 租户管理 | - SYS 租户：[oceanbase.DBA_OB_TABLE_LOCATIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219488) - MySQL 租户：[oceanbase.DBA_OB_TABLE_LOCATIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220006) - Oracle 租户：[DBA_OB_TABLE_LOCATIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219444) | 该视图用于展示表或者分区所在的位置，包括：系统表、用户表、索引表等。 | | 资源管理 | SYS 租户：[oceanbase.DBA_OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219556) | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 | | 资源管理 | - SYS 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219785) - MySQL 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219799) - Oracle 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219063) | 该视图用于展示租户所在的 OBServer 节点的 Unit 信息（资源统计）。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 | | 资源管理 | SYS 租户：[GV$OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219766) | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | | 参数管理 | - SYS 租户：[GV$OB_PARAMETERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219758) - MySQL 租户：[GV$OB_PARAMETERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219806) - Oracle 租户：[GV$OB_PARAMETERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219082) | 该视图用于展示租户所在的所有 OBServer 节点上的配置项信息(集群/租户配置项)。 | | MySQL 数据库管理 | - SYS 租户：[oceanbase.DBA_OB_DATABASES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219470) - MySQL 租户：[oceanbase.DBA_OB_DATABASES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219923) - Oracle 租户：[DBA_OB_DATABASES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219286) | 展示租户的数据库元信息。 | | 用户及权限管理 | - SYS 租户：mysql.user - MySQL 租户：[mysql.user](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220082) | 该视图用于展示用户列表以及用户级别的权限信息（VIRTUAL_TABLE）。 | | 用户及权限管理 | - SYS 租户：mysql.db - MySQL 租户：[mysql.db](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219934) | 该视图用于展示数据库级别的权限信息。 | | 用户及权限管理 | Oracle 租户：[DBA_USERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219395) | 该视图用于展示数据库所有用户的信息。 | | 用户及权限管理 | Oracle 租户：[DBA_ROLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219447) | 该视图用于展示数据库中存在的所有角色。 | | 用户及权限管理 | - SYS 租户：[oceanbase.DBA_OBJECTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219512) - MySQL 租户：[oceanbase.DBA_OBJECTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220083) - Oracle 租户：[DBA_OBJECTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219200) | 该视图用于展示数据库所有数据库对象。 | | 用户及权限管理 | Oracle 租户：[DBA_SYS_PRIVS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219432) | 该视图用于展示授予用户和角色的系统特权。此视图不显示 `USERNAME` 列。 | | 用户及权限管理 | Oracle 租户：[DBA_ROLE_PRIVS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219223) | 该视图用于展示授予所有用户的角色和数据库中的角色。 | | 用户及权限管理 | Oracle 租户：[DBA_TAB_PRIVS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219217) | 该视图用于展示数据库中的所有对象授权情况。 | ## 集群运维相关视图查询示例 ### 查看集群名称与集群 ID #### 说明 仅 sys 租户下可以查看。 1. 使用 root 用户登录到集群的 sys 租户。 ```shell obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A ``` 2. 执行以下命令查询集群名称与集群 ID。 ```shell obclient> SELECT * FROM oceanbase.gv$ob_parameters WHERE name IN ('cluster', 'cluster_id'); ``` 返回结果如下所示。 ```shell +-----------------+----------+-------+---------+-----------+------------+-----------+---------+---------------------+----------+-------------------+ | SVR_IP | SVR_PORT | ZONE | SCOPE | TENANT_ID | NAME | DATA_TYPE | VALUE | INFO | SECTION | EDIT_LEVEL | +-----------------+----------+-------+---------+-----------+------------+-----------+---------+---------------------+----------+-------------------+ | xxx.xxx.xxx.xxx | 2882 | zone1 | CLUSTER | NULL | cluster_id | NULL | 10001 | ID of the cluster | OBSERVER | DYNAMIC_EFFECTIVE | | xxx.xxx.xxx.xxx | 2882 | zone1 | CLUSTER | NULL | cluster | NULL | test421 | Name of the cluster | OBSERVER | DYNAMIC_EFFECTIVE | +-----------------+----------+-------+---------+-----------+------------+-----------+---------+---------------------+----------+-------------------+ ``` ### 查看 Zone 信息 ``` 2. 执行以下命令查询 Zone 信息。 ```shell obclient> SELECT `zone`, `status`, `region`, `idc` FROM oceanbase.DBA_OB_ZONES; ``` 返回结果如下所示。 ```shell +-------+--------+----------------+-----+ | zone | status | region | idc | +-------+--------+----------------+-----+ | zone1 | ACTIVE | default_region | | +-------+--------+----------------+-----+ ``` ### 根据 IP 和端口查看 OBServer 信息 根据 IP 和端口查看 OBServer 信息，如 OBServer 节点所属的 Zone、是否存在 Root Service 服务、OBServer 节点上次下线的时间、OBServer 节点被 stop 的时间等。 ``` 2. 执行以下命令查看 OBServer 信息。 ```shell obclient> SELECT zone, svr_ip, svr_port, sql_port AS inner_port, with_rootserver,UPPER(`status`) AS `status`, build_version,UNIX_TIMESTAMP(NVL(`stop_time`, 0))*1000000 AS stop_time,UNIX_TIMESTAMP(NVL(`start_service_time`, 0))*1000000 AS start_service_time,UNIX_TIMESTAMP(NVL(`last_offline_time`, 0))*1000000 AS last_offline_time,UNIX_TIMESTAMP(NVL(`block_migrate_in_time`, 0))*1000000 AS block_migrate_in_time FROM oceanbase.DBA_OB_SERVERS WHERE svr_ip = 'xxx.xxx.xxx.xxx' AND svr_port = '2883'; ``` 返回结果如下所示。 ```shell +------+-----------------+----------+------------+-----------------+--------+--------------------------------------------------------------------------+-----------+-------------------------+-------------------+-----------------------+ | zone | svr_ip | svr_port | inner_port | with_rootserver | status | build_version | stop_time | start_service_time | last_offline_time | block_migrate_in_time | +------+-----------------+----------+------------+-----------------+--------+--------------------------------------------------------------------------+-----------+-------------------------+-------------------+-----------------------+ | z1 | xxx.xxx.xxx.xxx | 2883 | 12250 | YES | ACTIVE | 4.2.1.1_1-c6b473e82c0d499547947a564aa453906e427cb6(Oct 30 2023 14:09:44) | 0.000000 | 1698652721693777.000000 | 0.000000 | 0.000000 | +------+-----------------+----------+------------+-----------------+--------+--------------------------------------------------------------------------+-----------+-------------------------+-------------------+-----------------------+ ``` ### 查询 RS 历史事件信息 ``` 2. 执行以下命令查询 RS 历史事件信息。 ```shell obclient> SELECT /*+QUERY_TIMEOUT(60000000) */ timestamp as gmt_create, module, event, name1, value1, name2, value2, name3, value3, name4, value4, name5, value5, name6, value6, extra_info, rs_svr_ip, rs_svr_port FROM oceanbase.DBA_OB_ROOTSERVICE_EVENT_HISTORY; ``` 返回结果如下： ```shell +----------------------------+-----------------+------------------------+-----------+--------+------------------------+------------------+------------+---------------------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ | gmt_create | module | event | name1 | value1 | name2 | value2 | name3 | value3 | name4 | value4 | name5 | value5 | name6 | value6 | extra_info | rs_svr_ip | rs_svr_port | +----------------------------+-----------------+------------------------+-----------+--------+------------------------+------------------+------------+---------------------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ | 2023-12-08 16:00:08.420777 | ddl | parallel_create_table | ret | -5331 | tenant_id | 1006 | table_id | -1 | cost | 13507 | | | | | | xxx.xxx.xxx.xxx | 2883 | | 2023-12-08 16:00:10.657836 | ddl | parallel_create_table | ret | -5331 | tenant_id | 1006 | table_id | -1 | cost | 13134 | | | | | | xxx.xxx.xxx.xxx | 2883 | | 2023-12-08 16:00:11.311443 | ddl | parallel_create_table | ret | -5331 | tenant_id | 1006 | table_id | -1 | cost | 10058 | | | | | | xxx.xxx.xxx.xxx | 2883 | +----------------------------+-----------------+------------------------+-----------+--------+------------------------+------------------+------------+---------------------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ ``` ### 获取集群字符集 - sys 租户 ```shell obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A ``` 2. 执行以下命令获取集群字符集。 ```shell obclient> SELECT `collation_name` AS collation, `character_set_name` AS charset, `id`, `is_default` FROM information_schema.collations; ``` 返回结果如下： ```shell +-------------------------+--------------+------+------------+ | collation | charset | id | is_default | +-------------------------+--------------+------+------------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | | utf8mb4_bin | utf8mb4 | 46 | | | binary | binary | 63 | Yes | | gbk_chinese_ci | gbk | 28 | Yes | | gbk_bin | gbk | 87 | | | utf16_general_ci | utf16 | 54 | Yes | | utf16_bin | utf16 | 55 | | | utf8mb4_unicode_ci | utf8mb4 | 224 | | | utf16_unicode_ci | utf16 | 101 | | | gb18030_chinese_ci | gb18030 | 248 | Yes | | gb18030_bin | gb18030 | 249 | | | latin1_swedish_ci | latin1 | 8 | Yes | | latin1_bin | latin1 | 47 | | | gb18030_2022_bin | gb18030_2022 | 216 | | | gb18030_2022_chinese_ci | gb18030_2022 | 217 | Yes | | gb18030_2022_chinese_cs | gb18030_2022 | 218 | | | gb18030_2022_radical_ci | gb18030_2022 | 219 | | | gb18030_2022_radical_cs | gb18030_2022 | 220 | | | gb18030_2022_stroke_ci | gb18030_2022 | 221 | | | gb18030_2022_stroke_cs | gb18030_2022 | 222 | | +-------------------------+--------------+------+------------+ ``` - MySQL 租户 1. 使用 root 用户登录到集群的 MySQL 租户。 ```shell obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A ``` 返回结果如下： ``` ### 获取某个租户的降级日志流 - sys 租户 ``` 2. 执行以下命令获取某个租户的降级日志流。 ```shell obclient> SELECT a.tenant_id, ls_id, svr_ip, svr_port, role, arbitration_member, degraded_list FROM oceanbase.GV$OB_LOG_STAT a,oceanbase.DBA_OB_TENANTS b WHERE degraded_list <> '' AND a.tenant_id = b.tenant_id AND tenant_type != 'META' AND a.tenant_id = ; ```shell obclient> SELECT a.tenant_id, ls_id, svr_ip, svr_port, role, arbitration_member, degraded_list FROM oceanbase.GV$OB_LOG_STAT a,oceanbase.DBA_OB_TENANTS b WHERE degraded_list <> '' AND a.tenant_id = b.tenant_id AND a.tenant_id = ; ``` - Oracle 租户 1. 使用 sys 用户登录到集群的 Oracle 租户。 ```shell obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A ```shell obclient> SELECT a.tenant_id, ls_id, svr_ip, svr_port, role, arbitration_member, degraded_list FROM GV$OB_LOG_STAT a,DBA_OB_TENANTS b WHERE degraded_list <> '' AND a.tenant_id = b.tenant_id AND a.tenant_id = ; ``` ### 获取 OceanBase 集群的仲裁服务信息 ``` 2. 执行以下命令获取 OceanBase 集群的仲裁服务信息。 ```shell obclient> SELECT arbitration_service_key, arbitration_service, previous_arbitration_service, type FROM oceanbase.DBA_OB_ARBITRATION_SERVICE; ``` ### 获取租户的合并进度 - sys 租户 ``` 2. 执行以下命令获取租户的合并进度。 ```shell obclient> SELECT 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) FROM oceanbase.GV$OB_COMPACTION_PROGRESS WHERE TENANT_ID = AND COMPACTION_SCN = ; ``` 返回结果如下： ```shell +-------------------------------------------------------------------+ | 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) | +-------------------------------------------------------------------+ | 97.3011 | +-------------------------------------------------------------------+ ```shell obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A ``` 返回结果如下： ```shell +-------------------------------------------------------------------+ | 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) | +-------------------------------------------------------------------+ | 100.0000 | +-------------------------------------------------------------------+ ```shell obclient> SELECT 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) FROM GV$OB_COMPACTION_PROGRESS WHERE TENANT_ID = AND COMPACTION_SCN = ; ``` 返回结果如下： ```shell +-------------------------------------------------------------------+ | 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) | +-------------------------------------------------------------------+ | 100 | +-------------------------------------------------------------------+ ``` ### 查询租户列表 - sys 租户 ``` 2. 执行以下命令查询租户列表。 ```shell obclient> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE, PRIMARY_ZONE, LOCALITY, COMPATIBILITY_MODE, STATUS, 0 AS LOCKED, IN_RECYCLEBIN, TIMESTAMPDIFF(SECOND, CREATE_TIME, now()) AS exist_seconds FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_TYPE IN ('SYS', 'USER'); ``` 返回结果如下： ```shell +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | TENANT_ID | TENANT_NAME | TENANT_TYPE | PRIMARY_ZONE | LOCALITY | COMPATIBILITY_MODE | STATUS | LOCKED | IN_RECYCLEBIN | exist_seconds | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | 1 | sys | SYS | RANDOM | FULL{1}@zone1 | MYSQL | NORMAL | 0 | NO | 63064 | | 1002 | mysql001 | USER | zone1 | FULL{1}@zone1 | MYSQL | NORMAL | 0 | NO | 62739 | | 1008 | oracle001 | USER | zone1 | FULL{1}@zone1 | ORACLE | NORMAL | 0 | NO | 62457 | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ ``` 返回结果如下： ```shell +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | TENANT_ID | TENANT_NAME | TENANT_TYPE | PRIMARY_ZONE | LOCALITY | COMPATIBILITY_MODE | STATUS | LOCKED | IN_RECYCLEBIN | exist_seconds | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | 1002 | mysql001 | USER | zone1 | FULL{1}@zone1 | MYSQL | NORMAL | 0 | NO | 62904 | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ ```shell obclient> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE, PRIMARY_ZONE, LOCALITY, COMPATIBILITY_MODE, STATUS, 0 AS LOCKED, IN_RECYCLEBIN, EXTRACT(SECOND FROM (CURRENT_TIMESTAMP - CREATE_TIME)) AS exist_seconds FROM DBA_OB_TENANTS WHERE TENANT_TYPE IN ('SYS', 'USER'); ``` 返回结果如下： ```shell +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | TENANT_ID | TENANT_NAME | TENANT_TYPE | PRIMARY_ZONE | LOCALITY | COMPATIBILITY_MODE | STATUS | LOCKED | IN_RECYCLEBIN | EXIST_SECONDS | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | 1008 | oracle001 | USER | zone1 | FULL{1}@zone1 | ORACLE | NORMAL | 0 | NO | 29.272675 | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ ``` ### 查询 Unit 规格列表 ``` 2. 执行以下命令查询 Unit 规格列表。 ```shell obclient> SELECT UNIT_CONFIG_ID, NAME, MAX_CPU, MIN_CPU, MEMORY_SIZE AS MAX_MEMORY, MEMORY_SIZE AS MIN_MEMORY, LOG_DISK_SIZE, MAX_IOPS, MIN_IOPS, IOPS_WEIGHT FROM oceanbase.DBA_OB_UNIT_CONFIGS; ``` 返回结果如下： ```shell +----------------+-----------------+---------+---------+------------+------------+---------------+---------------------+---------------------+-------------+ | UNIT_CONFIG_ID | NAME | MAX_CPU | MIN_CPU | MAX_MEMORY | MIN_MEMORY | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT | +----------------+-----------------+---------+---------+------------+------------+---------------+---------------------+---------------------+-------------+ | 1 | sys_unit_config | 2 | 2 | 1073741824 | 1073741824 | 2147483648 | 9223372036854775807 | 9223372036854775807 | 2 | | 1001 | proxyunit | 1 | 1 | 2147483648 | 2147483648 | 6442450944 | 9223372036854775807 | 9223372036854775807 | 1 | | 1002 | box1 | 4 | 4 | 6442450944 | 6442450944 | 19327352832 | 9223372036854775807 | 9223372036854775807 | 4 | +----------------+-----------------+---------+---------+------------+------------+---------------+---------------------+---------------------+-------------+ ``` ### 查询资源池列表 ``` 2. 执行以下命令查询资源池列表。 ```shell obclient> SELECT /*+ QUERY_TIMEOUT(60000000) */ time_to_usec(t1.MODIFY_TIME) AS UPDATE_TIME, t1.RESOURCE_POOL_ID, t1.NAME, t1.UNIT_COUNT, t1.UNIT_CONFIG_ID, t1.ZONE_LIST, t1.TENANT_ID, t1.REPLICA_TYPE, t2.NAME AS UNIT_CONFIG_NAME, t2.MAX_CPU, t2.MIN_CPU, t2.MEMORY_SIZE, t2.MAX_IOPS, t2.MIN_IOPS, t2.LOG_DISK_SIZE FROM oceanbase.DBA_OB_RESOURCE_POOLS AS t1 JOIN oceanbase.DBA_OB_UNIT_CONFIGS AS t2 ON t1.UNIT_CONFIG_ID = t2.UNIT_CONFIG_ID; ``` 返回结果如下： ```shell +------------------+------------------+------------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------+---------------------+---------------------+---------------+ | UPDATE_TIME | RESOURCE_POOL_ID | NAME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | TENANT_ID | REPLICA_TYPE | UNIT_CONFIG_NAME | MAX_CPU | MIN_CPU | MEMORY_SIZE | MAX_IOPS | MIN_IOPS | LOG_DISK_SIZE | +------------------+------------------+------------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------+---------------------+---------------------+---------------+ | 1698652714078394 | 1 | sys_pool | 1 | 1 | z1 | 1 | FULL | sys_unit_config | 2 | 2 | 1073741824 | 9223372036854775807 | 9223372036854775807 | 2147483648 | | 1698652748386936 | 1001 | proxypool | 1 | 1001 | z1 | 1002 | FULL | proxyunit | 1 | 1 | 2147483648 | 9223372036854775807 | 9223372036854775807 | 6442450944 | | 1698652881756178 | 1002 | oracle_pool | 1 | 1002 | z1 | 1006 | FULL | box1 | 4 | 4 | 6442450944 | 9223372036854775807 | 9223372036854775807 | 19327352832 | | 1698652853889086 | 1003 | mysql_pool | 1 | 1002 | z1 | 1004 | FULL | box1 | 4 | 4 | 6442450944 | 9223372036854775807 | 9223372036854775807 | 19327352832 | | 1698652918569279 | 1004 | mysql_pool_lower | 1 | 1002 | z1 | 1008 | FULL | box1 | 4 | 4 | 6442450944 | 9223372036854775807 | 9223372036854775807 | 19327352832 | +------------------+------------------+------------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------+---------------------+---------------------+---------------+ ``` ### 查询 Unit 列表 ``` 2. 执行以下命令查询 Unit 列表。 ```shell obclient> SELECT /*+ QUERY_TIMEOUT(5000000) */ t1.UNIT_ID, t1.RESOURCE_POOL_ID, t1.ZONE, t1.SVR_IP, t1.SVR_PORT, t1.STATUS, t2.REPLICA_TYPE, t2.TENANT_ID, t3.TENANT_NAME, t2.NAME AS RESOURCE_POOL_NAME, t1.MIGRATE_FROM_SVR_IP, t1.MIGRATE_FROM_SVR_PORT, t1.MANUAL_MIGRATE FROM oceanbase.DBA_OB_UNITS AS t1 JOIN oceanbase.DBA_OB_RESOURCE_POOLS AS t2 ON t1.RESOURCE_POOL_ID = t2.RESOURCE_POOL_ID JOIN oceanbase.DBA_OB_TENANTS t3 ON t2.TENANT_ID = t3.TENANT_ID WHERE 1=1; ``` 返回结果如下： ```shell +---------+------------------+------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ | UNIT_ID | RESOURCE_POOL_ID | ZONE | SVR_IP | SVR_PORT | STATUS | REPLICA_TYPE | TENANT_ID | TENANT_NAME | RESOURCE_POOL_NAME | MIGRATE_FROM_SVR_IP | MIGRATE_FROM_SVR_PORT | MANUAL_MIGRATE | +---------+------------------+------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ | 1 | 1 | z1 | xxx.xxx.xxx.xxx | 2883 | ACTIVE | FULL | 1 | sys | sys_pool | NULL | NULL | NULL | | 1001 | 1001 | z1 | xxx.xxx.xxx.xxx | 2883 | ACTIVE | FULL | 1002 | obproxy | proxypool | NULL | NULL | NULL | | 1002 | 1002 | z1 | xxx.xxx.xxx.xxx | 2883 | ACTIVE | FULL | 1006 | oracle | oracle_pool | NULL | NULL | NULL | | 1003 | 1003 | z1 | xxx.xxx.xxx.xxx | 2883 | ACTIVE | FULL | 1004 | mysql | mysql_pool | NULL | NULL | NULL | | 1004 | 1004 | z1 | xxx.xxx.xxx.xxx | 2883 | ACTIVE | FULL | 1008 | mysql_lower | mysql_pool_lower | NULL | NULL | NULL | +---------+------------------+------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ ``` ### 查看租户数据量 - sys 租户 ``` 2. 执行以下命令查看租户数据量。 ```shell obclient> SELECT tenant_id, svr_ip, svr_port, round(SUM(data_size) / 1024 / 1024) data_size_mb, round(SUM(required_size) / 1024 / 1024) required_size_mb FROM oceanbase.cdb_ob_tablet_replicas WHERE tenant_id = 1002 GROUP BY tenant_id, svr_ip, svr_port ORDER BY tenant_id, svr_ip, svr_port; ``` 返回结果如下： ```shell +-----------+-----------------+----------+--------------+------------------+ | tenant_id | svr_ip | svr_port | data_size_mb | required_size_mb | +-----------+-----------------+----------+--------------+------------------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | 2 | 134 | +-----------+-----------------+----------+--------------+------------------+ ```shell obclient> SELECT svr_ip, svr_port, round(SUM(data_size) / 1024 / 1024) data_size_mb, round(SUM(required_size) / 1024 / 1024) required_size_mb FROM oceanbase.dba_ob_tablet_replicas GROUP BY svr_ip, svr_port ORDER BY svr_ip, svr_port; ``` 返回结果如下： ```shell +-----------------+----------+--------------+------------------+ | svr_ip | svr_port | data_size_mb | required_size_mb | +-----------------+----------+--------------+------------------+ | xxx.xxx.xxx.xxx | 2882 | 2 | 134 | +-----------------+----------+--------------+------------------+ ```shell obclient> SELECT svr_ip, svr_port, round(SUM(data_size) / 1024 / 1024) data_size_mb, round(SUM(required_size) / 1024 / 1024) required_size_mb FROM dba_ob_tablet_replicas GROUP BY svr_ip, svr_port ORDER BY svr_ip, svr_port; ``` 返回结果如下： ```shell +-----------------+----------+--------------+------------------+ | svr_ip | svr_port | data_size_mb | required_size_mb | +-----------------+----------+--------------+------------------+ | xxx.xxx.xxx.xxx | 2882 | 5 | 146 | +-----------------+----------+--------------+------------------+ ``` ### 查看租户表大小统计 - sys 租户 ``` 2. 执行以下命令查看租户表大小统计。 ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(50000000) */ a.tenant_id, a.svr_ip, a.svr_port, c.object_type, round(SUM(data_size) / 1024 / 1024) AS data_size_mb, round(SUM(required_size) / 1024 / 1024) AS required_size_mb FROM oceanbase.cdb_ob_table_locations a JOIN (SELECT tenant_id, tablet_id, svr_ip, svr_port, data_size, required_size FROM oceanbase.cdb_ob_tablet_replicas) b ON a.tenant_id = b.tenant_id AND a.tenant_id = 1002 -- AND a.database_name = 'test' AND a.tablet_id = b.tablet_id AND a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port JOIN oceanbase.cdb_objects c ON a.tenant_id = c.con_id AND a.table_id = c.object_id AND c.object_type = 'TABLE' -- AND c.object_name = 'mysql' GROUP BY a.tenant_id, a.svr_ip, a.svr_port, c.object_type ORDER BY a.tenant_id, a.svr_ip, a.svr_port; ``` 返回结果如下： ```shell +-----------+-----------------+----------+-------------+--------------+------------------+ | tenant_id | svr_ip | svr_port | object_type | data_size_mb | required_size_mb | +-----------+-----------------+----------+-------------+--------------+------------------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | TABLE | 2 | 104 | +-----------+-----------------+----------+-------------+--------------+------------------+ ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(50000000) */ a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name, round(SUM(data_size) / 1024 / 1024) AS data_size_mb, round(SUM(required_size) / 1024 / 1024) AS required_size_mb FROM oceanbase.dba_ob_table_locations a JOIN ( SELECT tablet_id, svr_ip, svr_port, data_size, required_size FROM oceanbase.dba_ob_tablet_replicas ) b ON a.tablet_id = b.tablet_id AND a.database_name = 'mysql' AND a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port JOIN oceanbase.dba_objects c ON a.table_id = c.object_id AND c.object_type = 'TABLE' -- AND c.object_name = 'TEST' GROUP BY a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name ORDER BY required_size_mb DESC; ``` 返回结果如下： ```shell +-----------------+----------+---------------+-------------+---------------+--------------+------------------+ | svr_ip | svr_port | database_name | object_type | object_name | data_size_mb | required_size_mb | +-----------------+----------+---------------+-------------+---------------+--------------+------------------+ | xxx.xxx.xxx.xxx | 2882 | mysql | TABLE | help_topic | 0 | 0 | | xxx.xxx.xxx.xxx | 2882 | mysql | TABLE | help_category | 0 | 0 | | xxx.xxx.xxx.xxx | 2882 | mysql | TABLE | help_keyword | 0 | 0 | | xxx.xxx.xxx.xxx | 2882 | mysql | TABLE | help_relation | 0 | 0 | +-----------------+----------+---------------+-------------+---------------+--------------+------------------+ ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(50000000) */ a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name, round(SUM(data_size) / 1024 / 1024) AS data_size_mb, round(SUM(required_size) / 1024 / 1024) AS required_size_mb FROM dba_ob_table_locations a JOIN ( SELECT tablet_id, svr_ip, svr_port, data_size, required_size FROM dba_ob_tablet_replicas ) b ON a.tablet_id = b.tablet_id AND a.database_name = 'SYS' AND a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port JOIN dba_objects c ON a.table_id = c.object_id AND c.object_type = 'TABLE' -- AND c.object_name = 'TEST' GROUP BY a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name ORDER BY required_size_mb DESC; ``` 返回结果如下： ```shell +-----------------+----------+---------------+-------------+-------------+--------------+------------------+ | SVR_IP | SVR_PORT | DATABASE_NAME | OBJECT_TYPE | OBJECT_NAME | DATA_SIZE_MB | REQUIRED_SIZE_MB | +-----------------+----------+---------------+-------------+-------------+--------------+------------------+ | xxx.xxx.xxx.xxx | 2882 | SYS | TABLE | TBL1 | 0 | 2 | | xxx.xxx.xxx.xxx | 2882 | SYS | TABLE | T1 | 0 | 0 | | xxx.xxx.xxx.xxx | 2882 | SYS | TABLE | TBL2 | 0 | 0 | | xxx.xxx.xxx.xxx | 2882 | SYS | TABLE | T2_F_HRC | 0 | 0 | +-----------------+----------+---------------+-------------+-------------+--------------+------------------+ ``` ### 查询租户 partition/leader 分布情况 ``` 2. 执行以下命令查询租户 partition/leader 分布情况。 ```shell obclient> SELECT zone, svr_ip, role, COUNT(1) cnt FROM oceanbase.cdb_ob_table_locations WHERE tenant_id = 1012 GROUP BY svr_ip, role ORDER BY 1, 3 DESC; ``` ### 查询租户 RS 任务列表，用于租户运维后判断内核任务是否完成 ``` 2. 执行以下命令查询租户 RS 任务列表，用于租户运维后判断内核任务是否完成。 ```shell obclient> SELECT TENANT_ID, JOB_ID, JOB_TYPE, JOB_STATUS, PROGRESS FROM oceanbase.DBA_OB_TENANT_JOBS WHERE TENANT_ID = AND JOB_TYPE = 'ALTER_TENANT_LOCALITY' ORDER BY JOB_ID DESC LIMIT 1; ``` #### 注意 JOB_TYPE 只有 ALTER_TENANT_LOCALITY（修改租户 locality，新增副本和删除副本场景遇到） 和 SHRINK_RESOURCE_POOL_UNIT_NUM（减少 unit 数量） 两种。 ### SYS 租户下查询所有租户的合并状态 ``` 2. SYS 租户下查询所有租户的合并状态。 ```shell obclient> SELECT tenant_id, global_broadcast_scn AS broadcast_scn, is_error AS error, status, frozen_scn, last_scn, is_suspended AS suspend, info, start_time, last_finish_time FROM oceanbase.CDB_OB_MAJOR_COMPACTION; ``` 返回结果如下： ```shell +-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ | tenant_id | broadcast_scn | error | status | frozen_scn | last_scn | suspend | info | start_time | last_finish_time | +-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ | 1 | 1703095200994030132 | NO | IDLE | 1703095200994030132 | 1703095200994030132 | NO | | 2023-12-21 02:00:01.011792 | 2023-12-21 02:01:42.380131 | | 1001 | 1703095200408737206 | NO | IDLE | 1703095200408737206 | 1703095200408737206 | NO | | 2023-12-21 02:00:00.425185 | 2023-12-21 02:01:01.832624 | | 1002 | 1703095203592460265 | NO | IDLE | 1703095203592460265 | 1703095203592460265 | NO | | 2023-12-21 02:00:03.611772 | 2023-12-21 02:00:54.493800 | | 1007 | 1703095202034212311 | NO | IDLE | 1703095202034212311 | 1703095202034212311 | NO | | 2023-12-21 02:00:02.050003 | 2023-12-21 02:01:43.469878 | | 1008 | 1703095200122923223 | NO | IDLE | 1703095200122923223 | 1703095200122923223 | NO | | 2023-12-21 02:00:00.143240 | 2023-12-21 02:01:41.155777 | +-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ ``` ### 普通租户下查询当前租户的合并状态 - MySQL 租户 ``` 2. 普通租户下查询当前租户的合并状态。 ```shell obclient> SELECT global_broadcast_scn AS broadcast_scn, is_error AS error, status, frozen_scn, last_scn, is_suspended AS suspend, info, start_time, last_finish_time FROM oceanbase.DBA_OB_MAJOR_COMPACTION; ``` 返回结果如下： ```shell +---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ | broadcast_scn | error | status | frozen_scn | last_scn | suspend | info | start_time | last_finish_time | +---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ | 1703095203592460265 | NO | IDLE | 1703095203592460265 | 1703095203592460265 | NO | | 2023-12-21 02:00:03.611772 | 2023-12-21 02:00:54.493800 | +---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ ```shell obclient> SELECT global_broadcast_scn AS broadcast_scn, is_error AS error, status, frozen_scn, last_scn, is_suspended AS suspend, info, start_time, last_finish_time FROM DBA_OB_MAJOR_COMPACTION; ``` 返回结果如下： ```shell +---------------------+-------+--------+---------------------+---------------------+---------+------+------------------+------------------+ | BROADCAST_SCN | ERROR | STATUS | FROZEN_SCN | LAST_SCN | SUSPEND | INFO | START_TIME | LAST_FINISH_TIME | +---------------------+-------+--------+---------------------+---------------------+---------+------+------------------+------------------+ | 1703095200122923223 | NO | IDLE | 1703095200122923223 | 1703095200122923223 | NO | NULL | 1703095200143240 | 1703095301155777 | +---------------------+-------+--------+---------------------+---------------------+---------+------+------------------+------------------+ ``` ### 查询所有租户的 tablet 转储历史信息 ``` 2. 执行以下命令查询所有租户的 tablet 转储历史信息。 ```shell obclient> SELECT tenant_id, MIN(unix_timestamp(`start_time`)*1000000) AS min_start_time, MAX(unix_timestamp(`finish_time`)*1000000) AS max_finish_time, SUM(occupy_size) AS occupy_size, SUM(total_row_count) AS total_row_count, COUNT(1) AS tablet_count FROM oceanbase.GV$OB_TABLET_COMPACTION_HISTORY WHERE type = 'MINI_MERGE' AND unix_timestamp(`finish_time`)*1000000 > AND unix_timestamp(`finish_time`)*1000000 <= GROUP BY tenant_id; ``` 返回结果如下: ```shell +-----------+-------------------------+-------------------------+-------------+-----------------+--------------+ | tenant_id | min_start_time | max_finish_time | occupy_size | total_row_count | tablet_count | +-----------+-------------------------+-------------------------+-------------+-----------------+--------------+ | 1 | 1703065600886429.000000 | 1703095296923109.000000 | 56086169 | 529977 | 1058 | | 1001 | 1703065963530567.000000 | 1703144147436822.000000 | 194153621 | 2133381 | 960 | | 1002 | 1703065973310021.000000 | 1703095249856555.000000 | 12448639 | 93092 | 650 | | 1007 | 1703067112131041.000000 | 1703144629036419.000000 | 194035067 | 2132986 | 965 | | 1008 | 1703066254978307.000000 | 1703095291450918.000000 | 18040452 | 129623 | 656 | +-----------+-------------------------+-------------------------+-------------+-----------------+--------------+ ``` ### 查询 unit 迁移情况 ``` 2. 执行以下命令查询 unit 迁移情况。 ``` 返回结果如下： ```shell +---------+------------------+-------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ | UNIT_ID | RESOURCE_POOL_ID | ZONE | SVR_IP | SVR_PORT | STATUS | REPLICA_TYPE | TENANT_ID | TENANT_NAME | RESOURCE_POOL_NAME | MIGRATE_FROM_SVR_IP | MIGRATE_FROM_SVR_PORT | MANUAL_MIGRATE | +---------+------------------+-------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ | 1 | 1 | zone1 | xxx.xxx.xxx.xxx | 2882 | ACTIVE | FULL | 1 | sys | sys_pool | NULL | NULL | NULL | | 1001 | 1001 | zone1 | xxx.xxx.xxx.xxx | 2882 | ACTIVE | FULL | 1002 | mysql001 | mysql_pool_01 | NULL | NULL | NULL | | 1002 | 1002 | zone1 | xxx.xxx.xxx.xxx | 2882 | ACTIVE | FULL | 1008 | oracle001 | oracle_pool_01 | NULL | NULL | NULL | +---------+------------------+-------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ ``` ### 统计未使用的 unit ``` 2. 执行以下命令统计未使用的 unit。 ```shell obclient> SELECT /*+ QUERY_TIMEOUT(5000000) */ t1.UNIT_ID, t1.RESOURCE_POOL_ID, t1.ZONE, t1.SVR_IP, t1.SVR_PORT, t1.STATUS, t2.REPLICA_TYPE, NULL AS TENANT_ID, NULL AS TENANT_NAME, t2.NAME AS RESOURCE_POOL_NAME, t2.MODIFY_TIME AS RESOURCE_POOL_UPDATE_TIME, t1.MIGRATE_FROM_SVR_IP, t1.MIGRATE_FROM_SVR_PORT, t1.MANUAL_MIGRATE FROM oceanbase.DBA_OB_UNITS AS t1 JOIN oceanbase.DBA_OB_RESOURCE_POOLS AS t2 ON t1.RESOURCE_POOL_ID = t2.RESOURCE_POOL_ID WHERE t2.TENANT_ID IS NULL; ``` ### 统计租户资源（CPU/内存/磁盘） ``` 2. 执行以下命令统计租户资源。 ```shell obclient> SELECT COALESCE(t1.TENANT_ID, -1) as TENANT_ID, TENANT_NAME, SUM(MAX_CPU) AS MAX_CPU, SUM(MIN_CPU) AS MIN_CPU, SUM(MAX_MEMORY) AS MAX_MEMORY, SUM(MIN_MEMORY) AS MIN_MEMORY, SUM(MAX_IOPS) AS MAX_IOPS, SUM(MIN_IOPS) AS MIN_IOPS, SUM(DATA_DISK_IN_USE) AS DATA_DISK_IN_USE, SUM(LOG_DISK_IN_USE) AS LOG_DISK_IN_USE FROM (SELECT T1.UNIT_ID, T1.SVR_IP, T1.SVR_PORT, T1.ZONE, T2.TENANT_ID, T1.MIN_CPU, T1.MAX_CPU, T1.MIN_MEMORY, T1.MAX_MEMORY, T1.MIN_IOPS, T1.MAX_IOPS, T1.IOPS_WEIGHT, T1.DATA_DISK_IN_USE, T1.LOG_DISK_IN_USE FROM (SELECT UNIT_ID, SVR_IP, SVR_PORT, ZONE, SUM(MIN_CPU) AS MIN_CPU, SUM(MAX_CPU) AS MAX_CPU, SUM(MEMORY_SIZE) AS MIN_MEMORY, SUM(MEMORY_SIZE) AS MAX_MEMORY, SUM(MIN_IOPS) AS MIN_IOPS, SUM(MAX_IOPS) AS MAX_IOPS, SUM(IOPS_WEIGHT) AS IOPS_WEIGHT, SUM(DATA_DISK_IN_USE) AS DATA_DISK_IN_USE, SUM(LOG_DISK_IN_USE) AS LOG_DISK_IN_USE FROM oceanbase.GV$OB_UNITS GROUP BY UNIT_ID ) T1 JOIN oceanbase.DBA_OB_UNITS T2 ON T1.UNIT_ID = T2.UNIT_ID) t1 JOIN oceanbase.DBA_OB_TENANTS t2 ON t1.TENANT_ID = t2.TENANT_ID WHERE TENANT_TYPE IN ('SYS', 'USER') GROUP BY TENANT_ID; ``` 返回结果如下： ```shell +-----------+-------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+ | TENANT_ID | TENANT_NAME | MAX_CPU | MIN_CPU | MAX_MEMORY | MIN_MEMORY | MAX_IOPS | MIN_IOPS | DATA_DISK_IN_USE | LOG_DISK_IN_USE | +-----------+-------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+ | 1 | sys | 4 | 4 | 5368709120 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 35651584 | 430966295 | | 1002 | mysql001 | 1 | 1 | 5368709120 | 5368709120 | 10000 | 10000 | 127926272 | 851127046 | | 1008 | oracle001 | 1 | 1 | 5368709120 | 5368709120 | 10000 | 10000 | 127926272 | 854447918 | +-----------+-------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+ ``` ### 统计集群资源（CPU/内存/磁盘） ``` 2. 执行以下命令统计集群资源。 ```shell obclient> SELECT /* MONITOR_AGENT */ svr_ip, svr_port, cpu_capacity_max AS cpu_total, cpu_assigned_max AS cpu_assigned, round(mem_capacity / 1024 / 1024 / 1024) mem_total_gb, round(mem_assigned / 1024 / 1024 / 1024) mem_assigned_gb, round((cpu_assigned_max / cpu_capacity_max), 2) AS cpu_assigned_percent, round((mem_assigned / mem_capacity), 2) AS mem_assigned_percent, round(data_disk_capacity / 1024 / 1024 / 1024) data_disk_capacity_gb, round(data_disk_in_use / 1024 / 1024 / 1024) data_disk_in_use_gb, round( (data_disk_capacity - data_disk_in_use) / 1024 / 1024 / 1024 ) data_disk_free_gb, round(log_disk_capacity / 1024 / 1024 / 1024) log_disk_capacity_gb, round(log_disk_assigned / 1024 / 1024 / 1024) log_disk_assigned_gb, round(log_disk_in_use / 1024 / 1024 / 1024) log_disk_in_use_gb FROM gv$ob_servers ORDER BY svr_ip, svr_port; ``` 返回结果如下： ```shell +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ | svr_ip | svr_port | cpu_total | cpu_assigned | mem_total_gb | mem_assigned_gb | cpu_assigned_percent | mem_assigned_percent | data_disk_capacity_gb | data_disk_in_use_gb | data_disk_free_gb | log_disk_capacity_gb | log_disk_assigned_gb | log_disk_in_use_gb | +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ | xxx.xxx.xxx.xxx | 2882 | 64 | 6 | 34 | 15 | 0.09 | 0.44 | 100 | 0 | 100 | 167 | 28 | 23 | +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ ``` ### 统计数据库资源 ``` 2. 执行以下命令统计数据库资源。 ```shell obclient> SELECT t1.tenant_id, t1.database_name, t3.object_id AS database_id, round(SUM(t2.data_size) / 1024 / 1024) AS data_size_mb, round(SUM(t2.required_size) / 1024 / 1024) AS required_size_mb FROM ( SELECT tenant_id, database_name, table_id, tablet_id FROM cdb_ob_table_locations ) t1 LEFT JOIN ( SELECT tenant_id, tablet_id, svr_ip, svr_port, data_size, required_size FROM cdb_ob_tablet_replicas ) t2 ON t1.tenant_id = t2.tenant_id AND t1.tablet_id = t2.tablet_id LEFT JOIN ( SELECT con_id, object_name, object_id FROM cdb_objects WHERE object_type = 'DATABASE' ) t3 ON t1.tenant_id = t3.con_id AND t1.database_name = t3.object_name GROUP BY t1.database_name ORDER BY data_size_mb DESC; ``` 返回结果如下： ```shell +-----------+---------------+-------------+--------------+------------------+ | tenant_id | database_name | database_id | data_size_mb | required_size_mb | +-----------+---------------+-------------+--------------+------------------+ | 1 | oceanbase | 201001 | 23 | 844 | | 1 | mysql | 201003 | 0 | 0 | | 1 | test | 500001 | 0 | 2 | | 1002 | dbmwx | 500003 | 0 | 0 | | 1008 | SYS | 201006 | 0 | 2 | +-----------+---------------+-------------+--------------+------------------+ ``` ### 查询集群配置项（包括隐藏配置项） - sys 租户 ``` 2. 执行以下命令查询集群配置项。 ```shell obclient> SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM oceanbase.GV$OB_PARAMETERS WHERE SCOPE = 'CLUSTER' AND name = 'enable_sql_operator_dump'; ``` 返回结果如下： ```shell +-------+-----------------+----------+--------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+--------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | enable_sql_operator_dump | True | +-------+-----------------+----------+--------------------------+-------+ ``` 返回结果如下： ```shell obclient> SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM GV$OB_PARAMETERS WHERE SCOPE = 'CLUSTER' AND name = 'enable_sql_operator_dump'; ``` 返回结果如下： ``` ### 查询租户配置项（包括隐藏配置项） - sys 租户 ``` 2. 执行以下命令查询租户配置项。 ```shell obclient> SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM oceanbase.GV$OB_PARAMETERS WHERE SCOPE = 'TENANT' AND NAME = 'freeze_trigger_percentage'; ``` 返回结果如下： ```shell +-------+-----------------+----------+---------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+---------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | +-------+-----------------+----------+---------------------------+-------+ ``` 返回结果如下： ```shell +-------+-----------------+----------+---------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+---------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | +-------+-----------------+----------+---------------------------+-------+ ```shell obclient> SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM GV$OB_PARAMETERS WHERE SCOPE = 'TENANT' AND NAME = 'freeze_trigger_percentage'; ``` 返回结果如下： ``` ### 在 sys 租户查询所有租户的配置项（包括隐藏配置项） ``` 2. 在 sys 租户查询所有租户的配置项。 ```shell obclient> SELECT t.TENANT_ID, t.TENANT_NAME, p.ZONE, p.SVR_IP, p.SVR_PORT, p.NAME, p.VALUE FROM oceanbase.GV$OB_PARAMETERS p JOIN oceanbase.DBA_OB_TENANTS t ON p.TENANT_ID = t.TENANT_ID WHERE SCOPE = 'TENANT' AND TENANT_TYPE IN ('SYS', 'USER'); 返回结果如下： ```shell +-----------+-------------+------+-----------------+----------+---------------------------------------+-------------+ | TENANT_ID | TENANT_NAME | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-----------+-------------+------+-----------------+----------+---------------------------------------+-------------+ | 1 | sys | z1 | xxx.xxx.xxx.xxx | 2883 | ttl_thread_score | 0 | | 1 | sys | z1 | xxx.xxx.xxx.xxx | 2883 | enable_kv_ttl | False | | 1 | sys | z1 | xxx.xxx.xxx.xxx | 2883 | kv_ttl_history_recycle_interval | 7d | | 1 | sys | z1 | xxx.xxx.xxx.xxx | 2883 | kv_ttl_duty_duration | | | 1 | sys | z1 | xxx.xxx.xxx.xxx | 2883 | _ha_rpc_timeout | 0 | ....... | 1008 | mysql_lower | z1 | xxx.xxx.xxx.xxx | 12215 | cpu_quota_concurrency | 4 | | 1008 | mysql_lower | z1 | xxx.xxx.xxx.xxx | 12215 | enable_monotonic_weak_read | False | | 1008 | mysql_lower | z1 | xxx.xxx.xxx.xxx | 12215 | max_stale_time_for_weak_consistency | 5s | | 1008 | mysql_lower | z1 | xxx.xxx.xxx.xxx | 12215 | _hash_area_size | 32M | | 1008 | mysql_lower | z1 | xxx.xxx.xxx.xxx | 12215 | _sort_area_size | 32M | | 1008 | mysql_lower | z1 | xxx.xxx.xxx.xxx | 12215 | _publish_schema_mode | BEST_EFFORT | +-----------+-------------+------+-----------------+----------+---------------------------------------+-------------+ ``` ### 查询数据库列表 - sys 租户 ``` 2. 执行以下命令查询数据库列表。 ```shell obclient> SELECT o.CREATED AS GMT_CREATE, o.OBJECT_ID AS DATABASE_ID, d.DATABASE_NAME, c.ID AS COLLATION_TYPE,NULL AS PRIMARY_ZONE, 0 AS READ_ONLY FROM oceanbase.DBA_OB_DATABASES d JOIN oceanbase.DBA_OBJECTS o JOIN information_schema.collations c ON d.DATABASE_NAME = o.OBJECT_NAME AND d.COLLATION = c.COLLATION_NAME WHERE o.OBJECT_TYPE = 'DATABASE'; ``` 返回结果如下： ```shell +---------------------+-------------+--------------------+----------------+--------------+-----------+ | GMT_CREATE | DATABASE_ID | DATABASE_NAME | COLLATION_TYPE | PRIMARY_ZONE | READ_ONLY | +---------------------+-------------+--------------------+----------------+--------------+-----------+ | 2023-10-30 15:58:34 | 201001 | oceanbase | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201002 | information_schema | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201003 | mysql | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201004 | __recyclebin | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201005 | __public | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201006 | SYS | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201007 | LBACSYS | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 201008 | ORAAUDITOR | 45 | NULL | 0 | | 2023-10-30 15:58:34 | 500001 | test | 45 | NULL | 0 | +---------------------+-------------+--------------------+----------------+--------------+-----------+ ``` 返回结果如下： ``` ### 查看用户列表、全局权限授权情况 - sys 租户 ``` 2. 执行以下命令查看用户列表、全局权限授权情况。 ```shell obclient> SELECT user, (CASE account_locked WHEN 'Y' THEN 1 ELSE 0 END) AS account_locked, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, process_priv, grant_priv, index_priv, alter_priv, show_db_priv, super_priv, create_view_priv, show_view_priv, create_user_priv, password FROM mysql.user; ``` 返回结果如下： ```shell +--------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+ | user | account_locked | select_priv | insert_priv | update_priv | delete_priv | create_priv | drop_priv | process_priv | grant_priv | index_priv | alter_priv | show_db_priv | super_priv | create_view_priv | show_view_priv | create_user_priv | password | +--------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+ | root | 0 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | ORAAUDITOR | 1 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | *9311b0063b7a3b3323f83cd7319ba1e8b6f57e82 | | admin | 0 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | *4acfe3202a5ff5cf467898fc58aab1d615029441 | | proxyro | 0 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | *e9c2bcdc178a99b7b08dd25db58ded2ee5bff050 | | sysslaveuser | 0 | Y | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | *5b8a0e635515c4edee5e4dac8348731617d3c3fc | +--------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+ ``` 返回结果如下： ``` ### 查看数据库权限授权情况 - sys 租户 ``` 2. 执行以下命令查看数据库权限授权情况。 ```shell obclient> SELECT db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, index_priv, alter_priv, create_view_priv, show_view_priv FROM mysql.db; ``` 返回结果如下： ```shell +--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+ | db | user | select_priv | insert_priv | update_priv | delete_priv | create_priv | drop_priv | index_priv | alter_priv | create_view_priv | show_view_priv | +--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+ | test | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | oceanbase | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | mysql | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | information_schema | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | __recyclebin | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | __public | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | oceanbase | proxyro | Y | N | N | N | N | N | N | N | N | N | +--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+ ``` 返回结果如下： ``` ### 查看用户列表 #### 说明 仅 Oracle 租户下可以查看。 ``` 2. 执行以下命令查看用户列表。 ```shell obclient> SELECT USERNAME, CREATED, (CASE ACCOUNT_STATUS WHEN 'OPEN' THEN 0 ELSE 1 END) AS IS_LOCKED FROM DBA_USERS; ``` 返回结果如下： ```shell +--------------------+-----------+-----------+ | USERNAME | CREATED | IS_LOCKED | +--------------------+-----------+-----------+ | SYS | 30-OCT-23 | 0 | | LBACSYS | 30-OCT-23 | 1 | | ORAAUDITOR | 30-OCT-23 | 1 | +--------------------+-----------+-----------+ ``` ### 查看角色列表 ``` 2. 执行以下命令查看角色列表。 ```shell obclient> SELECT ROLE FROM DBA_ROLES; ``` 返回结果如下： ```shell +----------+ | ROLE | +----------+ | CONNECT | | RESOURCE | | DBA | | PUBLIC | +----------+ ``` ### 查看对象列表 ``` 2. 执行以下命令查看对象列表。 ```shell obclient> SELECT OBJECT_TYPE, OBJECT_NAME, OWNER AS SCHEMA_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE') AND OWNER NOT IN ('SYS', 'oceanbase'); ``` ### 查看系统权限授权情况 ``` 2. 执行以下命令查看系统权限授权情况。 ```shell obclient> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DBA'; ``` 返回结果如下： ```shell +-----------------------------+ | PRIVILEGE | +-----------------------------+ | CREATE SESSION | | CREATE TABLE | | CREATE ANY TABLE | ...... | DEBUG ANY PROCEDURE | | CREATE ANY CONTEXT | | DROP ANY CONTEXT | +-----------------------------+ ``` ### 查看角色授权情况 ``` 2. 执行以下命令查看角色授权情况。 ```shell obclient> SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = ; obclient> SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA' AND GRANTEE IN (SELECT USERNAME FROM DBA_USERS); obclient> SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA' AND GRANTEE IN (SELECT ROLE FROM DBA_ROLES); ``` ### 查看对象权限授权情况 ``` 2. 执行以下命令查看对象权限授权情况。 ```shell obclient> SELECT P.GRANTEE, P.OWNER, O.OBJECT_TYPE, O.OBJECT_NAME, P.PRIVILEGE FROM DBA_TAB_PRIVS P JOIN DBA_OBJECTS O ON P.OWNER = O.OWNER AND P.TABLE_NAME = O.OBJECT_NAME; ``` ## 监控相关视图 下边展示了 OceanBase 数据库监控中的常用视图。 | 模块 | 视图名 | 视图说明 | 备注 | | --- | --- | --- | --- | | 监控 | SYS 租户：[oceanbase.DBA_OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219633) | 该视图用于展示所有 OBServer 节点的信息。 | | 监控 | - SYS 租户：[GV$OB_KVCACHE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219730) - MySQL 租户：[GV$OB_KVCACHE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219833) - Oracle 租户：[GV$OB_KVCACHE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219148) | 该视图用于展示每台 OBServer 节点上的 KVCACHE 信息。 | | 监控 | - SYS 租户：[V$SYSSTAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219674) - MySQL 租户：[V$SYSSTAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219852) - Oracle 租户：[V$SYSSTAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219132) | 该视图用于展示当前服务器上租户级别的统计事件信息。 | | 监控 | SYS 租户：[oceanbase.CDB_TABLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219645) | 该视图用于展示所有租户的 TABLE 信息。 | | 监控 | - SYS 租户：[oceanbase.DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219511) - MySQL 租户：[oceanbase.DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220071) - Oracle 租户：[DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219430) | 该视图用于展示租户的基本信息。 | - 通过系统租户查询此视图时，可以展示所有租户的信息，包括系统租户、用户创建的租户（用户租户），以及 Meta 租户。用户可以根据 TENANT_TYPE 列来区分租户类型。 - 通过普通租户查询此视图时，只展示本租户的信息。 | | 监控 | - SYS 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219789) - MySQL 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219868) - Oracle 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219151) | 该视图用于展示租户所在的所有 OBServer 节点的会话信息。 | | 监控 | - SYS 租户：[V$OB_PLAN_CACHE_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219667) - MySQL 租户：[V$OB_PLAN_CACHE_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219850) - Oracle 租户：[V$OB_PLAN_CACHE_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219090) | 该视图用于展示当前租户在当前 OBServer 节点的计划缓存整体的状态。 | | 监控 | - SYS 租户：[V$SYSTEM_EVENT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219716) - MySQL 租户：[V$SYSTEM_EVENT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219875) - Oracle 租户：[V$SYSTEM_EVENT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219094) | 该视图用于展示当前服务器上租户系统级别的一些等待事件，统计每一类等待事件的发生次数、等待时间、超时次数等。 | | 监控 | - SYS 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219785) - MySQL 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219799) - Oracle 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219063) | 该视图用于展示租户所在的 OBServer 节点的 Unit 信息（资源统计）。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 | | 监控 | SYS 租户：[GV$OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219766) | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_TENANT_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219515) | 该视图用于展示租户级别的任务信息。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_UNIT_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219547) | 该视图用于展示所有 UNIT 相关任务。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_SERVER_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219666) | 该视图用于展示所有 OBServer 节点相关任务。 | | 监控 | - SYS 租户：[GV$OB_SSTABLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219695) - MySQL 租户：[GV$OB_SSTABLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219865) - Oracle 租户：[GV$OB_SSTABLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219126) | 该视图用于展示每台 OBServer 节点上的各分区下的 MemTable 和 SSTable 信息。 | | 监控 | - SYS 租户：[GV$OB_LOG_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219765) - MySQL 租户：[GV$OB_LOG_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219870) - Oracle 租户：[GV$OB_LOG_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219122) | 该视图用于展示日志流 Palf 的状态。 | - 查询日志流是否有 Leader，Leader 所在的副本。 - 查询日志流的成员列表，Paxos 副本数。 - 查询副本同步状态。 - 查询日志流日志的可回收位点。 - 查询日志流允许提供日志消费服务的范围（包括 LSN/SCN）。 - 查询 Palf 的访问模式。 | | 监控 | SYS 租户：[oceanbase.CDB_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219602) | 该视图用于展示所有租户的 INDEXE 信息。 | | 监控 | - SYS 租户：[compact_trigger_ratio](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219673) - MySQL 租户：[GV$OB_MEMSTORE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219856) - Oracle 租户：[GV$OB_MEMSTORE](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219167) | 该视图用于展示所有服务器上所有租户的 Memtable 的内存使用状况。 | | 监控 | SYS 租户：[oceanbase.CDB_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219476) | 系统租户下展示所有租户的合并全局信息。 | | 监控 | - SYS 租户：[GV$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219770) - MySQL 租户：[GV$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219821) - Oracle 租户：[GV$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219135) | 该视图用于展示所有服务器上每个租户的内存使用状况。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_RESOURCE_POOLS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219522) | 该视图用于展示所有租户的资源池信息。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_UNIT_CONFIGS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219545) | 该视图用于展示所有租户的 Unit 规格信息。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219556) | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 | | 监控 | - SYS 租户：[v$latch](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219709) - MySQL 租户：[v$latch](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219807) | 该视图用于展示集群中所有 OBServer 节点的 Latch 信息视图。 | | 监控 | SYS 租户：[oceanbase.DBA_OB_ROOTSERVICE_EVENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219542) | 该视图用于展示 Root Service 的事件历史。 | ## 监控相关视图查询示例 ### 查看连接情况 - sys 租户 ``` 2. 执行以下命令查看连接情况。 ```shell obclient> SELECT t2.svr_ip, t2.svr_port, t1.tenant_name, coalesce(t2.active_cnt, 0) AS active_cnt, coalesce(t2.all_cnt, 0) AS all_cnt FROM ( SELECT tenant_name FROM oceanbase.dba_ob_tenants WHERE tenant_type <> 'META' ) t1 LEFT JOIN ( SELECT count( `state` = 'ACTIVE' OR NULL ) AS active_cnt, COUNT(1) AS all_cnt, tenant AS tenant_name, svr_ip, svr_port FROM oceanbase.gv$ob_processlist GROUP BY tenant, svr_ip, svr_port ) t2 ON t1.tenant_name = t2.tenant_name ORDER BY all_cnt DESC, active_cnt DESC, t2.svr_ip, t2.svr_port, t1.tenant_name; ``` 返回结果如下： ```shell +-----------------+----------+-------------+------------+---------+ | svr_ip | svr_port | tenant_name | active_cnt | all_cnt | +-----------------+----------+-------------+------------+---------+ | xxx.xxx.xxx.xxx | 2882 | sys | 1 | 1 | | xxx.xxx.xxx.xxx | 2882 | mysql001 | 0 | 1 | | xxx.xxx.xxx.xxx | 2882 | oracle001 | 0 | 1 | +-----------------+----------+-------------+------------+---------+ ``` 返回结果如下： ```shell +-----------------+----------+-------------+------------+---------+ | svr_ip | svr_port | tenant_name | active_cnt | all_cnt | +-----------------+----------+-------------+------------+---------+ | xxx.xxx.xxx.xxx | 2882 | mysql001 | 1 | 1 | +-----------------+----------+-------------+------------+---------+ ``` 2. 执行以下命令统计 cache_size。 ```shell obclient> SELECT t2.svr_ip, t2.svr_port, t1.tenant_name, coalesce(t2.active_cnt, 0) AS active_cnt, coalesce(t2.all_cnt, 0) AS all_cnt FROM ( SELECT tenant_name FROM dba_ob_tenants WHERE tenant_type <> 'META' ) t1 LEFT JOIN ( SELECT count( case when state = 'ACTIVE' OR state IS NULL then 1 end ) AS active_cnt, COUNT(1) AS all_cnt, tenant AS tenant_name, svr_ip, svr_port FROM gv$ob_processlist GROUP BY tenant, svr_ip, svr_port ) t2 ON t1.tenant_name = t2.tenant_name ORDER BY all_cnt DESC, active_cnt DESC, t2.svr_ip, t2.svr_port, t1.tenant_name; ``` 返回结果如下： ```shell +-----------------+----------+--------------+------------+---------+ | svr_ip | svr_port | tenant_name | active_cnt | all_cnt | +-----------------+----------+--------------+------------+---------+ | xxx.xxx.xxx.xxx | 2882 | oracle001 | 1 | 1 | +-----------------+----------+--------------+------------+---------+ ``` ### 判断 server 是否是 RS 所在 ``` 2. 执行以下命令判断 server 是否是 RS 所在。 ```shell obclient> SELECT (case when with_rootserver='YES' then 1 else 0 end) as with_rootserver from oceanbase.DBA_OB_SERVERS where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882'; ``` 返回结果如下： ```shell +-----------------+ | with_rootserver | +-----------------+ | 1 | +-----------------+ ``` ### 统计 server 数量 ``` 2. 执行以下命令统计 server 数量。 ```shell obclient> SELECT /* MONITOR_AGENT */ group_concat(svr_ip SEPARATOR ',') as servers, status, count(1) as cnt from oceanbase.DBA_OB_SERVERS group by status; ``` 返回结果如下： ```shell +-----------------+--------+------+ | servers | status | cnt | +-----------------+--------+------+ | xxx.xxx.xxx.xxx | ACTIVE | 1 | +-----------------+--------+------+ ``` ### 统计 cache_size - sys 租户 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, cache_name, cache_size from oceanbase.GV$OB_KVCACHE where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882'; ``` 返回结果如下： ```shell +-----------+-----------------------+------------+ | tenant_id | cache_name | cache_size | +-----------+-----------------------+------------+ | 1 | schema_cache | 131087360 | | 1 | vtable_cache | 24968192 | | 1 | index_block_cache | 8322048 | ...... | 1007 | opt_column_stat_cache | 8322048 | | 1008 | opt_table_stat_cache | 6241280 | | 1008 | opt_column_stat_cache | 8322048 | +-----------+-----------------------+------------+ ``` 返回结果如下： ```shell +-----------+-----------------------+------------+ | tenant_id | cache_name | cache_size | +-----------+-----------------------+------------+ | 1002 | index_block_cache | 6241280 | | 1002 | user_block_cache | 18725888 | | 1002 | user_row_cache | 6241280 | | 1002 | fuse_row_cache | 6241280 | | 1002 | storage_meta_cache | 6241280 | | 1002 | opt_table_stat_cache | 6241280 | | 1002 | opt_column_stat_cache | 8322048 | | 1002 | opt_ds_stat_cache | 6241280 | +-----------+-----------------------+------------+ ``` 返回结果如下： ```shell +-----------+-----------------------+------------+ | TENANT_ID | CACHE_NAME | CACHE_SIZE | +-----------+-----------------------+------------+ | 1008 | index_block_cache | 6241280 | | 1008 | user_block_cache | 31210496 | | 1008 | user_row_cache | 6241280 | | 1008 | bf_cache | 6241280 | | 1008 | fuse_row_cache | 6241280 | | 1008 | storage_meta_cache | 6241280 | | 1008 | opt_table_stat_cache | 6241280 | | 1008 | opt_column_stat_cache | 8322048 | +-----------+-----------------------+------------+ ``` ### 统计 OceanBase 数据库系统信息（各类统计事件信息） - sys 租户 ``` 2. 执行以下命令统计 OceanBase 数据库系统信息。 ```shell obclient> SELECT /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from oceanbase.v$sysstat, oceanbase.DBA_OB_TENANTS where stat_id IN (10000, 10001, 10002, 10003, 10004, 10005, 10006, 140002, 140003, 140005, 140006, 40030, 80040, 80041, 130000, 130001, 130002, 130004, 20000, 20001, 20002, 30000, 30001, 30002, 30005, 30006, 30007, 30008, 30009, 30010, 30011, 30012, 30013, 40000, 40001, 40002, 40003, 40004, 40005, 40006, 40007, 40008, 40009, 40010, 40011, 40012, 40018, 40019, 50000, 50001, 60087, 50004, 50005, 50008, 50009, 50010, 50011, 50037, 50038, 60000, 60001, 60002, 60003, 60004, 60005, 60019, 60020, 60021, 60022, 60023, 60024, 80057, 120000, 120001, 120009, 120008) and (con_id > 1000 or con_id = 1) and class < 1000; ``` 返回结果如下： ```shell +-----------+---------+-------------+ | tenant_id | stat_id | value | +-----------+---------+-------------+ | 1 | 10000 | 3484431 | | 1 | 10000 | 3484431 | ...... | 1008 | 140006 | 0 | | 1008 | 140006 | 0 | +-----------+---------+-------------+ ``` 返回结果如下： ```shell +-----------+---------+------------+ | tenant_id | stat_id | value | +-----------+---------+------------+ | 1002 | 10000 | 696278 | | 1002 | 10001 | 282624028 | ...... | 1002 | 140003 | 898105344 | | 1002 | 140005 | 100 | | 1002 | 140006 | 0 | +-----------+---------+------------+ ``` 返回结果如下： ```shell +-----------+---------+------------+ | TENANT_ID | STAT_ID | VALUE | +-----------+---------+------------+ | 1008 | 10000 | 694680 | | 1008 | 10001 | 281931326 | ...... | 1008 | 140005 | 100 | | 1008 | 140006 | 0 | +-----------+---------+------------+ ``` ### 统计所有租户表数量 ``` 2. 执行以下命令统计租户表数量。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ con_id tenant_id, count(*) as cnt from oceanbase.CDB_TABLES group by con_id; ``` 返回结果如下： ```shell +-----------+------+ | tenant_id | cnt | +-----------+------+ | 1 | 263 | | 1001 | 242 | | 1002 | 168 | | 1007 | 242 | | 1008 | 164 | +-----------+------+ ``` ### session 信息 - sys 租户 ``` 2. 执行以下命令统计 session 信息。 ```shell obclient> SELECT /* MONITOR_AGENT */ case when cnt is null then 0 else cnt end as cnt, tenant_name, tenant_id from (select DBA_OB_TENANTS.tenant_name, DBA_OB_TENANTS.tenant_id, cnt from oceanbase.DBA_OB_TENANTS left join (select count(`state`='ACTIVE' OR NULL) as cnt, tenant as tenant_name from oceanbase.GV$OB_PROCESSLIST where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882' group by tenant) t1 on oceanbase.DBA_OB_TENANTS.tenant_name = t1.tenant_name where DBA_OB_TENANTS.tenant_type<>'META') t2; ``` 返回结果如下： ```shell +------+-------------+-----------+ | cnt | tenant_name | tenant_id | +------+-------------+-----------+ | 1 | sys | 1 | | 0 | mysql001 | 1002 | | 0 | oracle001 | 1008 | +------+-------------+-----------+ ``` 返回结果如下： ```shell +------+-------------+-----------+ | cnt | tenant_name | tenant_id | +------+-------------+-----------+ | 1 | mysql001 | 1002 | +------+-------------+-----------+ ```shell obclient> SELECT /* MONITOR_AGENT */ case when cnt is null then 0 else cnt end as cnt, tenant_name, tenant_id from (select DBA_OB_TENANTS.tenant_name, DBA_OB_TENANTS.tenant_id, cnt from DBA_OB_TENANTS left join (select count(case when state='ACTIVE' OR state is NULL then 1 end) as cnt, tenant as tenant_name from GV$OB_PROCESSLIST where svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882' group by tenant) t1 on DBA_OB_TENANTS.tenant_name = t1.tenant_name where DBA_OB_TENANTS.tenant_type<>'META') t2; ``` 返回结果如下： ```shell +------+-------------+-----------+ | CNT | TENANT_NAME | TENANT_ID | +------+-------------+-----------+ | 1 | oracle001 | 1008 | +------+-------------+-----------+ ``` ### 统计 plan cache - sys 租户 ``` 2. 执行以下命令统计 plan cache。 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, mem_used, access_count, hit_count from oceanbase.V$OB_PLAN_CACHE_STAT; ``` 返回结果如下： ```shell +-----------+-----------+--------------+-----------+ | tenant_id | mem_used | access_count | hit_count | +-----------+-----------+--------------+-----------+ | 1 | 304029750 | 4348561 | 4339967 | | 1001 | -3405329 | 3438287 | 3436147 | | 1002 | 130571960 | 824222 | 822976 | | 1007 | 21663406 | 3427634 | 3425441 | | 1008 | 125794689 | 855817 | 854276 | +-----------+-----------+--------------+-----------+ ``` 返回结果如下： ```shell +-----------+-----------+--------------+-----------+ | tenant_id | mem_used | access_count | hit_count | +-----------+-----------+--------------+-----------+ | 1002 | 130698360 | 824256 | 823009 | +-----------+-----------+--------------+-----------+ ```shell obclient> SELECT /* MONITOR_AGENT */ mem_used, access_count, hit_count from oceanbase.V$OB_PLAN_CACHE_STAT; ``` 返回结果如下： ```shell +-----------+--------------+-----------+ | MEM_USED | ACCESS_COUNT | HIT_COUNT | +-----------+--------------+-----------+ | 125921089 | 856104 | 854561 | +-----------+--------------+-----------+ ``` ### 统计系统事件（按租户分类） - sys 租户 ``` 2. 执行以下命令统计系统事件。 ```shell obclient> SELECT /* MONITOR_AGENT */ con_id tenant_id, sum(total_waits) as total_waits, sum(time_waited_micro) / 1000000 as time_waited from oceanbase.v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by tenant_id; ``` 返回结果如下： ```shell +-----------+-------------+---------------+ | tenant_id | total_waits | time_waited | +-----------+-------------+---------------+ | 1 | 1037896067 | 57941069.9614 | | 1001 | 71138436 | 712806.5033 | | 1002 | 145937284 | 1463698.3470 | | 1007 | 70913458 | 710554.5446 | | 1008 | 145530532 | 1459613.5522 | +-----------+-------------+---------------+ ``` 返回结果如下： ```shell +-----------+-------------+--------------+ | tenant_id | total_waits | time_waited | +-----------+-------------+--------------+ | 1002 | 145985517 | 1464182.2271 | +-----------+-------------+--------------+ ```shell obclient> SELECT /* MONITOR_AGENT */ con_id tenant_id, sum(total_waits) as total_waits, sum(time_waited_micro) / 1000000 as time_waited from v$system_event where v$system_event.wait_class <> 'IDLE' and (con_id > 1000 or con_id = 1) group by con_id; ``` 返回结果如下： ```shell +-----------+-------------+----------------+ | TENANT_ID | TOTAL_WAITS | TIME_WAITED | +-----------+-------------+----------------+ | 1008 | 145803716 | 1462353.823324 | +-----------+-------------+----------------+ ``` ### 统计系统事件（按 event 分类） - sys 租户 ```shell obclient> SELECT /* MONITOR_AGENT */ con_id tenant_id, CASE WHEN event_id = 10000 THEN 'INTERNAL' WHEN event_id = 13000 THEN 'SYNC_RPC' WHEN event_id = 14003 THEN 'ROW_LOCK_WAIT' WHEN ( event_id >= 10001 AND event_id <= 11006 ) OR ( event_id >= 11008 AND event_id <= 11011 ) THEN 'IO' WHEN event LIKE 'latch:%' THEN 'LATCH' ELSE 'OTHER' END event_group, SUM(total_waits) AS total_waits, SUM(time_waited_micro / 1000000) AS time_waited FROM oceanbase.v$system_event WHERE v$system_event.wait_class <> 'IDLE' AND ( con_id > 1000 OR con_id = 1 ) GROUP BY tenant_id, event_group ORDER BY tenant_id, event_group; ``` 返回结果如下： ```shell +-----------+---------------+-------------+-----------------+ | tenant_id | event_group | total_waits | time_waited | +-----------+---------------+-------------+-----------------+ | 1 | INTERNAL | 0 | 0.0000 | | 1 | IO | 32669413 | 8904.1464 | ...... | 1008 | OTHER | 6093536417 | 61118759.2330 | | 1008 | ROW_LOCK_WAIT | 0 | 0.0000 | | 1008 | SYNC_RPC | 12872 | 12.6659 | +-----------+---------------+-------------+-----------------+ ``` 返回结果如下： ```shell +-----------+---------------+-------------+---------------+ | tenant_id | event_group | total_waits | time_waited | +-----------+---------------+-------------+---------------+ | 1002 | INTERNAL | 0 | 0.0000 | | 1002 | IO | 223194 | 85.0179 | | 1002 | LATCH | 15996 | 2.3008 | | 1002 | OTHER | 6094008261 | 61123645.3157 | | 1002 | ROW_LOCK_WAIT | 0 | 0.0000 | | 1002 | SYNC_RPC | 610 | 2.1478 | +-----------+---------------+-------------+---------------+ ```shell obclient> SELECT /* MONITOR_AGENT */ t1.con_id tenant_id, t2.event_group, SUM(t1.total_waits) AS total_waits, SUM(t1.time_waited_micro / 1000000) AS time_waited FROM v$system_event t1, (SELECT con_id,(CASE WHEN event_id = 10000 THEN 'INTERNAL' WHEN event_id = 13000 THEN 'SYNC_RPC' WHEN event_id = 14003 THEN 'ROW_LOCK_WAIT' WHEN (event_id >= 10001 AND event_id <= 11006) OR ( event_id >= 11008 AND event_id <= 11011) THEN 'IO' WHEN event LIKE 'latch:%' THEN 'LATCH' ELSE 'OTHER' END) as event_group FROM v$system_event) t2 WHERE t1.con_id=t2.con_id AND t1.wait_class <> 'IDLE' AND (t1.con_id > 1000 OR t1.con_id = 1) GROUP BY t1.con_id, t2.event_group ORDER BY t1.con_id,t2.event_group; ``` 返回结果如下： ```shell +-----------+---------------+---------------+--------------------+ | TENANT_ID | EVENT_GROUP | TOTAL_WAITS | TIME_WAITED | +-----------+---------------+---------------+--------------------+ | 1008 | INTERNAL | 6106270942 | 61243999.100672 | | 1008 | IO | 73275251304 | 734927989.208064 | | 1008 | LATCH | 1941794159556 | 19475591714.013696 | | 1008 | OTHER | 372482527462 | 3735883945.140992 | | 1008 | ROW_LOCK_WAIT | 6106270942 | 61243999.100672 | | 1008 | SYNC_RPC | 6106270942 | 61243999.100672 | +-----------+---------------+---------------+--------------------+ ``` ### 统计磁盘 size ``` 2. 执行以下命令统计磁盘 size。 ```shell obclient> SELECT /* MONITOR_AGENT */ data_disk_capacity as total_size, (data_disk_capacity - data_disk_in_use) as free_size from oceanbase.GV$OB_SERVERS where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882'; ``` 返回结果如下： ```shell +--------------+--------------+ | total_size | free_size | +--------------+--------------+ | 107374182400 | 107002986496 | +--------------+--------------+ ``` ### 统计系统任务执行时间 ``` 2. 执行以下命令统计系统任务执行时间。 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, job_type as task_type, TIMESTAMPDIFF(SECOND, START_TIME, CURRENT_TIMESTAMP) as max_sys_task_duration_seconds, rs_svr_ip as svr_ip FROM oceanbase.DBA_OB_TENANT_JOBS WHERE job_status='INPROGRESS' AND rs_svr_ip= 'xxx.xxx.xx.xx' and rs_svr_port='2882' UNION SELECT tenant_id, job_type as task_type, TIMESTAMPDIFF(SECOND, START_TIME, CURRENT_TIMESTAMP) as max_sys_task_duration_seconds, rs_svr_ip as svr_ip FROM oceanbase.DBA_OB_UNIT_JOBS WHERE tenant_id IS NOT NULL AND job_status='INPROGRESS' AND rs_svr_ip= 'xxx.xxx.xx.xx' and rs_svr_port= '2882'; ``` ### 统计租户任务执行时间 ``` 2. 执行以下命令统计租户任务执行时间。 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, job_type as task_type, TIMESTAMPDIFF(SECOND, START_TIME, CURRENT_TIMESTAMP) as max_sys_task_duration_seconds, rs_svr_ip as svr_ip FROM oceanbase.DBA_OB_TENANT_JOBS WHERE job_status='INPROGRESS' AND rs_svr_ip='xxx.xxx.xx.xx' and rs_svr_port='2882' UNION SELECT tenant_id, job_type as task_type, TIMESTAMPDIFF(SECOND, START_TIME, CURRENT_TIMESTAMP) as max_sys_task_duration_seconds, rs_svr_ip as svr_ip FROM oceanbase.DBA_OB_UNIT_JOBS WHERE tenant_id IS NOT NULL AND job_status='INPROGRESS' AND rs_svr_ip='xxx.xxx.xx.xx' and rs_svr_port='2882'; ``` ### 统计 server 正在执行的任务执行时间 ``` 2. 执行以下命令统计 server 正在执行的任务执行时间。 ```shell obclient> SELECT job_type as task_type, TIMESTAMPDIFF(SECOND, START_TIME, CURRENT_TIMESTAMP) as max_sys_task_duration_seconds, svr_ip FROM oceanbase.DBA_OB_SERVER_JOBS WHERE job_status='INPROGRESS'; ``` ### 统计 server 所有任务执行时间 ``` 2. 执行以下命令统计 server 所有任务执行时间。 ```shell obclient> SELECT job_type AS task_type, job_status, timestampdiff(second, start_time, current_timestamp) AS max_sys_task_duration_seconds, svr_ip FROM oceanbase.dba_ob_server_jobs ORDER BY start_time DESC, task_type, job_status; ``` ### 统计 memtable 快照时间 1. 使用 root 用户登录到集群的 sys 租户。 ``` 2. 执行以下命令统计 memtable 快照时间。 ```shell obclient> SELECT /*+ PARALLEL(2), ENABLE_PARALLEL_DML, MONITOR_AGENT */tenant_id, max(UNIX_TIMESTAMP(NOW()) - END_LOG_SCN/1000000000) max_snapshot_duration_seconds FROM oceanbase.GV$OB_SSTABLES WHERE table_type='MEMTABLE' AND is_active='NO' and svr_ip='xxx.xxx.xx.xx' and svr_port= '2882' AND END_LOG_SCN/1000000000 > 1 GROUP BY tenant_id; ``` ### 日志流同步延迟 1. 使用 root 用户登录到集群的 sys 租户。 ``` 2. 执行以下命令统计日志流同步延迟。 ```shell obclient> SELECT /* MONITOR_AGENT */ leader.tenant_id, case leader.replica_type when 'NORMAL_REPLICA' then 0 when 'ARBIRTATION_REPLICA' then 5 else -1 end as replica_type, abs(max(CAST(leader_ts as SIGNED)-CAST(follower_ts as SIGNED)))/1000000000 max_clog_sync_delay_seconds from (select max(end_scn) leader_ts, tenant_id, replica_type, role from GV$OB_LOG_STAT where role='LEADER' group by tenant_id,replica_type ) leader inner join (select min(end_scn) follower_ts, tenant_id, replica_type, role from GV$OB_LOG_STAT where role='FOLLOWER' group by tenant_id,replica_type ) follower on leader.tenant_id=follower.tenant_id and leader.replica_type=follower.replica_type group by leader.tenant_id,leader.replica_type; ``` ### 查询索引状态 ``` 2. 执行以下命令查询索引状态。 ```shell obclient> SELECT con_id tenant_id, table_type, table_owner, table_name, owner index_owner, index_name, status, index_type, uniqueness, compression FROM oceanbase.cdb_indexes WHERE con_id = 1012 AND table_owner = 'oceanbase' -- AND table_name = 'TEST' ORDER BY tenant_id, table_owner, table_name, index_name; ``` ### 获取集群中无效索引的个数 ``` 2. 执行以下命令统计索引错误数。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from oceanbase.CDB_INDEXES where status in ('ERROR','UNUSABLE'); ``` ### 统计 memstore 信息 - sys 租户 ``` 2. 执行以下命令统计 memstore 信息。 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, active_span as active, memstore_used as total, freeze_trigger, freeze_cnt from oceanbase.GV$OB_MEMSTORE where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882'; ``` 返回结果如下： ```shell +-----------+-----------+-----------+----------------+------------+ | tenant_id | active | total | freeze_trigger | freeze_cnt | +-----------+-----------+-----------+----------------+------------+ | 1 | 186646528 | 186646528 | 1879052796 | 0 | | 1001 | 54525952 | 54525952 | 140929764 | 0 | | 1002 | 6291456 | 6291456 | 463052304 | 0 | | 1007 | 54525952 | 54525952 | 140929764 | 0 | | 1008 | 12582912 | 12582912 | 463052304 | 0 | +-----------+-----------+-----------+----------------+------------+ ``` 返回结果如下： ```shell +-----------+---------+---------+----------------+------------+ | tenant_id | active | total | freeze_trigger | freeze_cnt | +-----------+---------+---------+----------------+------------+ | 1002 | 6291456 | 6291456 | 463052304 | 0 | +-----------+---------+---------+----------------+------------+ ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, active_span as active, memstore_used as total, freeze_trigger, freeze_cnt from GV$OB_MEMSTORE where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882'; ``` 返回结果如下： ```shell +-----------+----------+----------+----------------+------------+ | TENANT_ID | ACTIVE | TOTAL | FREEZE_TRIGGER | FREEZE_CNT | +-----------+----------+----------+----------------+------------+ | 1008 | 12582912 | 12582912 | 463052304 | 0 | +-----------+----------+----------+----------------+------------+ ``` ### 查看租户 memstore 信息 - sys 租户 ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */ t.tenant_id, t.tenant_name, MAX(m.freeze_cnt) AS freeze_cnt, s.value AS minor_freeze_times, round(100 * MAX(m.freeze_cnt) / s.value, 2) AS compact_trigger_ratio FROM oceanbase.gv$ob_memstore m JOIN oceanbase.dba_ob_tenants t ON m.tenant_id = t.tenant_id JOIN oceanbase.gv$ob_parameters s ON s.name = 'major_compact_trigger' AND s.tenant_id = t.tenant_id WHERE t.tenant_id > 1000 AND t.tenant_type <> 'meta' GROUP BY m.tenant_id; ``` 返回结果如下： ```shell +-----------+-------------+------------+--------------------+-----------------------+ | tenant_id | tenant_name | freeze_cnt | minor_freeze_times | compact_trigger_ratio | +-----------+-------------+------------+--------------------+-----------------------+ | 1002 | mysql001 | 0 | 0 | NULL | | 1008 | oracle001 | 0 | 0 | NULL | +-----------+-------------+------------+--------------------+-----------------------+ ``` 返回结果如下： ```shell +-----------+-------------+------------+--------------------+-----------------------+ | tenant_id | tenant_name | freeze_cnt | minor_freeze_times | compact_trigger_ratio | +-----------+-------------+------------+--------------------+-----------------------+ | 1002 | mysql001 | 0 | 0 | NULL | +-----------+-------------+------------+--------------------+-----------------------+ ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */ t.tenant_id, MAX(m.freeze_cnt) AS freeze_cnt, s.value AS minor_freeze_times, round(100 * MAX(m.freeze_cnt) / s.value, 2) AS compact_trigger_ratio FROM gv$ob_memstore m inner JOIN dba_ob_tenants t ON m.tenant_id = t.tenant_id inner JOIN gv$ob_parameters s ON s.name = 'major_compact_trigger' AND s.tenant_id = t.tenant_id WHERE t.tenant_id > 1000 AND t.tenant_type <> 'meta' GROUP BY t.tenant_id, s.value; ``` ### 查看转储信息 ``` 2. 执行以下命令查看转储信息。 ```shell obclient> SELECT * FROM oceanbase.dba_ob_rootservice_event_history WHERE event = 'root_minor_freeze' ORDER BY timestamp DESC LIMIT 30; ``` 返回结果如下： ```shell +----------------------------+--------------+-------------------+-------+--------+-------+-------------------------------------------------------------------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ | TIMESTAMP | MODULE | EVENT | NAME1 | VALUE1 | NAME2 | VALUE2 | NAME3 | VALUE3 | NAME4 | VALUE4 | NAME5 | VALUE5 | NAME6 | VALUE6 | EXTRA_INFO | RS_SVR_IP | RS_SVR_PORT | +----------------------------+--------------+-------------------+-------+--------+-------+-------------------------------------------------------------------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ | 2024-01-31 02:00:04.199470 | root_service | root_minor_freeze | ret | 0 | arg | {tenant_ids:[1008], server_list:[], zone:"", tablet_id:{id:0}, ls_id:{id:-1}} | | | | | | | | | | xxx.xxx.xxx.xxx | 2882 | | 2024-01-31 02:00:03.942261 | root_service | root_minor_freeze | ret | 0 | arg | {tenant_ids:[1001], server_list:[], zone:"", tablet_id:{id:0}, ls_id:{id:-1}} | | | | | | | | | | xxx.xxx.xxx.xxx | 2882 | | 2024-01-31 02:00:03.274049 | root_service | root_minor_freeze | ret | 0 | arg | {tenant_ids:[1002], server_list:[], zone:"", tablet_id:{id:0}, ls_id:{id:-1}} | | | | | | | | | | xxx.xxx.xxx.xxx | 2882 | ...... | 2024-01-26 02:00:02.223438 | root_service | root_minor_freeze | ret | 0 | arg | {tenant_ids:[1], server_list:[], zone:"", tablet_id:{id:0}, ls_id:{id:-1}} | | | | | | | | | | xxx.xxx.xxx.xxx | 2882 | | 2024-01-26 02:00:01.565154 | root_service | root_minor_freeze | ret | 0 | arg | {tenant_ids:[1007], server_list:[], zone:"", tablet_id:{id:0}, ls_id:{id:-1}} | | | | | | | | | | xxx.xxx.xxx.xxx | 2882 | +----------------------------+--------------+-------------------+-------+--------+-------+-------------------------------------------------------------------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ ``` ### 查看每日合并耗时 ``` 2. 执行以下命令查看每日合并耗时。 ```shell obclient> SELECT t.tenant_id, t.global_boradcast_scn, t.merge_begin_time, u.merge_end_time, timestampdiff(second, t.merge_begin_time, u.merge_end_time) merge_time_second FROM ( SELECT value1 tenant_id, value2 global_boradcast_scn, timestamp merge_begin_time FROM oceanbase.dba_ob_rootservice_event_history WHERE module = 'daily_merge' AND event = 'merging' ) t, ( SELECT value1 tenant_id, value2 global_boradcast_scn, timestamp merge_end_time FROM oceanbase.dba_ob_rootservice_event_history WHERE module = 'daily_merge' AND event = 'global_merged' ) u WHERE t.tenant_id = u.tenant_id AND t.global_boradcast_scn = u.global_boradcast_scn ORDER BY 3 DESC LIMIT 10; ``` 返回结果如下： ```shell +-----------+----------------------+----------------------------+----------------------------+-------------------+ | tenant_id | global_boradcast_scn | merge_begin_time | merge_end_time | merge_time_second | +-----------+----------------------+----------------------------+----------------------------+-------------------+ | 1008 | 1706637604187935131 | 2024-01-31 02:00:04.204342 | 2024-01-31 02:02:05.195633 | 120 | | 1001 | 1706637603930469220 | 2024-01-31 02:00:03.948673 | 2024-01-31 02:01:45.330251 | 101 | | 1002 | 1706637603257891309 | 2024-01-31 02:00:03.283535 | 2024-01-31 02:01:34.361795 | 91 | | 1 | 1706637601837198708 | 2024-01-31 02:00:01.869199 | 2024-01-31 02:01:53.304855 | 111 | | 1007 | 1706637600790239684 | 2024-01-31 02:00:00.812716 | 2024-01-31 02:00:31.994831 | 31 | | 1 | 1706551204955071771 | 2024-01-30 02:00:04.982132 | 2024-01-30 02:01:26.302642 | 81 | | 1007 | 1706551203936096705 | 2024-01-30 02:00:03.952165 | 2024-01-30 02:02:05.369380 | 121 | | 1008 | 1706551202472229501 | 2024-01-30 02:00:02.496762 | 2024-01-30 02:01:53.687913 | 111 | | 1001 | 1706551202090639633 | 2024-01-30 02:00:02.109259 | 2024-01-30 02:01:23.668828 | 81 | | 1002 | 1706551201503941017 | 2024-01-30 02:00:01.525142 | 2024-01-30 02:01:23.142888 | 81 | +-----------+----------------------+----------------------------+----------------------------+-------------------+ ``` ### 查询合并信息（上一个已经完成合并的版本/最近一次合并的版本号/是否存在报错） ``` 2. 执行以下命令统计合并信息。 ```shell obclient> SELECT tenant_id, frozen_scn as frozen_version, LAST_SCN as last_version, case is_error when 'YES' then 1 else 0 end as is_error, case is_suspended when 'YES' then 1 else 0 end as is_suspended, time_to_usec(start_time) as start_time, time_to_usec(frozen_time) as frozen_time, time_to_usec(now()) as current from oceanbase.CDB_OB_MAJOR_COMPACTION; ``` 返回结果如下： ```shell +-----------+---------------------+---------------------+----------+--------------+------------------+------------------+------------------+ | tenant_id | frozen_version | last_version | is_error | is_suspended | start_time | frozen_time | current | +-----------+---------------------+---------------------+----------+--------------+------------------+------------------+------------------+ | 1 | 1703440801816444359 | 1703440801816444359 | 0 | 0 | 1703440801849575 | 1703440801816444 | 1703484184000000 | | 1001 | 1703440801161274829 | 1703440801161274829 | 0 | 0 | 1703440801181609 | 1703440801161275 | 1703484184000000 | | 1002 | 1703440804245544772 | 1703440804245544772 | 0 | 0 | 1703440804264253 | 1703440804245545 | 1703484184000000 | | 1007 | 1703440802826597835 | 1703440802826597835 | 0 | 0 | 1703440802846183 | 1703440802826598 | 1703484184000000 | | 1008 | 1703440800764472670 | 1703440800764472670 | 0 | 0 | 1703440800783454 | 1703440800764473 | 1703484184000000 | +-----------+---------------------+---------------------+----------+--------------+------------------+------------------+------------------+ ``` ### 查询 500 租户的内存使用情况 ``` 2. 执行以下命令查询 500 租户的内存使用情况。 ```shell obclient> SELECT /* MONITOR_AGENT */ sum(hold) as hold, sum(used) as used from oceanbase.GV$OB_MEMORY where tenant_id = 500 and svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882' and CTX_NAME <> 'KvstorCacheMb'; ``` 返回结果如下： ```shell +------------+------------+ | hold | used | +------------+------------+ | 1202488992 | 1183703541 | +------------+------------+ ``` ### 按租户统计使用的内存 - sys 租户 ``` 2. 执行以下命令按租户统计使用的内存。 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, svr_ip, svr_port, round(SUM(hold) / 1024 / 1024) AS hold_mb, round(SUM(used) / 1024 / 1024) AS used_mb FROM oceanbase.gv$ob_memory WHERE mod_name <> 'KvstorCacheMb' GROUP BY tenant_id, svr_ip, svr_port ORDER BY tenant_id, svr_ip, svr_port; ``` 返回结果如下： ```shell +-----------+-----------------+----------+---------+---------+ | tenant_id | svr_ip | svr_port | hold_mb | used_mb | +-----------+-----------------+----------+---------+---------+ | 1 | xxx.xxx.xxx.xxx | 2882 | 1800 | 1739 | | 500 | xxx.xxx.xxx.xxx | 2882 | 1164 | 1146 | | 508 | xxx.xxx.xxx.xxx | 2882 | 34 | 33 | | 509 | xxx.xxx.xxx.xxx | 2882 | 19 | 18 | | 1001 | xxx.xxx.xxx.xxx | 2882 | 425 | 414 | | 1002 | xxx.xxx.xxx.xxx | 2882 | 784 | 758 | | 1007 | xxx.xxx.xxx.xxx | 2882 | 416 | 406 | | 1008 | xxx.xxx.xxx.xxx | 2882 | 790 | 764 | +-----------+-----------------+----------+---------+---------+ ``` 返回结果如下： ```shell +-----------+-----------------+----------+---------+---------+ | tenant_id | svr_ip | svr_port | hold_mb | used_mb | +-----------+-----------------+----------+---------+---------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | 794 | 768 | +-----------+-----------------+----------+---------+---------+ ```shell obclient> SELECT /* MONITOR_AGENT */ svr_ip, svr_port, round(SUM(hold) / 1024 / 1024) AS hold_mb, round(SUM(used) / 1024 / 1024) AS used_mb FROM gv$ob_memory WHERE mod_name <> 'KvstorCacheMb' GROUP BY svr_ip, svr_port ORDER BY svr_ip, svr_port; ``` ### 按模块统计使用的内存 - sys 租户 ``` 2. 执行以下命令按模块统计使用的内存。 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, svr_ip, svr_port, mod_name, round(SUM(hold) / 1024 / 1024) AS hold_mb, round(SUM(used) / 1024 / 1024) AS used_mb FROM oceanbase.gv$ob_memory WHERE mod_name <> 'KvstorCacheMb' GROUP BY tenant_id, svr_ip, svr_port, mod_name ORDER BY tenant_id, svr_ip, svr_port, mod_name; ``` 返回结果如下： ```shell +-----------+-----------------+----------+-----------------+---------+---------+ | tenant_id | svr_ip | svr_port | mod_name | hold_mb | used_mb | +-----------+-----------------+----------+-----------------+---------+---------+ | 1 | xxx.xxx.xxx.xxx | 2882 | ApplySrv | 0 | 0 | | 1 | xxx.xxx.xxx.xxx | 2882 | APPLY_STATUS | 0 | 0 | | 1 | xxx.xxx.xxx.xxx | 2882 | ArcFetchQueue | 8 | 8 | | 1 | xxx.xxx.xxx.xxx | 2882 | ArcSenderQueue | 1 | 1 | ....... | 1008 | xxx.xxx.xxx.xxx | 2882 | [T]ObSessionDIB | 15 | 15 | | 1008 | xxx.xxx.xxx.xxx | 2882 | [T]ObTLDecoderC | 0 | 0 | | 1008 | xxx.xxx.xxx.xxx | 2882 | [T]ObTraceEvent | 0 | 0 | | 1008 | xxx.xxx.xxx.xxx | 2882 | [T]ObWarningBuf | 0 | 0 | | 1008 | xxx.xxx.xxx.xxx | 2882 | [T]TSILastOper | 0 | 0 | | 1008 | xxx.xxx.xxx.xxx | 2882 | [T]TSIUseWeak | 0 | 0 | +-----------+-----------------+----------+-----------------+---------+---------+ ``` 返回结果如下： ```shell +-----------+-----------------+----------+-----------------+---------+---------+ | tenant_id | svr_ip | svr_port | mod_name | hold_mb | used_mb | +-----------+-----------------+----------+-----------------+---------+---------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | ApplySrv | 0 | 0 | | 1002 | xxx.xxx.xxx.xxx | 2882 | APPLY_STATUS | 0 | 0 | | 1002 | xxx.xxx.xxx.xxx | 2882 | ArcFetchQueue | 8 | 8 | ..... | 1002 | xxx.xxx.xxx.xxx | 2882 | [T]ObSessionDIB | 15 | 15 | | 1002 | xxx.xxx.xxx.xxx | 2882 | [T]ObTLDecoderC | 0 | 0 | | 1002 | xxx.xxx.xxx.xxx | 2882 | [T]ObTraceEvent | 0 | 0 | | 1002 | xxx.xxx.xxx.xxx | 2882 | [T]ObWarningBuf | 0 | 0 | | 1002 | xxx.xxx.xxx.xxx | 2882 | [T]TSILastOper | 0 | 0 | | 1002 | xxx.xxx.xxx.xxx | 2882 | [T]TSIUseWeak | 0 | 0 | +-----------+-----------------+----------+-----------------+---------+---------+ ```shell obclient> SELECT /* MONITOR_AGENT */ svr_ip, svr_port, mod_name, round(SUM(hold) / 1024 / 1024) AS hold_mb, round(SUM(used) / 1024 / 1024) AS used_mb FROM gv$ob_memory WHERE mod_name <> 'KvstorCacheMb' GROUP BY svr_ip, svr_port, mod_name ORDER BY svr_ip, svr_port, mod_name; ``` ### 统计租户资源情况（CPU/内存/IOPS） ```shell obclient> SELECT /* MONITOR_AGENT */ DBA_OB_RESOURCE_POOLS.tenant_id, DBA_OB_UNIT_CONFIGS.name, DBA_OB_UNIT_CONFIGS.max_cpu, DBA_OB_UNIT_CONFIGS.min_cpu, DBA_OB_UNIT_CONFIGS.MEMORY_SIZE, DBA_OB_UNIT_CONFIGS.max_iops, DBA_OB_UNIT_CONFIGS.min_iops from oceanbase.DBA_OB_RESOURCE_POOLS, oceanbase.DBA_OB_UNIT_CONFIGS, oceanbase.DBA_OB_UNITS where DBA_OB_RESOURCE_POOLS.unit_config_id = DBA_OB_UNIT_CONFIGS.unit_config_id and DBA_OB_UNITS.resource_pool_id = DBA_OB_RESOURCE_POOLS.resource_pool_id and DBA_OB_UNITS.svr_ip = 'xxx.xxx.xxx.xxx'; ``` ### 查询有关 latch 的诊断信息 - sys 租户 ``` 2. 执行以下命令查询有关 latch 的诊断信息。 ```shell obclient> SELECT /* MONITOR_AGENT */ con_id tenant_id, name, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, wait_time / 1000000 as wait_time from oceanbase.v$latch where (con_id = 1 or con_id > 1001); ``` 返回结果如下： ```shell +-----------+------------------------------------------------+-------------+--------+--------+----------------+------------------+-------------+-----------+ | tenant_id | name | gets | misses | sleeps | immediate_gets | immediate_misses | spin_gets | wait_time | +-----------+------------------------------------------------+-------------+--------+--------+----------------+------------------+-------------+-----------+ | 1 | latch wait queue lock | 5342 | 0 | 0 | 0 | 0 | 14163 | 0.0000 | | 1 | default spin lock | 524773400 | 187 | 0 | 14 | 0 | 527126557 | 0.0000 | | 1 | default spin rwlock | 28389905749 | 384 | 0 | 37849712 | 0 | 28429201827 | 0.0000 | ...... | 1008 | mds table handler lock | 19097 | 0 | 0 | 0 | 0 | 19097 | 0.0000 | | 1008 | mds table handler lock | 19097 | 0 | 0 | 0 | 0 | 19097 | 0.0000 | | 1008 | mds table handler lock | 19097 | 0 | 0 | 0 | 0 | 19097 | 0.0000 | +-----------+------------------------------------------------+-------------+--------+--------+----------------+------------------+-------------+-----------+ ``` 2. 执行以下命令统计诊断信息。 ``` 返回结果如下： ```shell +-----------+-----------------------------------------------+---------+--------+--------+----------------+------------------+-----------+-----------+ | tenant_id | name | gets | misses | sleeps | immediate_gets | immediate_misses | spin_gets | wait_time | +-----------+-----------------------------------------------+---------+--------+--------+----------------+------------------+-----------+-----------+ | 1002 | latch wait queue lock | 64 | 0 | 0 | 0 | 0 | 64 | 0.0000 | | 1002 | default spin lock | 68 | 0 | 0 | 0 | 0 | 68 | 0.0000 | | 1002 | default spin rwlock | 3558118 | 0 | 0 | 125904 | 0 | 3686515 | 0.0000 | ...... | 1002 | display tasks lock | 1 | 0 | 0 | 0 | 0 | 1 | 0.0000 | | 1002 | mds table handler lock | 20156 | 0 | 0 | 0 | 0 | 20156 | 0.0000 | | 1002 | mds table handler lock | 20156 | 0 | 0 | 0 | 0 | 20156 | 0.0000 | +-----------+-----------------------------------------------+---------+--------+--------+----------------+------------------+-----------+-----------+ ``` ## 告警 | 模块 | 视图名 | 视图说明 | 备注 | | --- | --- | --- | --- | | 告警 | - SYS 租户：[oceanbase.DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219511) - MySQL 租户：[oceanbase.DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220071) - Oracle 租户：[DBA_OB_TENANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219430) | 该视图用于展示租户的基本信息。 | - 通过系统租户查询此视图时，可以展示所有租户的信息，包括系统租户、用户创建的租户（用户租户），以及 Meta 租户。用户可以根据 TENANT_TYPE 列来区分租户类型。 - 通过普通租户查询此视图时，只展示本租户的信息。 | | 告警 | - SYS 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219789) - MySQL 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219868) - Oracle 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219151) | 该视图用于展示租户所在的所有 OBServer 节点的会话信息。 | | 告警 | SYS 租户：[GV$OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219766) | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | | 告警 | - SYS 租户：[V$SYSSTAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219674) - MySQL 租户：[V$SYSSTAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219852) - Oracle 租户：[V$SYSSTAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219132) | 该视图用于展示当前服务器上租户级别的统计事件信息。 | | 告警 | SYS 租户：[oceanbase.DBA_OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219633) | 该视图用于展示所有 OBServer 节点的信息。 | | 告警 | SYS 租户：[oceanbase.CDB_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219602) | 该视图用于展示所有租户的 INDEXE 信息。 | | 告警 | SYS 租户：[oceanbase.CDB_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219476) | 系统租户下展示所有租户的合并全局信息。 | | 告警 | - SYS 租户：[GV$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219770) - MySQL 租户：[GV$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219821) - Oracle 租户：[GV$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219135) | 该视图用于展示所有服务器上每个租户的内存使用状况。 | | 告警 | - SYS 租户：[V$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219785) - MySQL 租户：[V$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219799) - Oracle 租户：[V$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219140) | 该视图用于展示本 OBServer 节点的 Unit 信息。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 | | 告警 | SYS 租户：[oceanbase.DBA_OB_UNIT_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219547) | 该视图用于展示所有 UNIT 相关任务。 | | 告警 | SYS 租户：[oceanbase.DBA_OB_TENANT_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219515) | 该视图用于展示租户级别的任务信息。 | | 告警 | SYS 租户：[oceanbase.DBA_OB_SERVER_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219666) | 该视图用于展示所有 OBServer 节点相关任务。 | ## 告警相关视图查询示例 ### 采集活跃会话数 - sys 租户 ``` 2. 执行以下命令采集活跃会话数。 ``` 返回结果如下： ``` 返回结果如下： ``` 返回结果如下： ``` ### 获取 OBServer CPU 已分配百分比 ``` 2. 执行以下命令获取 OBServer CPU 已分配百分比。 ```shell obclient> SELECT /* MONITOR_AGENT */ cpu_capacity_max as cpu_total,cpu_assigned_max as cpu_assigned,mem_capacity as mem_total,mem_assigned as mem_assigned,data_disk_capacity as disk_total, (cpu_assigned_max / cpu_capacity_max) as cpu_assigned_percent, (mem_assigned / mem_capacity) as mem_assigned_percent from oceanbase.GV$OB_SERVERS where svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882'; ``` 返回结果如下： ```shell +-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+ | cpu_total | cpu_assigned | mem_total | mem_assigned | disk_total | cpu_assigned_percent | mem_assigned_percent | +-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+ | 64 | 6 | 36507222016 | 16106127360 | 107374182400 | 0.09375 | 0.4412 | +-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+ ``` ### 采集 OceanBase 统计信息 采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等 - sys 租户 ``` 2. 执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。 ``` 返回结果如下： ```shell +-----------+---------+-------------+ | tenant_id | stat_id | value | +-----------+---------+-------------+ | 1 | 10000 | 16833339 | | 1 | 10000 | 16833339 | | 1 | 10000 | 16833339 | | 1 | 10000 | 16833339 | ...... | 1008 | 140006 | 0 | | 1008 | 140006 | 0 | | 1008 | 140006 | 0 | +-----------+---------+-------------+ ``` 返回结果如下： ```shell +-----------+---------+------------+ | tenant_id | stat_id | value | +-----------+---------+------------+ | 1002 | 10000 | 3387631 | | 1002 | 10001 | 1375347058 | | 1002 | 10002 | 5081203 | | 1002 | 10003 | 1471019401 | ...... | 1002 | 140003 | 954757120 | | 1002 | 140005 | 100 | | 1002 | 140006 | 0 | +-----------+---------+------------+ ```shell obclient> SELECT /* MONITOR_AGENT */ con_id tenant_id, stat_id, value from v$sysstat, DBA_OB_TENANTS where stat_id IN (10000, 10001, 10002, 10003, 10004, 10005, 10006, 140002, 140003, 140005, 140006, 40030, 80040, 80041, 130000, 130001, 130002, 130004, 20000, 20001, 20002, 30000, 30001, 30002, 30005, 30006, 30007, 30008, 30009, 30010, 30011, 30012, 30013, 40000, 40001, 40002, 40003, 40004, 40005, 40006, 40007, 40008, 40009, 40010, 40011, 40012, 40018, 40019, 50000, 50001, 60087, 50004, 50005, 50008, 50009, 50010, 50011, 50037, 50038, 60000, 60001, 60002, 60003, 60004, 60005, 60019, 60020, 60021, 60022, 60023, 60024, 80057, 120000, 120001, 120009, 120008) and (con_id > 1000 or con_id = 1) and class < 1000; ``` 返回结果如下： ```shell +-----------+---------+------------+ | TENANT_ID | STAT_ID | VALUE | +-----------+---------+------------+ | 1008 | 10000 | 3387127 | | 1008 | 10001 | 1374851374 | | 1008 | 10002 | 5080980 | | 1008 | 10003 | 1470696360 | | 1008 | 10004 | 0 | ...... | 1008 | 130004 | 2147483600 | | 1008 | 140002 | 4294967296 | | 1008 | 140003 | 984117248 | | 1008 | 140005 | 100 | | 1008 | 140006 | 0 | +-----------+---------+------------+ ``` ### 采集 OceanBase 统计信息（不含 META 租户） 采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等 - sys 租户 ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, stat_id, value FROM oceanbase.v$sysstat, oceanbase.dba_ob_tenants WHERE stat_id IN ( 30066, 50003, 50021, 50022, 50030, 50039, 50040, 60031, 60057, 60083, 80023, 80025, 80026, 120002, 120005, 120006, 200001, 200002 ) AND ( con_id > 1000 OR con_id = 1 ) AND dba_ob_tenants.tenant_id = v$sysstat.con_id AND dba_ob_tenants.tenant_type <> 'META' UNION ALL SELECT con_id AS tenant_id, stat_id, value FROM oceanbase.v$sysstat WHERE stat_id IN ( 80025, 80026, 80023 ) AND con_id > 1 AND con_id < 1001 AND value > 0; ``` 返回结果如下： ```shell +-----------+---------+--------------+ | tenant_id | stat_id | value | +-----------+---------+--------------+ | 1 | 30066 | 0 | | 1 | 50021 | 0 | | 1 | 50022 | 0 | | 1 | 50030 | 0 | ...... | 1008 | 200001 | 485965201 | | 1008 | 200002 | 484923980 | | 1008 | 120002 | 6241280 | | 1008 | 120005 | 0 | | 1008 | 120006 | 607582208 | +-----------+---------+--------------+ ``` 返回结果如下： ```shell +-----------+---------+-----------+ | tenant_id | stat_id | value | +-----------+---------+-----------+ | 1002 | 30066 | 0 | | 1002 | 50021 | 0 | | 1002 | 50022 | 0 | ...... | 1002 | 120002 | 6241280 | | 1002 | 120005 | 0 | | 1002 | 120006 | 389101568 | +-----------+---------+-----------+ ```shell obclient> SELECT /* MONITOR_AGENT */ tenant_id, stat_id, value FROM v$sysstat, dba_ob_tenants WHERE stat_id IN ( 30066, 50003, 50021, 50022, 50030, 50039, 50040, 60031, 60057, 60083, 80023, 80025, 80026, 120002, 120005, 120006, 200001, 200002 ) AND ( con_id > 1000 OR con_id = 1 ) AND dba_ob_tenants.tenant_id = v$sysstat.con_id AND dba_ob_tenants.tenant_type <> 'META' UNION ALL SELECT con_id AS tenant_id, stat_id, value FROM v$sysstat WHERE stat_id IN ( 80025, 80026, 80023 ) AND con_id > 1 AND con_id < 1001 AND value > 0; ``` 返回结果如下： ```shell +-----------+---------+-----------+ | TENANT_ID | STAT_ID | VALUE | +-----------+---------+-----------+ | 1008 | 30066 | 0 | | 1008 | 50021 | 0 | | 1008 | 50022 | 0 | | 1008 | 50030 | 0 | ...... | 1008 | 200002 | 484925832 | | 1008 | 120002 | 6241280 | | 1008 | 120005 | 0 | | 1008 | 120006 | 607582208 | +-----------+---------+-----------+ ``` ### 获取各个状态 OBServer 个数 ``` 2. 执行以下命令获取各个状态 OBServer 个数。 ``` ### 获取集群中无效索引的个数 ``` 2. 执行以下命令获取集群索引无效的个数。 ``` ### 获取 OBServer 已分配内存百分比 ``` 返回结果如下： ``` ### 查询租户合并信息 ``` 2. 执行以下命令查询租户合并信息。 ``` 返回结果如下： ``` ### 获取 OBServer 500 租户的内存占用 ``` 2. 执行以下命令获取 OBServer 500 租户的内存占用。 ```shell obclient> SELECT /* MONITOR_AGENT */ sum(hold) as hold, sum(used) as used from oceanbase.GV$OB_MEMORY where tenant_id = 500 and svr_ip = 'xxx.xxx.xx.xx' and svr_port = '2882' and CTX_NAME <> 'OB_KVSTORE_CACHE_MB'; ``` 返回结果如下： ```shell +------------+------------+ | hold | used | +------------+------------+ | 1202505488 | 1183719757 | +------------+------------+ ``` ### 获取 SSTable 使用率 ``` 2. 执行以下命令获取 SSTable 使用率。 ``` 返回结果如下： ```shell +--------------+--------------+ | total_size | free_size | +--------------+--------------+ | 107374182400 | 106982014976 | +--------------+--------------+ ``` ### 获取租户日志盘使用率 - sys 租户 ``` 2. 执行以下命令获取租户日志盘使用率。 ```shell obclient> SELECT tenant_id, log_disk_in_use, log_disk_size FROM oceanbase.GV$OB_UNITS; ``` 返回结果如下： ```shell +-----------+-----------------+---------------+ | tenant_id | log_disk_in_use | log_disk_size | +-----------+-----------------+---------------+ | 1 | 1789104118 | 17448304640 | | 1001 | 512742114 | 644245094 | | 1002 | 1234090827 | 5798205850 | | 1007 | 506335700 | 644245094 | | 1008 | 1247515009 | 5798205850 | +-----------+-----------------+---------------+ ``` 返回结果如下： ```shell +-----------+-----------------+---------------+ | tenant_id | log_disk_in_use | log_disk_size | +-----------+-----------------+---------------+ | 1002 | 1234096663 | 5798205850 | +-----------+-----------------+---------------+ ``` 返回结果如下： ```shell +-----------+-----------------+---------------+ | TENANT_ID | LOG_DISK_IN_USE | LOG_DISK_SIZE | +-----------+-----------------+---------------+ | 1008 | 1247546517 | 5798205850 | +-----------+-----------------+---------------+ ``` ### 获取租户任务运行时长 ``` 2. 执行以下命令获取租户任务运行时长。 ``` ### 获取 OBServer 运行中的任务运行时长 ``` 2. 执行以下命令获取 OBServer 运行中的任务运行时长。 ``` ## 诊断 | 模块 | 视图名 | 视图说明 | 备注 | | --- | --- | --- | --- | | SQL 诊断 | SYS 租户：[oceanbase.CDB_OBJECTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219589) | 系统租户下展示所有租户的对象信息。 | | SQL 诊断 | SYS 租户：[oceanbase.CDB_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219602) | 该视图用于展示所有租户的 INDEXE 信息。 | | SQL 诊断 | SYS 租户：[oceanbase.CDB_IND_COLUMNS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219623) | 该视图用于展示所有租户的索引表的列信息。 | | SQL 详情 | - MySQL 租户：[oceanbase.DBA_PART_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219944) - Oracle 租户：[DBA_PART_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219434) | 该视图用于查看所有的分区索引的分区信息。 | | SQL 详情 | MySQL 租户：[information_schema.TABLE_CONSTRAINTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220050) | 该视图用于描述表约束信息。 | | SQL 详情 | MySQL 租户：[information_schema.STATISTICS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220096) | 该视图用于记录表索引信息。 | | SQL 详情 | Oracle 租户：[DBA_IND_COLUMNS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219422) | 该视图用于查看数据库所有表的索引的索引列信息。 | | SQL 详情 | Oracle 租户：[DBA_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219281) | 该视图用于查看数据库所有表的索引信息。 | | SQL 详情 | Oracle 租户：[DBA_CONSTRAINTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219350) | 该视图用于查看数据库中所有表的约束信息。 | | Outline | - SYS 租户：[oceanbase.DBA_PART_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219634) - MySQL 租户：[oceanbase.DBA_PART_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219944) - Oracle 租户：[DBA_PART_INDEXES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219434) | 该视图用于查看所有的分区索引的分区信息。 | | Outline | - SYS 租户：[oceanbase.DBA_OB_CONCURRENT_LIMIT_SQL](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219586) - MySQL 租户：[oceanbase.DBA_OB_CONCURRENT_LIMIT_SQL](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219912) - Oracle 租户：[DBA_OB_CONCURRENT_LIMIT_SQL](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219454) | 该视图用于展示本租户的限流规则 Outline 相关信息。 | | Outline | - SYS 租户：[oceanbase.DBA_OB_OUTLINE_CONCURRENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219638) - MySQL 租户：[oceanbase.DBA_OB_OUTLINE_CONCURRENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220105) - Oracle 租户：[DBA_OB_OUTLINE_CONCURRENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219240) | 该视图用于展示本租户的执行计划和限流规则 Outline 历史信息。 | | 索引推荐 | MySQL 租户：[information_schema.COLUMNS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220054) | 该视图用于记录列信息。 | | 索引推荐 | MySQL 租户：[oceanbase.DBA_TAB_COL_STATISTICS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220029) | 该视图用于展示数据库中所有表的列统计信息和直方图信息。 | | 索引推荐 | Oracle 租户：[DBA_TAB_COLUMNS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219186) | 该视图用于查看数据库中所有的表、视图的所有列。 | | 索引推荐 | Oracle 租户：[DBA_TAB_COL_STATISTICS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219249) | 该视图用于查看数据库中所有的表、视图的所有列。 | | SQL 采集 | - SYS 租户：[V$OB_SQL_AUDIT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219769) - MySQL 租户：[V$OB_SQL_AUDIT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219873) - Oracle 租户：[V$OB_SQL_AUDIT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219100) | 该视图用于展示当前 OBServer 节点的每一次 SQL 请求的来源、执行状态等统计信息。 | 该视图是按照租户拆分的，除了系统租户，其他租户不能跨租户查询。 | | SQL 采集 | - SYS 租户：[GV$OB_SQL_AUDIT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219776) - MySQL 租户：[GV$OB_SQL_AUDIT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219838) - Oracle 租户：[GV$OB_SQL_AUDIT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219093) | 该视图用于展示所有 OBServer 节点上每一次 SQL 请求的来源、执行状态等统计信息。 | 该视图是按照租户拆分的，除了系统租户，其他租户不能跨租户查询。 | | SQL 采集 | - SYS 租户：[V$OB_PLAN_CACHE_PLAN_EXPLAIN](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219754) - MySQL 租户：[V$OB_PLAN_CACHE_PLAN_EXPLAIN](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219881) - Oracle 租户：[V$OB_PLAN_CACHE_PLAN_EXPLAIN](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219153) | 该视图用于展示缓存在当前 OBServer 节点的计划缓存中的物理执行计划。 | | SQL 采集 | - SYS 租户：[GV$OB_PLAN_CACHE_PLAN_EXPLAIN](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219787) - MySQL 租户：[GV$OB_PLAN_CACHE_PLAN_EXPLAIN](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219855) - Oracle 租户：[GV$OB_PLAN_CACHE_PLAN_EXPLAIN](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219054) | 该视图用于展示缓存在全部的 OBServer 节点中的计划缓存中的物理执行计划。 | | SQL 采集 | - SYS 租户：[GV$OB_PLAN_CACHE_PLAN_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219745) - MySQL 租户：[GV$OB_PLAN_CACHE_PLAN_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219910) - Oracle 租户：[GV$OB_PLAN_CACHE_PLAN_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219080) | 该视图用于展示当前租户在所有 OBServer 节点上的计划缓存中缓存的每一个缓存对象的状态。 | 该视图不仅缓存了 SQL 计划对象，也缓存了 PL 对象（如匿名块、PL Package 以及 PL Function），某些字段只在特定对象下有效。 | | SQL 采集 | - SYS 租户：[V$OB_PLAN_CACHE_PLAN_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219720) - MySQL 租户：[V$OB_PLAN_CACHE_PLAN_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219872) - Oracle 租户：[V$OB_PLAN_CACHE_PLAN_STAT](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219156) | 该视图用于展示当前租户在当前 OBServer 节点上的计划缓存中缓存的每一个缓存对象的状态。 | 该视图不仅缓存了 SQL 计划对象，也缓存了 PL 对象（例如匿名块、PL Package 以及 PL Function），某些字段只在特定对象下有效。 | | 性能报告 | SYS 租户：[GV$OB_SERVERS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219766) | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | | 性能报告 | - SYS 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219785) - MySQL 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219799) - Oracle 租户：[GV$OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219063) | 该视图用于展示租户所在的 OBServer 节点的 Unit 信息（资源统计）。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 | | 性能报告 | SYS 租户：[oceanbase.DBA_OB_UNITS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219556) | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 | | 事务诊断 | - SYS 租户：[V$OB_TRANSACTION_PARTICIPANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219764) - MySQL 租户：[V$OB_TRANSACTION_PARTICIPANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219829) - Oracle 租户：[V$OB_TRANSACTION_PARTICIPANTS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219101) | 该视图用于展示所有 OBServer 节点上的事务参与者信息。 | | 死锁检测 | - SYS 租户：[oceanbase.DBA_OB_DEADLOCK_EVENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219580) - MySQL 租户：[oceanbase.DBA_OB_DEADLOCK_EVENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219959) - Oracle 租户：[DBA_OB_DEADLOCK_EVENT_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219340) | 该视图用于展示死锁事件的历史记录。 | 该视图在非 root 租户下展示本租户的历史记录。 | | 会话管理 | - SYS 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219789) - MySQL 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219868) - Oracle 租户：[GV$OB_PROCESSLIST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219151) | 该视图用于展示租户所在的所有 OBServer 节点的会话信息。 | ## 诊断相关视图查询示例 ### 获取数据库列表 ``` 2. 执行以下命令获取数据库列表。 ```shell obclient> SELECT/*+ QUERY_TIMEOUT(1000000) */ con_id as tenant_id, object_id as database_id, object_name as database_name FROM oceanbase.cdb_objects where con_id = and OBJECT_TYPE = 'DATABASE'; ``` 返回结果如下： ```shell +-----------+-------------+--------------------+ | tenant_id | database_id | database_name | +-----------+-------------+--------------------+ | 1 | 201001 | oceanbase | | 1 | 201002 | information_schema | | 1 | 201003 | mysql | | 1 | 201004 | __recyclebin | | 1 | 201005 | __public | | 1 | 201006 | SYS | | 1 | 201007 | LBACSYS | | 1 | 201008 | ORAAUDITOR | | 1 | 500001 | test | +-----------+-------------+--------------------+ ``` ### 表的统计数据查询 - sys 租户 ``` 2. 执行以下命令进行表的统计数据查询。 ```shell obclient> SELECT S.owner,S.table_name,S.column_name,S.num_distinct as ndvCount, S.num_nulls as nullCount,S.low_value as minValue, S.high_value as `maxValue`, C.data_type as dataTypeName from (select * from information_schema.COLUMNS where table_schema = and table_name = ) C left join (select * from oceanbase.DBA_TAB_COL_STATISTICS where owner = and table_name = ) S on S.owner = C.table_schema and S.table_name = C.table_name and S.column_name = C.column_name; ```shell obclient> SELECT S.owner,S.table_name,S.column_name,S.num_distinct as ndvCount, S.num_nulls as nullCount, S.low_value as minValue,S.high_value maxValue,C.data_type as dataTypeName from (select * from DBA_TAB_COLUMNS where owner = and table_name = ) C left join (select * from DBA_TAB_COL_STATISTICS where owner = and table_name = ) S on S.owner = C.owner and S.table_name = C.table_name and S.column_name = C.column_name; ``` ### 查询限流 Outline - sys 租户 ``` 2. 执行以下命令查询限流 Outline。 ```shell obclient> SELECT outline_name as outlineName,outline_id, outline_content, database_id, database_name, visible_signature, sql_text, concurrent_num, limit_target, create_time, modify_time from oceanbase.DBA_OB_CONCURRENT_LIMIT_SQL; ```shell obclient> SELECT outline_name as outlineName,outline_id, dbms_lob.substr(outline_content) as outline_content , database_id, database_name, dbms_lob.substr(visible_signature) as visible_signature, dbms_lob.substr(sql_text) as sql_text, concurrent_num, dbms_lob.substr(limit_target) as limit_target, sysdate as gmt_create from DBA_OB_CONCURRENT_LIMIT_SQL; ``` ### 查询普通 outline - sys 租户 ``` 2. 执行以下命令查询普通 outline。 ```shell obclient> SELECT database_id, tenant_id, outline_id, outline_name as outlineName, outline_content, sql_text, sql_id, outline_target, create_time, modify_time from oceanbase.DBA_OB_OUTLINES; ```shell obclient> SELECT database_id, tenant_id, outline_id, outline_name as outlineName, dbms_lob.substr(outline_content) as outline_content, dbms_lob.substr(sql_text) as sql_text, sql_id, dbms_lob.substr(outline_target) as outline_target, sysdate as gmt_create from DBA_OB_OUTLINES; ``` ### 查询历史 Outline - sys 租户 ``` 2. 执行以下命令查询历史 Outline。 ```shell obclient> SELECT database_id, tenant_id, outline_id, outline_name as outlineName, outline_content, sql_text, sql_id, outline_target, case enabled when 'YES' then 1 else 0 end as enabled, create_time from oceanbase.DBA_OB_OUTLINE_CONCURRENT_HISTORY where ADDDATE(create_time, ) > CURDATE() and is_deleted = 'NO'; ```shell obclient> SELECT database_id, tenant_id, outline_id, outline_name as outlineName, dbms_lob.substr(outline_content) as outline_content , dbms_lob.substr(sql_text) as sql_text, sql_id, dbms_lob.substr(outline_target) as outline_target, case enabled when 'YES' then 1 else 0 end as enabled, sysdate as gmt_create from DBA_OB_OUTLINE_CONCURRENT_HISTORY where create_time < (sysdate + ) and is_deleted = 'NO'; ``` ### 查询索引信息 查询索引信息，如索引名称，索引列名称，注释等。 - sys 租户 ``` 2. 执行以下命令查询索引信息。 ```shell obclient> SELECT I.index_name, I.index_type, I.uniqueness, I.status, I.table_owner, group_concat(C.column_name order by column_position separator ',') as column_name from oceanbase.cdb_indexes I left join cdb_ind_columns C on I.table_owner = C.table_owner and I.table_name = C.table_name and I.index_name = C.index_name and I.con_id = C.con_id where I.con_id = and I.table_owner = and I.table_name = and I.status = 'VALID' group by index_name; ```shell obclient> SELECT I.index_name as index_name,I.column_name as column_name, case I.comment when 'available' then 'VALID' when 'unique_checking' then 'CHECKING' when 'unavailable' then 'UNUSABLE' when 'unusable' then 'UNUSABLE' when 'unique_inelegible' then 'INELEGIBLE' when 'index_error' then 'ERROR' when 'restore_index_error' then 'ERROR' else 'UNKNOWN' end status, case I.non_unique when 0 then 'true' else 'false' end is_unique, case P.locality when 'GLOBAL' then 'true' else 'false' end is_global, case C.CONSTRAINT_TYPE when 'PRIMARY KEY' then 'true' else 'false' end is_primary from ( select table_schema,index_name,table_name,non_unique,comment,group_concat(column_name order by seq_in_index separator ',') as column_name from information_schema.statistics where table_schema = $数据库名 and table_name = $表名 group by index_name) as I left join oceanbase.DBA_PART_INDEXES as P on I.index_name = P.index_name and I .table_name = P.table_name and P.owner = I.table_schema left join information_schema.TABLE_CONSTRAINTS C on I.index_name = C .CONSTRAINT_NAME and I.table_schema = C.table_schema and I.table_name = C.table_name; ```shell obclient> SELECT C.TABLE_NAME as table_name,C.INDEX_NAME as index_name,C.column_name as column_name, I.status, case I.uniqueness when 'UNIQUE' then 'true' else 'false' end is_unique, case P.locality when 'GLOBAL' then 'true' else 'false' end is_global, case T.constraint_type when 'P' then 'true' else 'false' end is_primary FROM (SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, LISTAGG(COLUMN_NAME, ',') WITHIN GROUP ( ORDER BY COLUMN_POSITION) column_name FROM DBA_IND_COLUMNS WHERE TABLE_OWNER = $登录用户名 and TABLE_NAME = $表名 GROUP BY TABLE_OWNER,TABLE_NAME,INDEX_NAME ) C left join DBA_INDEXES I on C.table_owner = I.table_owner and C.table_name = I.table_name and C.index_name = I.index_name left join DBA_PART_INDEXES P on I.index_name = P.index_name and I.table_name = P.table_name and P.owner = I.table_owner left join DBA_CONSTRAINTS T on I.table_name = T.table_name and I.table_owner = T.owner and I.index_name = T.constraint_name; ``` ### 获取原生的 sql_audit 数据 - sys 租户 ``` 2. 执行以下命令获取原生的 sql_audit 数据。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ sql_id, tenant_id, tenant_name, user_id, user_name, db_id, db_name, query_sql, plan_id, trace_id, client_ip,client_port, request_time, request_id, elapsed_time, execute_time, total_wait_time_micro, wait_time_micro, get_plan_time, affected_rows, return_rows, partition_cnt, ret_code, (case when ret_code = 0 then 0 else 1 end) as fail_count, (case when ret_code = -4012 then 1 else 0 end) as ret_code_4012_count, (case when ret_code = -4013 then 1 else 0 end) as ret_code_4013_count, (case when ret_code = -5001 then 1 else 0 end) as ret_code_5001_count, (case when ret_code = -5024 then 1 else 0 end) as ret_code_5024_count, (case when ret_code = -5167 then 1 else 0 end) as ret_code_5167_count, (case when ret_code = -5217 then 1 else 0 end) as ret_code_5217_count, (case when ret_code = -6002 then 1 else 0 end) as ret_code_6002_count, (case event when 'system internal wait' then wait_time_micro else 0 end) as event_0_wait_time, (case event when 'mysql response wait client' then wait_time_micro else 0 end) as event_1_wait_time, (case event when 'sync rpc' then wait_time_micro else 0 end) as event_2_wait_time, (case event when 'db file data read' then wait_time_micro else 0 end) as event_3_wait_time, total_waits, rpc_count, plan_type, (case when plan_type=1 then 1 else 0 end) as plan_type_local_count, (case when plan_type=2 then 1 else 0 end) as plan_type_remote_count, (case when plan_type=3 then 1 else 0 end) as plan_type_dist_count, is_inner_sql, is_executor_rpc, is_hit_plan, consistency_level, (case when is_inner_sql=1 then 1 else 0 end) as inner_sql_count, (case when is_executor_rpc = 1 then 1 else 0 end) as executor_rpc_count, (case when is_hit_plan=1 then 0 else 1 end) as miss_plan_count, (case consistency_level when 3 then 1 else 0 end) as consistency_level_strong, (case consistency_level when 2 then 1 else 0 end) as consistency_level_weak, net_time, net_wait_time, queue_time, decode_time, application_wait_time, concurrency_wait_time, user_io_wait_time, schedule_time, row_cache_hit, bloom_filter_cache_hit, block_cache_hit, disk_reads, retry_cnt, table_scan, memstore_read_row_count, ssstore_read_row_count, expected_worker_count, used_worker_count, request_memory_used, tx_id as transaction_hash from (select * from oceanbase.V$OB_SQL_AUDIT where tenant_id = and request_id >= and request_id <= and (request_time + elapsed_time) >= and (request_time + elapsed_time) < and query_sql not like order by request_id limit ) t; ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ sql_id, tenant_id, tenant_name, user_id, user_name, db_id, db_name, query_sql, plan_id, trace_id, client_ip,client_port, request_time, request_id, elapsed_time, execute_time, total_wait_time_micro, wait_time_micro, get_plan_time, affected_rows, return_rows, partition_cnt, ret_code, (case when ret_code = 0 then 0 else 1 end) as fail_count, (case when ret_code = -4012 then 1 else 0 end) as ret_code_4012_count, (case when ret_code = -4013 then 1 else 0 end) as ret_code_4013_count, (case when ret_code = -5001 then 1 else 0 end) as ret_code_5001_count, (case when ret_code = -5024 then 1 else 0 end) as ret_code_5024_count, (case when ret_code = -5167 then 1 else 0 end) as ret_code_5167_count, (case when ret_code = -5217 then 1 else 0 end) as ret_code_5217_count, (case when ret_code = -6002 then 1 else 0 end) as ret_code_6002_count, (case event when 'system internal wait' then wait_time_micro else 0 end) as event_0_wait_time, (case event when 'mysql response wait client' then wait_time_micro else 0 end) as event_1_wait_time, (case event when 'sync rpc' then wait_time_micro else 0 end) as event_2_wait_time, (case event when 'db file data read' then wait_time_micro else 0 end) as event_3_wait_time, total_waits, rpc_count, plan_type, (case when plan_type=1 then 1 else 0 end) as plan_type_local_count, (case when plan_type=2 then 1 else 0 end) as plan_type_remote_count, (case when plan_type=3 then 1 else 0 end) as plan_type_dist_count, is_inner_sql, is_executor_rpc, is_hit_plan, consistency_level, (case when is_inner_sql=1 then 1 else 0 end) as inner_sql_count, (case when is_executor_rpc = 1 then 1 else 0 end) as executor_rpc_count, (case when is_hit_plan=1 then 0 else 1 end) as miss_plan_count, (case consistency_level when 3 then 1 else 0 end) as consistency_level_strong, (case consistency_level when 2 then 1 else 0 end) as consistency_level_weak, net_time, net_wait_time, queue_time, decode_time, application_wait_time, concurrency_wait_time, user_io_wait_time, schedule_time, row_cache_hit, bloom_filter_cache_hit, block_cache_hit, disk_reads, retry_cnt, table_scan, memstore_read_row_count, ssstore_read_row_count, expected_worker_count, used_worker_count, request_memory_used, tx_id as transaction_hash from (select * from V$OB_SQL_AUDIT where tenant_id = and request_id >= and request_id <= and (request_time + elapsed_time) >= and (request_time + elapsed_time) < and query_sql not like and rownum < order by request_id) t; ``` ### 执行计划的统计信息 - sys 租户 ``` 2. 执行以下命令查询执行计划的统计信息。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ time_to_usec(now()) as collect_time, tenant_id, db_id, plan_id, avg_exe_usec, slowest_exe_usec, slow_count, hit_count, executions, disk_reads, direct_writes, buffer_gets, application_wait_time, concurrency_wait_time, user_io_wait_time, rows_processed, elapsed_time, cpu_time, large_querys, delayed_large_querys, table_scan, timeout_count, time_to_usec(first_load_time) as first_load_time, sql_id, type, plan_hash, schema_version, plan_size, outline_data from oceanbase.V$OB_PLAN_CACHE_PLAN_STAT where tenant_id = and plan_id > and object_type = 'sql_plan' order by plan_id limit ; ``` 返回结果如下： ```shell +------------------+-----------+--------+---------+--------------+------------------+------------+-----------+------------+------------+---------------+-------------+-----------------------+-----------------------+-------------------+----------------+--------------+----------+--------------+----------------------+------------+---------------+------------------+----------------------------------+------+---------------------+----------------+-----------+-------------------------------------------------------------------------------+ | 1703560523000000 | 1 | 201009 | 3622 | 1767 | 1767 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1767 | 1721 | 0 | 0 | 0 | 0 | 1703063773292247 | 07E5B378A3CD3778A58E18DB9AD2A430 | 1 | 7420493073239164301 | 1 | 94656 | /*+BEGIN_OUTLINE_DATA OPTIMIZER_FEATURES_ENABLE('4.0.0.0') END_OUTLINE_DATA*/ | +------------------+-----------+--------+---------+--------------+------------------+------------+-----------+------------+------------+---------------+-------------+-----------------------+-----------------------+-------------------+----------------+--------------+----------+--------------+----------------------+------------+---------------+------------------+----------------------------------+------+---------------------+----------------+-----------+-------------------------------------------------------------------------------+ ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ tenant_id, db_id, plan_id, avg_exe_usec, slowest_exe_usec, slow_count, hit_count, executions, disk_reads, direct_writes, concurrency_wait_time, user_io_wait_time, rows_processed, elapsed_time, cpu_time, large_querys, delayed_large_querys, table_scan, timeout_count, sql_id, type, plan_hash, schema_version, plan_size, outline_data from V$OB_PLAN_CACHE_PLAN_STAT where tenant_id = and plan_id > and to_char(object_type) = 'SQL_PLAN' and rownum < order by plan_id; ``` ### 执行计划的详情 - sys 租户 ``` 2. 执行以下命令查询执行计划的详情。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ time_to_usec(now()) as collect_time, tenant_id, plan_id, operator, name, rows, cost, property from oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN where tenant_id = and plan_id = and svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882'; ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ tenant_id, plan_id, operator, name, rows, cost, property from V$OB_PLAN_CACHE_PLAN_EXPLAIN where tenant_id = and plan_id = and svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882'; ``` ### 采集事务信息 采集事务信息，例如事务类型，事务 ID，所属日志流 ID，参与者列表，事务日志大小等。 - sys 租户 ``` 2. 执行以下命令采集事务信息。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ tenant_id, tx_id, ls_id, floor(unix_timestamp(ctx_create_time) *1000000) as ctx_create_time, session_id, participants, (pending_log_size + flushed_log_size) as log_size_byte, (case tx_type when 'DISTRIBUTED' then 2 else 1 end) as trans_type, (case action when 'START TASK' then 1 when 'END TASK' then 2 when 'COMMIT' then 3 when 'ABORT' then 4 when 'DIED' then 5 when 'END' then 6 else 0 end) as part_trans_action from oceanbase.v$ob_transaction_participants where svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882'; ``` 2. 执行以下命令查询事务采集。 ```shell obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(%d) */ tenant_id, tx_id, ls_id, session_id, participants, (pending_log_size + flushed_log_size) as log_size_byte, (case tx_type when 'DISTRIBUTED' then 2 else 1 end) as trans_type, (case action when 'START TASK' then 1 when 'END TASK' then 2 when 'COMMIT' then 3 when 'ABORT' then 4 when 'DIED' then 5 when 'END' then 6 else 0 end) as part_trans_action from v$ob_transaction_participants where svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882'; ``` ### 获取并行 SQL 的 sql_plan_monitor 信息 - sys 租户 ``` 2. 执行以下命令采集并行 SQL 的 sql_plan_monitor 。 ```shell obclient> SELECT /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(%d) */ con_id as tenant_id, request_id, trace_id, plan_line_id, process_name as process_id, plan_operation, plan_depth, output_rows, first_refresh_time, last_refresh_time, ifnull(first_change_time, 0) as first_change_time, ifnull(last_change_time, 0) as last_change_time, starts as rescan_count, otherstat_1_id, otherstat_1_value, otherstat_2_id, otherstat_2_value, otherstat_3_id, otherstat_3_value, otherstat_4_id, otherstat_4_value, otherstat_5_id, otherstat_5_value, otherstat_6_id, otherstat_6_value, otherstat_7_id, otherstat_7_value, otherstat_8_id, otherstat_8_value, otherstat_9_id, otherstat_9_value, otherstat_10_id, otherstat_10_value from oceanbase.v$sql_plan_monitor where con_id = and request_id >= and request_id <= order by request_id limit ; ``` 返回结果如下： ```shell +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | tenant_id | request_id | trace_id | plan_line_id | process_id | plan_operation | plan_depth | output_rows | first_refresh_time | last_refresh_time | first_change_time | last_change_time | rescan_count | otherstat_1_id | otherstat_1_value | otherstat_2_id | otherstat_2_value | otherstat_3_id | otherstat_3_value | otherstat_4_id | otherstat_4_value | otherstat_5_id | otherstat_5_value | otherstat_6_id | otherstat_6_value | otherstat_7_id | otherstat_7_value | otherstat_8_id | otherstat_8_value | otherstat_9_id | otherstat_9_value | otherstat_10_id | otherstat_10_value | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | 1 | 1 | YB42AC1E87FF-00060CED7BED1D0A-0-0 | 6 | 30270 | PHY_GRANULE_ITERATOR | 6 | 0 | 2023-12-21 18:21:05.830940 | 2023-12-21 18:21:05.850918 | 0 | 0 | 0 | 25 | 0 | 26 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ ``` ```shell +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | tenant_id | request_id | trace_id | plan_line_id | process_id | plan_operation | plan_depth | output_rows | first_refresh_time | last_refresh_time | first_change_time | last_change_time | rescan_count | otherstat_1_id | otherstat_1_value | otherstat_2_id | otherstat_2_value | otherstat_3_id | otherstat_3_value | otherstat_4_id | otherstat_4_value | otherstat_5_id | otherstat_5_value | otherstat_6_id | otherstat_6_value | otherstat_7_id | otherstat_7_value | otherstat_8_id | otherstat_8_value | otherstat_9_id | otherstat_9_value | otherstat_10_id | otherstat_10_value | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | 1002 | 1 | YB42AC1E87FF-00060CED6A0D1D17-0-0 | 6 | 30273 | PHY_GRANULE_ITERATOR | 6 | 0 | 2023-12-21 18:21:09.041429 | 2023-12-21 18:21:09.045617 | 0 | 0 | 0 | 25 | 0 | 26 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ ```shell obclient> SELECT /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(%d) */ con_id as tenant_id, request_id, trace_id, plan_line_id, process_name as process_id, plan_operation, plan_depth, output_rows, first_refresh_time, last_refresh_time, starts as rescan_count, otherstat_1_id, otherstat_1_value, otherstat_2_id, otherstat_2_value, otherstat_3_id, otherstat_3_value, otherstat_4_id, otherstat_4_value, otherstat_5_id, otherstat_5_value, otherstat_6_id, otherstat_6_value, otherstat_7_id, otherstat_7_value, otherstat_8_id, otherstat_8_value, otherstat_9_id, otherstat_9_value, otherstat_10_id, otherstat_10_value from v$sql_plan_monitor where con_id = and request_id >= and request_id <= and rownum < order by request_id ; ``` 返回结果如下： ```shell +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+------------------------------+------------------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | TENANT_ID | REQUEST_ID | TRACE_ID | PLAN_LINE_ID | PROCESS_ID | PLAN_OPERATION | PLAN_DEPTH | OUTPUT_ROWS | FIRST_REFRESH_TIME | LAST_REFRESH_TIME | RESCAN_COUNT | OTHERSTAT_1_ID | OTHERSTAT_1_VALUE | OTHERSTAT_2_ID | OTHERSTAT_2_VALUE | OTHERSTAT_3_ID | OTHERSTAT_3_VALUE | OTHERSTAT_4_ID | OTHERSTAT_4_VALUE | OTHERSTAT_5_ID | OTHERSTAT_5_VALUE | OTHERSTAT_6_ID | OTHERSTAT_6_VALUE | OTHERSTAT_7_ID | OTHERSTAT_7_VALUE | OTHERSTAT_8_ID | OTHERSTAT_8_VALUE | OTHERSTAT_9_ID | OTHERSTAT_9_VALUE | OTHERSTAT_10_ID | OTHERSTAT_10_VALUE | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+------------------------------+------------------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | 1008 | 1 | YB42AC1E87FF-00060CED79DD1D41-0-0 | 2 | 59751 | PHY_GRANULE_ITERATOR | 2 | 0 | 25-DEC-23 10.32.39.536937 AM | 25-DEC-23 10.32.39.541164 AM | 0 | 25 | 0 | 26 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+------------------------------+------------------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ ``` ### 采集会话信息 - sys 租户 ``` 2. 执行以下命令采集会话信息。 ```shell obclient> SELECT /*+ query_timeout(%d) */ sid as session_id, event_id, con_id as tenant_id, total_waits, time_waited_micro as time_waited_us from oceanbase.v$session_event where total_waits > 0 and (sid> or (sid = and event_id > )) order by sid, event_id limit ; ``` 返回结果如下： ```shell +------------+----------+-----------+-------------+----------------+ | session_id | event_id | tenant_id | total_waits | time_waited_us | +------------+----------+-----------+-------------+----------------+ | 3221225472 | 10001 | 1 | 2 | 334 | +------------+----------+-----------+-------------+----------------+ ``` 返回结果如下： ```shell +------------+----------+-----------+-------------+----------------+ | session_id | event_id | tenant_id | total_waits | time_waited_us | +------------+----------+-----------+-------------+----------------+ | 3221487622 | 10001 | 1002 | 12 | 1869 | +------------+----------+-----------+-------------+----------------+ ``` ### 查询资源分配情况 ``` 2. 执行以下命令查询资源分配情况。 ```shell obclient> SELECT /* MONITOR_AGENT */ svr_ip, svr_port, cpu_capacity_max AS cpu_total, cpu_assigned_max AS cpu_assigned, round(mem_capacity / 1024 / 1024 / 1024) mem_total_gb, round(mem_assigned / 1024 / 1024 / 1024) mem_assigned_gb, round((cpu_assigned_max / cpu_capacity_max), 2) AS cpu_assigned_percent, round((mem_assigned / mem_capacity), 2) AS mem_assigned_percent, round(data_disk_capacity / 1024 / 1024 / 1024) data_disk_capacity_gb, round(data_disk_in_use / 1024 / 1024 / 1024) data_disk_in_use_gb, round( (data_disk_capacity - data_disk_in_use) / 1024 / 1024 / 1024 ) data_disk_free_gb, round(log_disk_capacity / 1024 / 1024 / 1024) log_disk_capacity_gb, round(log_disk_assigned / 1024 / 1024 / 1024) log_disk_assigned_gb, round(log_disk_in_use / 1024 / 1024 / 1024) log_disk_in_use_gb FROM oceanbase.gv$ob_servers ORDER BY svr_ip, svr_port; ``` 返回结果如下： ```shell +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ | svr_ip | svr_port | cpu_total | cpu_assigned | mem_total_gb | mem_assigned_gb | cpu_assigned_percent | mem_assigned_percent | data_disk_capacity_gb | data_disk_in_use_gb | data_disk_free_gb | log_disk_capacity_gb | log_disk_assigned_gb | log_disk_in_use_gb | +----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ | xxx.xxx.xxx.xxx | 2882 | 64 | 6 | 34 | 15 | 0.09 | 0.44 | 100 | 0 | 100 | 167 | 28 | 23 | +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ ``` ### 查询租户副本数据大小 ``` 2. 执行以下命令查询租户副本数据大小。 ```shell obclient> SELECT UNIT_ID, SVR_IP, SVR_PORT, ZONE, TENANT_ID, MIN_CPU, MAX_CPU, MIN_MEMORY, MAX_MEMORY, MIN_IOPS, MAX_IOPS, IOPS_WEIGHT, DATA_DISK_IN_USE, LOG_DISK_IN_USE FROM ( SELECT T1.UNIT_ID, T1.SVR_IP, T1.SVR_PORT, T1.ZONE, T2.TENANT_ID, T1.MIN_CPU, T1.MAX_CPU, T1.MIN_MEMORY, T1.MAX_MEMORY, T1.MIN_IOPS, T1.MAX_IOPS, T1.IOPS_WEIGHT, T1.DATA_DISK_IN_USE, T1.LOG_DISK_IN_USE FROM (SELECT UNIT_ID, SVR_IP, SVR_PORT, ZONE, SUM(MIN_CPU) AS MIN_CPU, SUM(MAX_CPU) AS MAX_CPU, SUM(MEMORY_SIZE) AS MIN_MEMORY, SUM(MEMORY_SIZE) AS MAX_MEMORY, SUM(MIN_IOPS) AS MIN_IOPS, SUM(MAX_IOPS) AS MAX_IOPS, SUM(IOPS_WEIGHT) AS IOPS_WEIGHT, SUM(DATA_DISK_IN_USE) AS DATA_DISK_IN_USE, SUM(LOG_DISK_IN_USE) AS LOG_DISK_IN_USE FROM oceanbase.GV$OB_UNITS GROUP BY UNIT_ID ) T1 JOIN oceanbase.DBA_OB_UNITS T2 ON T1.UNIT_ID = T2.UNIT_ID ); ``` 返回结果如下： ```shell +---------+-----------------+----------+-------+-----------+---------+---------+------------+------------+---------------------+---------------------+-------------+------------------+-----------------+ | UNIT_ID | SVR_IP | SVR_PORT | ZONE | TENANT_ID | MIN_CPU | MAX_CPU | MIN_MEMORY | MAX_MEMORY | MIN_IOPS | MAX_IOPS | IOPS_WEIGHT | DATA_DISK_IN_USE | LOG_DISK_IN_USE | +---------+-----------------+----------+-------+-----------+---------+---------+------------+------------+---------------------+---------------------+-------------+------------------+-----------------+ | 1 | xxx.xxx.xxx.xxx | 2882 | zone1 | 1 | 4 | 4 | 5368709120 | 5368709120 | 9223372036854775807 | 9223372036854775807 | 4 | 142606336 | 2103018093 | | 1001 | xxx.xxx.xxx.xxx | 2882 | zone1 | 1002 | 1 | 1 | 5368709120 | 5368709120 | 10000 | 10000 | 1 | 104857600 | 1915370236 | | 1002 | xxx.xxx.xxx.xxx | 2882 | zone1 | 1008 | 1 | 1 | 5368709120 | 5368709120 | 10000 | 10000 | 1 | 111149056 | 1921953986 | +---------+-----------------+----------+-------+-----------+---------+---------+------------+------------+---------------------+---------------------+-------------+------------------+-----------------+ ``` ### 查询事务参与者信息 - sys 租户 ``` 2. 执行以下命令查询事务参与者信息。 ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = ; ```shell obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = ; ``` ### 获取死锁检测信息 - sys 租户 ``` 2. 执行以下命令获取死锁检测信息。 ```shell obclient> SELECT event_id, svr_ip, svr_port, report_time, cycle_idx as cycle_index, cycle_size, role, visitor, object as queryResource, extra_name1,extra_value1 from oceanbase.DBA_OB_DEADLOCK_EVENT_HISTORY; ```shell obclient> SELECT event_id, svr_ip, svr_port, report_time, cycle_idx as cycle_index, cycle_size, role, visitor, object as queryResource, extra_name1,extra_value1 from DBA_OB_DEADLOCK_EVENT_HISTORY; ``` ### 获取 elapsed_time 排序最近 M 分钟内的 TOP N 的 SQL ``` 2. 执行以下命令获取 elapsed_time 排序最近 M 分钟内的 top N 的 SQL。 ```shell obclient> SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_name, db_name, svr_ip, plan_id, plan_type, affected_rows, return_rows, elapsed_time, execute_time, sql_id, usec_to_time(request_time), substr( replace(query_sql, '\n', ' '), 1, 100 ) as query_sql FROM oceanbase.gv$ob_sql_audit WHERE 1 = 1 AND request_time > (time_to_usec(now()) - 10 * 60 * 1000000) AND is_inner_sql = 0 -- and tenant_id = 1001 ORDER BY elapsed_time DESC LIMIT 10; ``` 返回结果如下： ```shell +-----------+-------------+-----------+-----------+-----------------+---------+-----------+---------------+-------------+--------------+--------------+----------------------------------+----------------------------+------------------------------------------------------------------------------------------------------+ | tenant_id | tenant_name | user_name | db_name | svr_ip | plan_id | plan_type | affected_rows | return_rows | elapsed_time | execute_time | sql_id | usec_to_time(request_time) | query_sql | +-----------+-------------+-----------+-----------+-----------------+---------+-----------+---------------+-------------+--------------+--------------+----------------------------------+----------------------------+------------------------------------------------------------------------------------------------------+ | 1 | sys | root | oceanbase | xxx.xxx.xxx.xxx | 15612 | 1 | 0 | 6 | 174162 | 168985 | AF7E6BCB1F73440DA146CE8C15EAB6D7 | 2024-02-01 16:26:31.856555 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_ | | 1 | sys | root | oceanbase | xxx.xxx.xxx.xxx | 15611 | 1 | 0 | 0 | 165347 | 160265 | D498FFF358612A2E703287E72FD5BCD9 | 2024-02-01 16:20:48.881873 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | | 1002 | mysql001 | root | oceanbase | xxx.xxx.xxx.xxx | 4369 | 1 | 0 | 0 | 16015 | 10396 | D498FFF358612A2E703287E72FD5BCD9 | 2024-02-01 16:20:51.857552 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | | 1008 | oracle001 | SYS | SYS | xxx.xxx.xxx.xxx | 0 | 0 | 0 | 0 | 4043 | 27 | 462121E10294CE06C71E415E7FB68AE1 | 2024-02-01 16:24:35.840240 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | | 1002 | mysql001 | root | oceanbase | xxx.xxx.xxx.xxx | 0 | 0 | 0 | 0 | 2558 | 30 | C31C614A7D2555779FD7DC8BD46AB97A | 2024-02-01 16:21:38.681345 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | | 1008 | oracle001 | SYS | SYS | xxx.xxx.xxx.xxx | 0 | 0 | 0 | 0 | 375 | 38 | D7C1C28C1E55278D895EC1E0426BA7A0 | 2024-02-01 16:23:57.360901 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | | 1008 | oracle001 | SYS | SYS | xxx.xxx.xxx.xxx | 0 | 0 | 0 | 0 | 359 | 24 | CC8DDE985AB3E09E4DD8D216D719D014 | 2024-02-01 16:23:36.824769 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | +-----------+-------------+-----------+-----------+-----------------+---------+-----------+---------------+-------------+--------------+--------------+----------------------------------+----------------------------+------------------------------------------------------------------------------------------------------+ ``` ### 按 QPS 排序获取业务租户最近 M 分钟执行次数最多的 TOP N 的 SQL ``` 2. 执行以下命令按 QPS 排序获取业务租户最近 M 分钟执行次数最多的 TOP N 的 SQL。 ```shell obclient> SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, sql_id, COUNT(1) / 60 qps, AVG(elapsed_time), AVG(execute_time), AVG(queue_time), AVG(return_rows), AVG(affected_rows), substr( replace(query_sql, '\n', ' '), 1, 100 ) query_sql, ret_code FROM oceanbase.gv$ob_sql_audit WHERE 1 = 1 AND request_time > (time_to_usec(now()) - 10 * 60 * 1000000) AND is_inner_sql = 0 AND tenant_id > 1000 GROUP BY tenant_id, sql_id, query_sql, ret_code ORDER BY qps DESC LIMIT 10; ``` 返回结果如下： ```shell +-----------+----------------------------------+--------+-------------------+-------------------+-----------------+------------------+--------------------+------------------------------------------------------------------------------------------------------+----------+ | tenant_id | sql_id | qps | AVG(elapsed_time) | AVG(execute_time) | AVG(queue_time) | AVG(return_rows) | AVG(affected_rows) | query_sql | ret_code | +-----------+----------------------------------+--------+-------------------+-------------------+-----------------+------------------+--------------------+------------------------------------------------------------------------------------------------------+----------+ | 1002 | D498FFF358612A2E703287E72FD5BCD9 | 0.0167 | 16015.0000 | 10396.0000 | 26.0000 | 0.0000 | 0.0000 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | 0 | | 1002 | C31C614A7D2555779FD7DC8BD46AB97A | 0.0167 | 2558.0000 | 30.0000 | 59.0000 | 0.0000 | 0.0000 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | -5217 | | 1008 | CC8DDE985AB3E09E4DD8D216D719D014 | 0.0167 | 359.0000 | 24.0000 | 26.0000 | 0.0000 | 0.0000 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | -5001 | | 1008 | D7C1C28C1E55278D895EC1E0426BA7A0 | 0.0167 | 375.0000 | 38.0000 | 29.0000 | 0.0000 | 0.0000 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | -5001 | | 1008 | 462121E10294CE06C71E415E7FB68AE1 | 0.0167 | 4043.0000 | 27.0000 | 27.0000 | 0.0000 | 0.0000 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ tenant_id, tenant_name, user_na | -5055 | +-----------+----------------------------------+--------+-------------------+-------------------+-----------------+------------------+--------------------+------------------------------------------------------------------------------------------------------+----------+ ``` ### 按 SQLID 查找最近执行的 N 个 SQL 详情 ``` 2. 执行以下命令按 SQLID 查找最近执行的 N 个 SQL 详情。 ```shell obclient> SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ * FROM oceanbase.gv$ob_sql_audit WHERE 1 = 1 -- AND sql_id = 'xxx' ORDER BY request_time DESC LIMIT 10; ``` 返回结果如下： ```shell +-----------------+----------+------------+-------------+-----------------------------------+------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+ | SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | PARAMS_VALUE | RULE_NAME | PARTITION_HIT | TX_INTERNAL_ROUTING | TX_STATE_VERSION | FLT_TRACE_ID | +-----------------+----------+------------+-------------+-----------------------------------+------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+ | xxx.xxx.xxx.xxx | 2882 | 172258887 | 645419910 | YB42AC1E87FF-00060CEDF90ED4FF-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1007 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ls_meta_table WHERE tenant_id = 1008 ORDER BY tenant_id, ls_id, svr_ip, svr_port | 22669 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442238019 | 224 | 0 | 0 | 0 | 0 | 91 | 133 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 98304 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 1706776442210247610 | 1 | 0 | NULL | 17719762375429218827 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 172258886 | 645419906 | YB42AC1E87FF-00060CEDFB7FB7DC-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1007 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 24D486F20861EBD9E0217692DB0E9C61 | update __all_weak_read_service set min_version=1706776441906751354, max_version=1706776441906751354 where tenant_id = 1008 and level_id = 0 and level_value = '' and min_version = 1706776441704366019 and max_version = 1706776441704366019 | 26291 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442153906 | 286 | 0 | 0 | 0 | 0 | 75 | 211 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 254328 | 0 | 0 | NULL | 0 | -1 | -1 | 52106236 | 1706776442108922656 | 1 | 0 | NULL | 10435171333981900908 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 172227055 | 645419905 | YB42AC1E87FF-00060CEDFB6FBD80-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1001 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 24D486F20861EBD9E0217692DB0E9C61 | update __all_weak_read_service set min_version=1706776441805379279, max_version=1706776441805379279 where tenant_id = 1002 and level_id = 0 and level_value = '' and min_version = 1706776441595389462 and max_version = 1706776441595389462 | 24548 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442146032 | 444 | 0 | 0 | 0 | 0 | 122 | 322 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 254328 | 0 | 0 | NULL | 0 | -1 | -1 | 52094481 | 1706776442108872353 | 1 | 0 | NULL | 10435171333981900908 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 172258885 | 645419904 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1007 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1706776442102633 | 229 | 0 | 0 | 0 | 0 | 21 | 208 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 131136 | 0 | 0 | NULL | 0 | -1 | -1 | 52106235 | 0 | 1 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 172258884 | 645419903 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1007 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 5BDB02D3E0C0B003B5D7630BC63B6E63 | SELECT * FROM __all_zone_merge_info WHERE tenant_id = '1008' | 25538 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442102473 | 116 | 0 | 0 | 0 | 0 | 24 | 92 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 155648 | 0 | 0 | NULL | 0 | -1 | -1 | 52106235 | 1706776442083566902 | 1 | 0 | NULL | 16683907568006892574 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 172258883 | 645419902 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1007 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 61CB6C89EBDB0217851C65235BC5DA23 | SELECT * FROM __all_service_epoch WHERE tenant_id = '1008' AND name = 'freeze_service_epoch' FOR UPDATE | 26295 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442102285 | 151 | 0 | 0 | 0 | 0 | 27 | 124 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 292488 | 0 | 0 | NULL | 0 | -1 | -1 | 52106235 | 1706776442083566902 | 1 | 0 | NULL | 423179735753002061 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 172258882 | 645419901 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1007 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | B7A6FA97FEC98C06F9586D23935AC4C6 | START TRANSACTION | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1706776442102216 | 48 | 0 | 0 | 0 | 0 | 41 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 131072 | 0 | 0 | NULL | 0 | -1 | -1 | 52106235 | 0 | 1 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 54501730 | 645419899 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1008 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1706776442101019 | 286 | 0 | 0 | 0 | 0 | 20 | 266 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 131072 | 0 | 0 | NULL | 0 | -1 | -1 | 10393783 | 0 | 1 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 54501729 | 645419898 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1008 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | 556CEFD22F28DDDCB6E283DF89BD9348 | UPDATE __all_core_table SET column_value = 1706776442100585023 WHERE table_name = '__all_global_stat' AND column_name = 'snapshot_gc_scn' AND column_value < 1706776442100585023 | 259 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442100596 | 383 | 0 | 0 | 0 | 0 | 27 | 356 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 345552 | 0 | 0 | NULL | 0 | -1 | -1 | 10393783 | 1706776442100585023 | 1 | 0 | NULL | 7744860154259363870 | 0 | | | 1 | 0 | 0 | | | xxx.xxx.xxx.xxx | 2882 | 54501728 | 645419897 | YB42AC1E87FF-00060CEDBD9FE2F2-0-0 | 1 | 0.0.0.0 | 0 | 1 | sys | 1008 | 200001 | root | 0 | 0.0.0.0 | 201001 | oceanbase | FD4390F00BA50EBCC4BBAE0C3A06BD8E | SELECT column_value FROM __all_core_table WHERE TABLE_NAME = '__all_global_stat' AND COLUMN_NAME = 'snapshot_gc_scn' FOR UPDATE | 238 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1706776442099891 | 648 | 0 | 0 | 0 | 0 | 32 | 616 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 349832 | 0 | 0 | NULL | 0 | -1 | -1 | 10393783 | 1706776442058495579 | 1 | 0 | NULL | 10950220524135603512 | 0 | | | 1 | 0 | 0 | | +-----------------+----------+------------+-------------+-----------------------------------+------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+ ``` ### 查看某租户在各 server 上最近 M 分钟的 QPS ``` 2. 执行以下命令查看某租户在各 server 上最近 M 分钟的 QPS。 ```shell obclient> SELECT t2.zone, t1.tenant_id, t1.svr_ip, COUNT(*) / 10 / 60 AS qps, AVG(t1.elapsed_time), AVG(t1.queue_time), AVG(get_plan_time), AVG(execute_time) FROM oceanbase.gv$ob_sql_audit t1, oceanbase.dba_ob_servers t2 WHERE t1.svr_ip = t2.svr_ip -- AND t1.tenant_id = 1001 AND is_executor_rpc = 0 AND request_time > (time_to_usec(now()) - 10 * 60 * 1000000) GROUP BY t1.tenant_id, t1.svr_ip ORDER BY qps; ``` 返回结果如下： ```shell +-------+-----------+-----------------+-------------+----------------------+--------------------+--------------------+-------------------+ | zone | tenant_id | svr_ip | qps | AVG(t1.elapsed_time) | AVG(t1.queue_time) | AVG(get_plan_time) | AVG(execute_time) | +-------+-----------+-----------------+-------------+----------------------+--------------------+--------------------+-------------------+ | zone1 | 1008 | xxx.xxx.xxx.xxx | 0.00666667 | 658.7500 | 16.7500 | 589.2500 | 36.7500 | | zone1 | 1 | xxx.xxx.xxx.xxx | 96.60333333 | 229.1685 | 0.0010 | 52.7417 | 176.4250 | +-------+-----------+-----------------+-------------+----------------------+--------------------+--------------------+-------------------+ ``` ### 查看租户 TOP N 个最消耗 CPU 的 SQLID ```shell obclient> SELECT sql_id, SUM(elapsed_time - queue_time) sum_t, COUNT(*) cnt, AVG(get_plan_time), AVG(execute_time), substr( replace(query_sql, '\n', ' '), 1, 100 ) query_sql FROM oceanbase.gv$ob_sql_audit WHERE tenant_id = tenant_id AND is_executor_rpc = 0 AND request_time > (time_to_usec(now()) - 10 * 60 * 1000000) GROUP BY sql_id ORDER BY sum_t DESC LIMIT 10; ``` 返回结果如下： ```shell +----------------------------------+---------+------+--------------------+-------------------+------------------------------------------------------------------------------------------------------+ | sql_id | sum_t | cnt | AVG(get_plan_time) | AVG(execute_time) | query_sql | +----------------------------------+---------+------+--------------------+-------------------+------------------------------------------------------------------------------------------------------+ | 24D486F20861EBD9E0217692DB0E9C61 | 2190954 | 6291 | 84.8765 | 263.3915 | update __all_weak_read_service set min_version=1706776140917507852, max_version=170677614091750 | | DC5A1BC0A35FF338552F83EABA2D5786 | 1322267 | 1 | 6673.0000 | 1315567.0000 | SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/ * | | 1532BA78C664771E7113567D8E951B51 | 1079787 | 1158 | 38.8359 | 893.6226 | SELECT FIELD FROM `oceanbase`.`__tenant_virtual_table_column` WHERE TABLE_ID = 20001 | | D6BA704B31B7CC9E69F310B99764AAE6 | 929352 | 424 | 40.3774 | 2151.4906 | SELECT FIELD FROM "SYS"."TENANT_VIRTUAL_TABLE_COLUMN" WHERE TABLE_ID = 25001 | | 18AB15790D884E6A4823C892456E8650 | 712526 | 1660 | 60.1506 | 369.0819 | SELECT arbitration_member FROM __all_virtual_log_stat WHERE tenant_id = 1 AND ls_id = 1 AND role = ' | | 1D0BA376E273B9D622641124D8C59264 | 688618 | 5388 | 21.9929 | 105.8129 | COMMIT | | F171B4806D7261AD7C4293472A6741F5 | 532461 | 328 | 28.5000 | 1594.8567 | select sync_scn, min_wrs from (select min(greatest(create_scn, sync_scn)) as sync_scn from __all_ls_ | | 32AB97A0126F566064F84DDDF4936F82 | 517920 | 2434 | 83.1927 | 129.5929 | SELECT * FROM __all_ls_meta_table WHERE tenant_id = 1001 ORDER BY tenant_id, ls_id, svr_ip, svr_port | | 735537F7B5DB7C4E0E946C9B26108560 | 460897 | 2128 | 74.8501 | 141.7368 | SELECT * FROM __all_spatial_reference_systems WHERE (SRS_ID < 70000000 AND SRS_ID != 0) OR SRS_ID > | | 664BF7B4A5CCEC68B4A59C0132E738BA | 414477 | 2828 | 54.1093 | 92.4526 | SELECT * FROM __all_ls_status WHERE tenant_id = 1001 ORDER BY tenant_id, ls_id | +----------------------------------+---------+------+--------------------+-------------------+------------------------------------------------------------------------------------------------------+ ``` ## 巡检 | 模块 | 视图名 | 视图说明 | 备注 | | --- | --- | --- | --- | | 巡检 | SYS 租户：[oceanbase.CDB_OB_DATABASES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219587) | 系统租户下展示所有租户的数据库元信息。 | | 巡检 | SYS 租户：[oceanbase.CDB_OB_MAJOR_COMPACTION](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219476) | 系统租户下展示所有租户的合并全局信息。 | | 巡检 | - SYS 租户：[oceanbase.DBA_SEQUENCES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219510) - MySQL 租户：[oceanbase.DBA_SEQUENCES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220091) - Oracle 租户：[DBA_SEQUENCES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219510) | 该视图用于展示当前租户下的所有序列。 | | 巡检 | SYS 租户：[oceanbase.CDB_PART_TABLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219652) | 该视图用于展示所有租户的分区表的分区信息。 | | 巡检 | SYS 租户：[oceanbase.CDB_TAB_COLS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219475) | 该视图用于展示所有租户的表、视图对象的列信息。 | | 巡检 | SYS 租户：[oceanbase.CDB_TABLES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219645) | 该视图用于展示所有租户的 TABLE 信息。 | | 巡检 | SYS 租户：[oceanbase.CDB_TAB_PARTITIONS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219525) | 该视图用于展示所有租户的分区表的一级分区信息。 | ## 备份恢复 | 模块 | 视图名 | 视图说明 | 备注 | | --- | --- | --- | --- | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_ARCHIVELOG_SUMMARY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219659) | 该视图用于展示历史以及当前所有的日志归档的状态。 | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_BACKUP_JOBS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219663) | 该视图用于展示集群下所有租户的备份 JOB 任务。 | 用户发起一次备份任务，记录一条 JOB 任务记录。 | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_BACKUP_JOB_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219565) | 该视图用于展示集群下所有租户的备份 JOB 任务的历史记录。 | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_RESTORE_PROGRESS](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219517) | 该视图用于展示物理恢复任务当前的执行进度。 | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_RESTORE_HISTORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219528) | 该视图用于展示物理恢复任务完成时的状态，是恢复任务结束时，对应任务在 `oceanbase.CDB_OB_RESTORE_PROGRESS` 中对应记录的快照。 | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219480) | 该视图用于展示备份集中 Piece 的状态。 | | 物理备份 | SYS 租户：[oceanbase.CDB_OB_BACKUP_SET_FILES](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219483) | 该视图用于展示备份集的文件状态。 | | 物理备份 | - SYS 租户：[oceanbase.DBA_OB_BACKUP_PARAMETER](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219593) - MySQL 租户：[oceanbase.DBA_OB_BACKUP_PARAMETER](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220109) - Oracle 租户：[DBA_OB_BACKUP_PARAMETER](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219457) | 该视图用于展示备份参数。 | | 物理备份 | - SYS 租户：[oceanbase.DBA_OB_ARCHIVE_DEST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219641) - MySQL 租户：[oceanbase.DBA_OB_ARCHIVE_DEST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220067) - Oracle 租户：[DBA_OB_ARCHIVE_DEST](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219176) | 该视图用于展示归档路径配置。 | | 物理备份 | - SYS 租户：[oceanbase.DBA_OB_BACKUP_DELETE_POLICY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219498) - MySQL 租户：[oceanbase.DBA_OB_BACKUP_DELETE_POLICY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000220015) - Oracle 租户：[DBA_OB_BACKUP_DELETE_POLICY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000219342) | 该视图用于展示备份参数。 | ## 备份恢复相关视图查询示例 ### 获取某租户最新一轮的日志备份任务 ```shell obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A ``` 2. 执行以下命令获取某租户最新一轮的日志备份任务。 ```shell obclient> SELECT incarnation, round_id AS log_archive_round, tenant_id, path AS backup_dest, start_scn_display AS min_first_time, checkpoint_scn_display AS max_next_time, status, truncate((time_to_usec(now()) - checkpoint_scn / 1000) / 1000000, 4) AS delay, now(6) AS check_time FROM oceanbase.CDB_OB_ARCHIVELOG_SUMMARY WHERE tenant_id = AND round_id = (SELECT MAX(round_id) FROM oceanbase.CDB_OB_ARCHIVELOG_SUMMARY WHERE tenant_id = ); ``` ### 获取集群所有的数据备份任务（租户维度） ``` 2. 执行以下命令获取集群所有的数据备份任务。 ```shell obclient> SELECT incarnation, tenant_id, backup_set_id, backup_type, path AS backup_dest, start_timestamp as start_time, end_timestamp as end_time, now(6) as check_time, status, comment, description, 'CLUSTER' as backup_level FROM ( SELECT incarnation, tenant_id, backup_set_id, backup_type, path, start_timestamp, end_timestamp, status, comment, description FROM oceanbase.CDB_OB_BACKUP_JOBS UNION SELECT incarnation, tenant_id, backup_set_id, backup_type, path, start_timestamp, end_timestamp, status, comment, description FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY) ORDER BY start_time DESC; ``` ### 获取租户恢复任务 ``` 2. 执行以下命令获取租户恢复任务。 ```shell obclient> SELECT job_id, backup_cluster_name, backup_tenant_name, backup_tenant_id, backup_dest, restore_tenant_name, restore_tenant_id, restore_option, restore_scn_display AS restore_finish_timestamp, start_timestamp AS start_time, finish_timestamp AS completion_time, status, comment AS error_msg, description FROM ( SELECT job_id, tenant_id, backup_cluster_name, backup_tenant_name, backup_tenant_id, backup_dest, restore_tenant_name, restore_tenant_id, restore_option, restore_scn_display, start_timestamp, NULL AS finish_timestamp, status, NULL AS comment, description FROM oceanbase.CDB_OB_RESTORE_PROGRESS UNION SELECT job_id, tenant_id, backup_cluster_name, backup_tenant_name, backup_tenant_id, backup_dest, restore_tenant_name, restore_tenant_id, restore_option, restore_scn_display, start_timestamp, finish_timestamp, status, comment, description FROM oceanbase.CDB_OB_RESTORE_HISTORY) RIGHT JOIN ( SELECT job_id AS _job_id, max(tenant_id) as _tenant_id, description AS _description FROM ( SELECT job_id, tenant_id, description FROM oceanbase.CDB_OB_RESTORE_PROGRESS UNION SELECT job_id, tenant_id, description FROM oceanbase.CDB_OB_RESTORE_HISTORY ) GROUP BY _job_id ) AS t ON tenant_id = t._tenant_id AND description = t._description; ``` ### 获取清理策略 - sys 租户 ``` 2. 执行以下命令获取清理策略。 ```shell obclient> SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = ; ```shell obclient -h172.30.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A ```shell obclient -h172.30.xx.xx -P2883 -usys@oracle001#cluster -p**** -A ``` 上一篇 [OceanBase 数据库知识导图](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000324072) 下一篇 [常用命令汇总](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001685025) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线