运维相关视图
下边展示了 OceanBase 数据库运维过程中的常用视图。
| 模块 | 视图名 | 视图说明 | 备注 |
|---|---|---|---|
| 集群运维 | SYS 租户：oceanbase.DBA_OB_ZONES | 该视图用于展示所有 Zone 的信息。 | |
| 集群运维 | SYS 租户：oceanbase.CDB_OB_LS_LOCATIONS | 该视图用于展示所有租户的日志流（ LS ）副本分布信息。 | 也可以通过该视图获取集群 Root Server 相关信息。 |
| 集群运维 | SYS 租户：oceanbase.DBA_OB_SERVERS | 该视图用于展示所有 OBServer 节点的信息。 | |
| 集群运维 |
| 该视图用于展示所有 OBServer 节点上的 Schema 信息。 | 可以通过该视图获取 Schema 未刷新的租户个数。 |
| 集群运维 | SYS 租户：oceanbase.DBA_OB_ROOTSERVICE_EVENT_HISTORY | 该视图用于展示 Root Service 的事件历史。 | |
| 集群运维 |
| 该视图用于展示展示每个字符集的比对方法信息。 | |
| 合并管理 |
| 该视图用于展示当前租户的合并全局信息。 | |
| 合并管理 | SYS 租户：oceanbase.CDB_OB_MAJOR_COMPACTION | 该视图用于在系统租户下展示所有租户的合并全局信息。 | |
| 合并管理 |
| 该视图用于展示 TABLET 级 COMPACTION 的历史信息。 | |
| 租户管理 |
| 该视图用于展示租户的基本信息。 |
|
| 租户管理 | SYS 租户：oceanbase.DBA_OB_UNIT_CONFIGS | 该视图用于展示所有租户的 Unit 规格信息。 | |
| 租户管理 | SYS 租户：oceanbase.DBA_OB_RESOURCE_POOLS | 该视图用于展示所有租户的资源池信息。 | 需要 join 其他视图 |
| 租户管理 | SYS 租户：oceanbase.DBA_OB_UNITS | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 |
| 租户管理 | SYS 租户：oceanbase.DBA_OB_TENANT_JOBS | 该视图用于展示租户级别的任务信息。 | |
| 租户管理 | SYS 租户：oceanbase.CDB_OB_TABLET_REPLICAS | 该视图用于展示本租户的所有 TABLET 副本信息。 | 每个 TABLET 副本由联合键唯一确定：<TENANT_ID, TABLET_ID, SVR_IP, SVR_PORT, LS_ID>。 |
| 租户管理 |
| 该视图用于展示本租户的所有 TABLET 副本信息。 | 每个 TABLET 副本由联合键唯一确定：<TENANT_ID, TABLET_ID, SVR_IP, SVR_PORT, LS_ID>。 |
| 租户管理 | SYS 租户：oceanbase.CDB_OB_TABLE_LOCATIONS | 该视图用于展示表或者分区所在的位置，包括：系统表、用户表、索引表等。 | |
| 租户管理 |
| 该视图用于展示表或者分区所在的位置，包括：系统表、用户表、索引表等。 | |
| 资源管理 | SYS 租户：oceanbase.DBA_OB_UNITS | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 |
| 资源管理 |
| 该视图用于展示租户所在的 OBServer 节点的 Unit 信息（资源统计）。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 |
| 资源管理 | SYS 租户：GV$OB_SERVERS | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | |
| 参数管理 |
| 该视图用于展示租户所在的所有 OBServer 节点上的配置项信息(集群/租户配置项)。 | |
| MySQL 数据库管理 |
| 展示租户的数据库元信息。 | |
| 用户及权限管理 |
| 该视图用于展示用户列表以及用户级别的权限信息（VIRTUAL_TABLE）。 | |
| 用户及权限管理 |
| 该视图用于展示数据库级别的权限信息。 | |
| 用户及权限管理 | Oracle 租户：DBA_USERS | 该视图用于展示数据库所有用户的信息。 | |
| 用户及权限管理 | Oracle 租户：DBA_ROLES | 该视图用于展示数据库中存在的所有角色。 | |
| 用户及权限管理 |
| 该视图用于展示数据库所有数据库对象。 | |
| 用户及权限管理 | Oracle 租户：DBA_SYS_PRIVS | 该视图用于展示授予用户和角色的系统特权。此视图不显示 USERNAME 列。 | |
| 用户及权限管理 | Oracle 租户：DBA_ROLE_PRIVS | 该视图用于展示授予所有用户的角色和数据库中的角色。 | |
| 用户及权限管理 | Oracle 租户：DBA_TAB_PRIVS | 该视图用于展示数据库中的所有对象授权情况。 | |
集群运维相关视图查询示例
查看集群名称与集群 ID
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 Zone 信息。
obclient > SELECT * FROM oceanbase.gv$ob_parameters WHERE name IN ('cluster', 'cluster_id');返回结果如下所示。
+-----------------+----------+-------+---------+-----------+------------+-----------+---------+---------------------+----------+-------------------+ | 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 信息
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 Zone 信息。
obclient > SELECT `zone`, `status`, `region`, `idc` FROM `oceanbase.DBA_OB_ZONES`;返回结果如下所示。
+-------+--------+----------------+-----+ | zone | status | region | idc | +-------+--------+----------------+-----+ | zone1 | ACTIVE | default_region | | +-------+--------+----------------+-----+
根据 IP 和端口查看 OBServer 信息
说明
仅 sys 租户下可以查看。
根据 IP 和端口查看 OBServer 信息，如 OBServer 节点所属的 Zone、是否存在 Root Service 服务、OBServer 节点上次下线的时间、OBServer 节点被 stop 的时间等。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看 OBServer 信息。
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';返回结果如下所示。
+------+-----------------+----------+------------+-----------------+--------+--------------------------------------------------------------------------+-----------+-------------------------+-------------------+-----------------------+ | 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 历史事件信息
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 RS 历史事件信息。
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;返回结果如下：
+----------------------------+-----------------+------------------------+-----------+--------+------------------------+------------------+------------+---------------------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取集群字符集。
obclient > SELECT `collation_name` AS collation, `character_set_name` AS charset, `id`, `is_default` FROM information_schema.collations;返回结果如下：
+-------------------------+--------------+------+------------+ | 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 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令获取集群字符集。
obclient > SELECT `collation_name` AS collation, `character_set_name` AS charset, `id`, `is_default` FROM information_schema.collations;返回结果如下：
+-------------------------+--------------+------+------------+ | 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 | | +-------------------------+--------------+------+------------+
-
获取某个租户的降级日志流
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取某个租户的降级日志流。
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 = <tenant_id>;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令获取某个租户的降级日志流。
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 = <tenant_id>;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令获取某个租户的降级日志流。
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 = <tenant_id>;
-
获取 OceanBase 集群的仲裁服务信息
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 OceanBase 集群的仲裁服务信息。
obclient > SELECT arbitration_service_key, arbitration_service, previous_arbitration_service, type FROM oceanbase.DBA_OB_ARBITRATION_SERVICE;
获取租户的合并进度
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取租户的合并进度。
obclient > SELECT 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) FROM oceanbase.GV$OB_COMPACTION_PROGRESS WHERE TENANT_ID = <tenant_id> AND COMPACTION_SCN = <COMPACTION_SCN>;返回结果如下：
+-------------------------------------------------------------------+ | 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) | +-------------------------------------------------------------------+ | 97.3011 | +-------------------------------------------------------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A -
执行以下命令获取租户的合并进度。
obclient > SELECT 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) FROM oceanbase.GV$OB_COMPACTION_PROGRESS WHERE TENANT_ID = <tenant_id> AND COMPACTION_SCN = <COMPACTION_SCN>;返回结果如下：
+-------------------------------------------------------------------+ | 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) | +-------------------------------------------------------------------+ | 100.0000 | +-------------------------------------------------------------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令获取租户的合并进度。
obclient > SELECT 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) FROM GV$OB_COMPACTION_PROGRESS WHERE TENANT_ID = <tenant_id> AND COMPACTION_SCN = <COMPACTION_SCN>;返回结果如下：
+-------------------------------------------------------------------+ | 100 * (1- SUM(UNFINISHED_TABLET_COUNT) / SUM(TOTAL_TABLET_COUNT)) | +-------------------------------------------------------------------+ | 100 | +-------------------------------------------------------------------+
-
查询租户列表
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询租户列表。
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');返回结果如下：
+-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | 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 | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A -
执行以下命令查询租户列表。
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');返回结果如下：
+-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | 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 | +-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询租户列表。
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');返回结果如下：
+-----------+-------------+-------------+--------------+---------------+--------------------+--------+--------+---------------+---------------+ | 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 规格列表
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 Unit 规格列表。
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;返回结果如下：
+----------------+-----------------+---------+---------+------------+------------+---------------+---------------------+---------------------+-------------+ | 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 | +----------------+-----------------+---------+---------+------------+------------+---------------+---------------------+---------------------+-------------+
查询资源池列表
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询资源池列表。
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;返回结果如下：
+------------------+------------------+------------------+------------+----------------+-----------+-----------+--------------+------------------+---------+---------+-------------+---------------------+---------------------+---------------+ | 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 列表
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 Unit 列表。
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;返回结果如下：
+---------+------------------+------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看租户数据量。
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;返回结果如下：
+-----------+-----------------+----------+--------------+------------------+ | tenant_id | svr_ip | svr_port | data_size_mb | required_size_mb | +-----------+-----------------+----------+--------------+------------------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | 2 | 134 | +-----------+-----------------+----------+--------------+------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查看租户数据量。
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;返回结果如下：
+-----------------+----------+--------------+------------------+ | svr_ip | svr_port | data_size_mb | required_size_mb | +-----------------+----------+--------------+------------------+ | xxx.xxx.xxx.xxx | 2882 | 2 | 134 | +-----------------+----------+--------------+------------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看租户数据量。
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;返回结果如下：
+-----------------+----------+--------------+------------------+ | svr_ip | svr_port | data_size_mb | required_size_mb | +-----------------+----------+--------------+------------------+ | xxx.xxx.xxx.xxx | 2882 | 5 | 146 | +-----------------+----------+--------------+------------------+
-
查看租户表大小统计
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看租户表大小统计。
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;返回结果如下：
+-----------+-----------------+----------+-------------+--------------+------------------+ | tenant_id | svr_ip | svr_port | object_type | data_size_mb | required_size_mb | +-----------+-----------------+----------+-------------+--------------+------------------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | TABLE | 2 | 104 | +-----------+-----------------+----------+-------------+--------------+------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查看租户表大小统计。
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;返回结果如下：
+-----------------+----------+---------------+-------------+---------------+--------------+------------------+ | 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 | +-----------------+----------+---------------+-------------+---------------+--------------+------------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看租户表大小统计。
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;返回结果如下：
+-----------------+----------+---------------+-------------+-------------+--------------+------------------+ | 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 分布情况
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询租户 partition/leader 分布情况。
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 任务列表，用于租户运维后判断内核任务是否完成
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询租户 RS 任务列表，用于租户运维后判断内核任务是否完成。
obclient > SELECT TENANT_ID, JOB_ID, JOB_TYPE, JOB_STATUS, PROGRESS FROM oceanbase.DBA_OB_TENANT_JOBS WHERE TENANT_ID = <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 租户下查询所有租户的合并状态
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
SYS 租户下查询所有租户的合并状态。
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;返回结果如下：
+-----------+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ | 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 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A -
普通租户下查询当前租户的合并状态。
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;返回结果如下：
+---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+ | 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 | +---------------------+-------+--------+---------------------+---------------------+---------+------+----------------------------+----------------------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询租户列表。
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;返回结果如下：
+---------------------+-------+--------+---------------------+---------------------+---------+------+------------------+------------------+ | 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 转储历史信息
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询所有租户的 tablet 转储历史信息。
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 > <val> AND unix_timestamp(`finish_time`)*1000000 <= <val> GROUP BY tenant_id;返回结果如下:
+-----------+-------------------------+-------------------------+-------------+-----------------+--------------+ | 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 迁移情况
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 unit 迁移情况。
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;返回结果如下：
+---------+------------------+-------+-----------------+----------+--------+--------------+-----------+-------------+--------------------+---------------------+-----------------------+----------------+ | 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
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计未使用的 unit。
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/内存/磁盘）
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计租户资源。
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;返回结果如下：
+-----------+-------------+---------+---------+------------+------------+---------------------+---------------------+------------------+-----------------+ | 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/内存/磁盘）
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计集群资源。
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;返回结果如下：
+-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ | 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 | +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+
统计数据库资源
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计数据库资源。
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;返回结果如下：
+-----------+---------------+-------------+--------------+------------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询集群配置项。
obclient > SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM oceanbase.GV$OB_PARAMETERS WHERE SCOPE = 'CLUSTER' AND name = 'enable_sql_operator_dump';返回结果如下：
+-------+-----------------+----------+--------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+--------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | enable_sql_operator_dump | True | +-------+-----------------+----------+--------------------------+-------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询集群配置项。
obclient > SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM oceanbase.GV$OB_PARAMETERS WHERE SCOPE = 'CLUSTER' AND name = 'enable_sql_operator_dump';返回结果如下：
+-------+-----------------+----------+--------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+--------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | enable_sql_operator_dump | True | +-------+-----------------+----------+--------------------------+-------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询集群配置项。
obclient > SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM GV$OB_PARAMETERS WHERE SCOPE = 'CLUSTER' AND name = 'enable_sql_operator_dump';返回结果如下：
+-------+-----------------+----------+--------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+--------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | enable_sql_operator_dump | True | +-------+-----------------+----------+--------------------------+-------+
-
查询租户配置项（包括隐藏配置项）
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询租户配置项。
obclient > SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM oceanbase.GV$OB_PARAMETERS WHERE SCOPE = 'TENANT' AND NAME = 'freeze_trigger_percentage';返回结果如下：
+-------+-----------------+----------+---------------------------+-------+ | 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 | +-------+-----------------+----------+---------------------------+-------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询租户配置项。
obclient > SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM oceanbase.GV$OB_PARAMETERS WHERE SCOPE = 'TENANT' AND NAME = 'freeze_trigger_percentage';返回结果如下：
+-------+-----------------+----------+---------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+---------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | +-------+-----------------+----------+---------------------------+-------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询租户配置项。
obclient > SELECT ZONE, SVR_IP, SVR_PORT, NAME, VALUE FROM GV$OB_PARAMETERS WHERE SCOPE = 'TENANT' AND NAME = 'freeze_trigger_percentage';返回结果如下：
+-------+-----------------+----------+---------------------------+-------+ | ZONE | SVR_IP | SVR_PORT | NAME | VALUE | +-------+-----------------+----------+---------------------------+-------+ | zone1 | xxx.xxx.xxx.xxx | 2882 | freeze_trigger_percentage | 20 | +-------+-----------------+----------+---------------------------+-------+
-
在 sys 租户查询所有租户的配置项（包括隐藏配置项）
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
在 sys 租户查询所有租户的配置项。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询数据库列表。
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';返回结果如下：
+---------------------+-------------+--------------------+----------------+--------------+-----------+ | 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 | +---------------------+-------------+--------------------+----------------+--------------+-----------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询数据库列表。
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';返回结果如下：
+---------------------+-------------+--------------------+----------------+--------------+-----------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看用户列表、全局权限授权情况。
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;返回结果如下：
+--------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+ | 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 | +--------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查看用户列表、全局权限授权情况。
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;返回结果如下：
+--------------+----------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+------------+------------+--------------+------------+------------------+----------------+------------------+-------------------------------------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看数据库权限授权情况。
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;返回结果如下：
+--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+ | 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 | +--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查看数据库权限授权情况。
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;返回结果如下：
+--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+ | 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 租户下可以查看。
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看用户列表。
obclient >SELECT USERNAME, CREATED, (CASE ACCOUNT_STATUS WHEN 'OPEN' THEN 0 ELSE 1 END) AS IS_LOCKED FROM DBA_USERS;返回结果如下：
+--------------------+-----------+-----------+ | USERNAME | CREATED | IS_LOCKED | +--------------------+-----------+-----------+ | SYS | 30-OCT-23 | 0 | | LBACSYS | 30-OCT-23 | 1 | | ORAAUDITOR | 30-OCT-23 | 1 | +--------------------+-----------+-----------+
查看角色列表
说明
仅 Oracle 租户下可以查看。
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看角色列表。
obclient >SELECT ROLE FROM DBA_ROLES;返回结果如下：
+----------+ | ROLE | +----------+ | CONNECT | | RESOURCE | | DBA | | PUBLIC | +----------+
查看对象列表
说明
仅 Oracle 租户下可以查看。
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看对象列表。
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');
查看系统权限授权情况
说明
仅 Oracle 租户下可以查看。
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看系统权限授权情况。
obclient >SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DBA';返回结果如下：
+-----------------------------+ | PRIVILEGE | +-----------------------------+ | CREATE SESSION | | CREATE TABLE | | CREATE ANY TABLE | ...... | DEBUG ANY PROCEDURE | | CREATE ANY CONTEXT | | DROP ANY CONTEXT | +-----------------------------+
查看角色授权情况
说明
仅 Oracle 租户下可以查看。
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看角色授权情况。
obclient >SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = <grantee_val>; 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);
查看对象权限授权情况
说明
仅 Oracle 租户下可以查看。
-
使用 sys 用户登录到集群的 oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查看对象权限授权情况。
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 | 该视图用于展示所有 OBServer 节点的信息。 | |
| 监控 |
| 该视图用于展示每台 OBServer 节点上的 KVCACHE 信息。 | |
| 监控 | 该视图用于展示当前服务器上租户级别的统计事件信息。 | ||
| 监控 | SYS 租户：oceanbase.CDB_TABLES | 该视图用于展示所有租户的 TABLE 信息。 | |
| 监控 |
| 该视图用于展示租户的基本信息。 |
|
| 监控 |
| 该视图用于展示租户所在的所有 OBServer 节点的会话信息。 | |
| 监控 |
| 该视图用于展示当前租户在当前 OBServer 节点的计划缓存整体的状态。 | |
| 监控 |
| 该视图用于展示当前服务器上租户系统级别的一些等待事件，统计每一类等待事件的发生次数、等待时间、超时次数等。 | |
| 监控 |
| 该视图用于展示租户所在的 OBServer 节点的 Unit 信息（资源统计）。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 |
| 监控 | SYS 租户：GV$OB_SERVERS | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | |
| 监控 | SYS 租户：oceanbase.DBA_OB_TENANT_JOBS | 该视图用于展示租户级别的任务信息。 | |
| 监控 | SYS 租户：oceanbase.DBA_OB_UNIT_JOBS | 该视图用于展示所有 UNIT 相关任务。 | |
| 监控 | SYS 租户：oceanbase.DBA_OB_SERVER_JOBS | 该视图用于展示所有 OBServer 节点相关任务。 | |
| 监控 |
| 该视图用于展示每台 OBServer 节点上的各分区下的 MemTable 和 SSTable 信息。 | |
| 监控 |
| 该视图用于展示日志流 Palf 的状态。 |
|
| 监控 | SYS 租户：oceanbase.CDB_INDEXES | 该视图用于展示所有租户的 INDEXE 信息。 | |
| 监控 |
| 该视图用于展示所有服务器上所有租户的 Memtable 的内存使用状况。 | |
| 监控 | SYS 租户：oceanbase.CDB_OB_MAJOR_COMPACTION | 系统租户下展示所有租户的合并全局信息。 | |
| 监控 |
| 该视图用于展示所有服务器上每个租户的内存使用状况。 | |
| 监控 | SYS 租户：oceanbase.DBA_OB_RESOURCE_POOLS | 该视图用于展示所有租户的资源池信息。 | |
| 监控 | SYS 租户：oceanbase.DBA_OB_UNIT_CONFIGS | 该视图用于展示所有租户的 Unit 规格信息。 | |
| 监控 | SYS 租户：oceanbase.DBA_OB_UNITS | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 |
| 监控 | 该视图用于展示集群中所有 OBServer 节点的 Latch 信息视图。 | ||
| 监控 | SYS 租户：oceanbase.DBA_OB_ROOTSERVICE_EVENT_HISTORY | 该视图用于展示 Root Service 的事件历史。 | |
监控相关视图查询示例
查看连接情况
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看连接情况。
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;返回结果如下：
+-----------------+----------+-------------+------------+---------+ | 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 | +-----------------+----------+-------------+------------+---------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查看连接情况。
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;返回结果如下：
+-----------------+----------+-------------+------------+---------+ | svr_ip | svr_port | tenant_name | active_cnt | all_cnt | +-----------------+----------+-------------+------------+---------+ | xxx.xxx.xxx.xxx | 2882 | mysql001 | 1 | 1 | +-----------------+----------+-------------+------------+---------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 cache_size。
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;返回结果如下：
+-----------------+----------+--------------+------------+---------+ | svr_ip | svr_port | tenant_name | active_cnt | all_cnt | +-----------------+----------+--------------+------------+---------+ | xxx.xxx.xxx.xxx | 2882 | oracle001 | 1 | 1 | +-----------------+----------+--------------+------------+---------+
-
判断 server 是否是 RS 所在
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令判断 server 是否是 RS 所在。
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';返回结果如下：
+-----------------+ | with_rootserver | +-----------------+ | 1 | +-----------------+
统计 server 数量
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 server 数量。
obclient >select /* MONITOR_AGENT */ group_concat(svr_ip SEPARATOR ',') as servers, status, count(1) as cnt from oceanbase.DBA_OB_SERVERS group by status;返回结果如下：
+-----------------+--------+------+ | servers | status | cnt | +-----------------+--------+------+ | xxx.xxx.xxx.xxx | ACTIVE | 1 | +-----------------+--------+------+
统计 cache_size
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 cache_size。
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';返回结果如下：
+-----------+-----------------------+------------+ | 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 | +-----------+-----------------------+------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计 cache_size。
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';返回结果如下：
+-----------+-----------------------+------------+ | 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 | +-----------+-----------------------+------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 cache_size。
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';返回结果如下：
+-----------+-----------------------+------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 OceanBase 数据库系统信息。
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;返回结果如下：
+-----------+---------+-------------+ | tenant_id | stat_id | value | +-----------+---------+-------------+ | 1 | 10000 | 3484431 | | 1 | 10000 | 3484431 | ...... | 1008 | 140006 | 0 | | 1008 | 140006 | 0 | +-----------+---------+-------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计 OceanBase 数据库系统信息。
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;返回结果如下：
+-----------+---------+------------+ | tenant_id | stat_id | value | +-----------+---------+------------+ | 1002 | 10000 | 696278 | | 1002 | 10001 | 282624028 | ...... | 1002 | 140003 | 898105344 | | 1002 | 140005 | 100 | | 1002 | 140006 | 0 | +-----------+---------+------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 OceanBase 数据库系统信息。
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;返回结果如下：
+-----------+---------+------------+ | TENANT_ID | STAT_ID | VALUE | +-----------+---------+------------+ | 1008 | 10000 | 694680 | | 1008 | 10001 | 281931326 | ...... | 1008 | 140005 | 100 | | 1008 | 140006 | 0 | +-----------+---------+------------+
-
统计所有租户表数量
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计租户表数量。
obclient >select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ con_id tenant_id, count(*) as cnt from oceanbase.CDB_TABLES group by con_id;返回结果如下：
+-----------+------+ | tenant_id | cnt | +-----------+------+ | 1 | 263 | | 1001 | 242 | | 1002 | 168 | | 1007 | 242 | | 1008 | 164 | +-----------+------+
session 信息
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 session 信息。
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;返回结果如下：
+------+-------------+-----------+ | cnt | tenant_name | tenant_id | +------+-------------+-----------+ | 1 | sys | 1 | | 0 | mysql001 | 1002 | | 0 | oracle001 | 1008 | +------+-------------+-----------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计 session 信息。
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;返回结果如下：
+------+-------------+-----------+ | cnt | tenant_name | tenant_id | +------+-------------+-----------+ | 1 | mysql001 | 1002 | +------+-------------+-----------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 session 信息。
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;返回结果如下：
+------+-------------+-----------+ | CNT | TENANT_NAME | TENANT_ID | +------+-------------+-----------+ | 1 | oracle001 | 1008 | +------+-------------+-----------+
-
统计 plan cache
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 plan cache。
obclient >select /* MONITOR_AGENT */ tenant_id, mem_used, access_count, hit_count from oceanbase.V$OB_PLAN_CACHE_STAT;返回结果如下：
+-----------+-----------+--------------+-----------+ | 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 | +-----------+-----------+--------------+-----------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计 plan cache。
obclient >select /* MONITOR_AGENT */ tenant_id, mem_used, access_count, hit_count from oceanbase.V$OB_PLAN_CACHE_STAT;返回结果如下：
+-----------+-----------+--------------+-----------+ | tenant_id | mem_used | access_count | hit_count | +-----------+-----------+--------------+-----------+ | 1002 | 130698360 | 824256 | 823009 | +-----------+-----------+--------------+-----------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 plan cache。
obclient >select /* MONITOR_AGENT */ mem_used, access_count, hit_count from oceanbase.V$OB_PLAN_CACHE_STAT;返回结果如下：
+-----------+--------------+-----------+ | MEM_USED | ACCESS_COUNT | HIT_COUNT | +-----------+--------------+-----------+ | 125921089 | 856104 | 854561 | +-----------+--------------+-----------+
-
统计系统事件（按租户分类）
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计系统事件。
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;返回结果如下：
+-----------+-------------+---------------+ | 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 | +-----------+-------------+---------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计系统事件。
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;返回结果如下：
+-----------+-------------+--------------+ | tenant_id | total_waits | time_waited | +-----------+-------------+--------------+ | 1002 | 145985517 | 1464182.2271 | +-----------+-------------+--------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计系统事件。
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;返回结果如下：
+-----------+-------------+----------------+ | TENANT_ID | TOTAL_WAITS | TIME_WAITED | +-----------+-------------+----------------+ | 1008 | 145803716 | 1462353.823324 | +-----------+-------------+----------------+
-
统计系统事件（按 event 分类）
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计系统事件。
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;返回结果如下：
+-----------+---------------+-------------+-----------------+ | 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 | +-----------+---------------+-------------+-----------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计系统事件。
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;返回结果如下：
+-----------+---------------+-------------+---------------+ | 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 | +-----------+---------------+-------------+---------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计系统事件。
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;返回结果如下：
+-----------+---------------+---------------+--------------------+ | 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
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计磁盘 size。
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';返回结果如下：
+--------------+--------------+ | total_size | free_size | +--------------+--------------+ | 107374182400 | 107002986496 | +--------------+--------------+
统计系统任务执行时间
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计系统任务执行时间。
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';
统计租户任务执行时间
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计租户任务执行时间。
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 正在执行的任务执行时间
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 server 正在执行的任务执行时间。
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 所有任务执行时间
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 server 所有任务执行时间。
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 快照时间
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 memtable 快照时间。
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;
日志流同步延迟
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计日志流同步延迟。
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;
查询索引状态
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询索引状态。
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;
获取集群中无效索引的个数
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计索引错误数。
obclient >select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from oceanbase.CDB_INDEXES where status in ('ERROR','UNUSABLE');
统计 memstore 信息
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 memstore 信息。
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';返回结果如下：
+-----------+-----------+-----------+----------------+------------+ | 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 | +-----------+-----------+-----------+----------------+------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计 memstore 信息。
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';返回结果如下：
+-----------+---------+---------+----------------+------------+ | tenant_id | active | total | freeze_trigger | freeze_cnt | +-----------+---------+---------+----------------+------------+ | 1002 | 6291456 | 6291456 | 463052304 | 0 | +-----------+---------+---------+----------------+------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 memstore 信息。
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';返回结果如下：
+-----------+----------+----------+----------------+------------+ | TENANT_ID | ACTIVE | TOTAL | FREEZE_TRIGGER | FREEZE_CNT | +-----------+----------+----------+----------------+------------+ | 1008 | 12582912 | 12582912 | 463052304 | 0 | +-----------+----------+----------+----------------+------------+
-
查看租户 memstore 信息
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计 memstore 信息。
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;返回结果如下：
+-----------+-------------+------------+--------------------+-----------------------+ | tenant_id | tenant_name | freeze_cnt | minor_freeze_times | compact_trigger_ratio | +-----------+-------------+------------+--------------------+-----------------------+ | 1002 | mysql001 | 0 | 0 | NULL | | 1008 | oracle001 | 0 | 0 | NULL | +-----------+-------------+------------+--------------------+-----------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计 memstore 信息。
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;返回结果如下：
+-----------+-------------+------------+--------------------+-----------------------+ | tenant_id | tenant_name | freeze_cnt | minor_freeze_times | compact_trigger_ratio | +-----------+-------------+------------+--------------------+-----------------------+ | 1002 | mysql001 | 0 | 0 | NULL | +-----------+-------------+------------+--------------------+-----------------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令统计 memstore 信息。
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;
-
查看转储信息
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看转储信息。
obclient >SELECT * FROM oceanbase.dba_ob_rootservice_event_history WHERE event = 'root_minor_freeze' ORDER BY timestamp DESC LIMIT 30;返回结果如下：
+----------------------------+--------------+-------------------+-------+--------+-------+-------------------------------------------------------------------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+ | 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 | +----------------------------+--------------+-------------------+-------+--------+-------+-------------------------------------------------------------------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+
查看每日合并耗时
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看每日合并耗时。
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;返回结果如下：
+-----------+----------------------+----------------------------+----------------------------+-------------------+ | 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 | +-----------+----------------------+----------------------------+----------------------------+-------------------+
查询合并信息（上一个已经完成合并的版本/最近一次合并的版本号/是否存在报错）
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计合并信息。
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;返回结果如下：
+-----------+---------------------+---------------------+----------+--------------+------------------+------------------+------------------+ | 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 租户的内存使用情况
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询 500 租户的内存使用情况。
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';返回结果如下：
+------------+------------+ | hold | used | +------------+------------+ | 1202488992 | 1183703541 | +------------+------------+
按租户统计使用的内存
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令按租户统计使用的内存。
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;返回结果如下：
+-----------+-----------------+----------+---------+---------+ | 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 | +-----------+-----------------+----------+---------+---------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令按租户统计使用的内存。
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;返回结果如下：
+-----------+-----------------+----------+---------+---------+ | tenant_id | svr_ip | svr_port | hold_mb | used_mb | +-----------+-----------------+----------+---------+---------+ | 1002 | xxx.xxx.xxx.xxx | 2882 | 794 | 768 | +-----------+-----------------+----------+---------+---------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令按租户统计使用的内存。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令按模块统计使用的内存。
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;返回结果如下：
+-----------+-----------------+----------+-----------------+---------+---------+ | 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 | +-----------+-----------------+----------+-----------------+---------+---------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令按模块统计使用的内存。
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;返回结果如下：
+-----------+-----------------+----------+-----------------+---------+---------+ | 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 | +-----------+-----------------+----------+-----------------+---------+---------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令按模块统计使用的内存。
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）
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令统计租户资源。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询有关 latch 的诊断信息。
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);返回结果如下：
+-----------+------------------------------------------------+-------------+--------+--------+----------------+------------------+-------------+-----------+ | 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 | +-----------+------------------------------------------------+-------------+--------+--------+----------------+------------------+-------------+-----------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令统计诊断信息。
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);返回结果如下：
+-----------+-----------------------------------------------+---------+--------+--------+----------------+------------------+-----------+-----------+ | 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 | +-----------+-----------------------------------------------+---------+--------+--------+----------------+------------------+-----------+-----------+
-
告警
| 模块 | 视图名 | 视图说明 | 备注 |
|---|---|---|---|
| 告警 |
| 该视图用于展示租户的基本信息。 |
|
| 告警 |
| 该视图用于展示租户所在的所有 OBServer 节点的会话信息。 | |
| 告警 | SYS 租户：GV$OB_SERVERS | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | |
| 告警 | 该视图用于展示当前服务器上租户级别的统计事件信息。 | ||
| 告警 | SYS 租户：oceanbase.DBA_OB_SERVERS | 该视图用于展示所有 OBServer 节点的信息。 | |
| 告警 | SYS 租户：oceanbase.CDB_INDEXES | 该视图用于展示所有租户的 INDEXE 信息。 | |
| 告警 | SYS 租户：oceanbase.CDB_OB_MAJOR_COMPACTION | 系统租户下展示所有租户的合并全局信息。 | |
| 告警 |
| 该视图用于展示所有服务器上每个租户的内存使用状况。 | |
| 告警 |
| 该视图用于展示本 OBServer 节点的 Unit 信息。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 |
| 告警 | SYS 租户：oceanbase.DBA_OB_UNIT_JOBS | 该视图用于展示所有 UNIT 相关任务。 | |
| 告警 | SYS 租户：oceanbase.DBA_OB_TENANT_JOBS | 该视图用于展示租户级别的任务信息。 | |
| 告警 | SYS 租户：oceanbase.DBA_OB_SERVER_JOBS | 该视图用于展示所有 OBServer 节点相关任务。 | |
告警相关视图查询示例
采集活跃会话数
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令采集活跃会话数。
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;返回结果如下：
+------+-------------+-----------+ | cnt | tenant_name | tenant_id | +------+-------------+-----------+ | 1 | sys | 1 | | 0 | mysql001 | 1002 | | 0 | oracle001 | 1008 | +------+-------------+-----------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令采集活跃会话数。
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;返回结果如下：
+------+-------------+-----------+ | cnt | tenant_name | tenant_id | +------+-------------+-----------+ | 1 | mysql001 | 1002 | +------+-------------+-----------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令采集活跃会话数。
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;返回结果如下：
+------+-------------+-----------+ | CNT | TENANT_NAME | TENANT_ID | +------+-------------+-----------+ | 1 | oracle001 | 1008 | +------+-------------+-----------+
-
获取 OBServer CPU 已分配百分比
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 OBServer CPU 已分配百分比。
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';返回结果如下：
+-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。
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;返回结果如下：
+-----------+---------+-------------+ | 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 | +-----------+---------+-------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。
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;返回结果如下：
+-----------+---------+------------+ | 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 | +-----------+---------+------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。
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;返回结果如下：
+-----------+---------+------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。
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;返回结果如下：
+-----------+---------+--------------+ | 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 | +-----------+---------+--------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。
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;返回结果如下：
+-----------+---------+-----------+ | tenant_id | stat_id | value | +-----------+---------+-----------+ | 1002 | 30066 | 0 | | 1002 | 50021 | 0 | | 1002 | 50022 | 0 | ...... | 1002 | 120002 | 6241280 | | 1002 | 120005 | 0 | | 1002 | 120006 | 389101568 | +-----------+---------+-----------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令采集 OceanBase 统计信息，如 QPS，TPS，CPU 使用率等。
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;返回结果如下：
+-----------+---------+-----------+ | 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 个数
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取各个状态 OBServer 个数。
obclient >select /* MONITOR_AGENT */ group_concat(svr_ip SEPARATOR ',') as servers, status, count(1) as cnt from oceanbase.DBA_OB_SERVERS group by status;
获取集群中无效索引的个数
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取集群索引无效的个数。
obclient >select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from oceanbase.CDB_INDEXES where status in ('ERROR','UNUSABLE');
获取 OBServer 已分配内存百分比
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 OBServer CPU 已分配百分比。
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';返回结果如下：
+-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+ | 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 | +-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+
查询租户合并信息
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询租户合并信息。
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;返回结果如下：
+-----------+---------------------+---------------------+----------+--------------+------------------+------------------+------------------+ | 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 | +-----------+---------------------+---------------------+----------+--------------+------------------+------------------+------------------+
获取 OBServer 500 租户的内存占用
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 OBServer 500 租户的内存占用。
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';返回结果如下：
+------------+------------+ | hold | used | +------------+------------+ | 1202505488 | 1183719757 | +------------+------------+
获取 SSTable 使用率
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 SSTable 使用率。
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';返回结果如下：
+--------------+--------------+ | total_size | free_size | +--------------+--------------+ | 107374182400 | 106982014976 | +--------------+--------------+
获取租户日志盘使用率
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取租户日志盘使用率。
obclient >SELECT tenant_id, log_disk_in_use, log_disk_size FROM oceanbase.GV$OB_UNITS;返回结果如下：
+-----------+-----------------+---------------+ | tenant_id | log_disk_in_use | log_disk_size | +-----------+-----------------+---------------+ | 1 | 1789104118 | 17448304640 | | 1001 | 512742114 | 644245094 | | 1002 | 1234090827 | 5798205850 | | 1007 | 506335700 | 644245094 | | 1008 | 1247515009 | 5798205850 | +-----------+-----------------+---------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令获取租户日志盘使用率。
obclient >SELECT tenant_id, log_disk_in_use, log_disk_size FROM oceanbase.GV$OB_UNITS;返回结果如下：
+-----------+-----------------+---------------+ | tenant_id | log_disk_in_use | log_disk_size | +-----------+-----------------+---------------+ | 1002 | 1234096663 | 5798205850 | +-----------+-----------------+---------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令获取租户日志盘使用率。
obclient >SELECT tenant_id, log_disk_in_use, log_disk_size FROM oceanbase.GV$OB_UNITS;返回结果如下：
+-----------+-----------------+---------------+ | TENANT_ID | LOG_DISK_IN_USE | LOG_DISK_SIZE | +-----------+-----------------+---------------+ | 1008 | 1247546517 | 5798205850 | +-----------+-----------------+---------------+
-
获取租户任务运行时长
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取租户任务运行时长。
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';
获取 OBServer 运行中的任务运行时长
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 OBServer 运行中的任务运行时长。
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';
诊断
| 模块 | 视图名 | 视图说明 | 备注 |
|---|---|---|---|
| SQL 诊断 | SYS 租户：oceanbase.CDB_OBJECTS | 系统租户下展示所有租户的对象信息。 | |
| SQL 诊断 | SYS 租户：oceanbase.CDB_INDEXES | 该视图用于展示所有租户的 INDEXE 信息。 | |
| SQL 诊断 | SYS 租户：oceanbase.CDB_IND_COLUMNS | 该视图用于展示所有租户的索引表的列信息。 | |
| SQL 详情 |
| 该视图用于查看所有的分区索引的分区信息。 | |
| SQL 详情 | MySQL 租户：information_schema.TABLE_CONSTRAINTS | 该视图用于描述表约束信息。 | |
| SQL 详情 | MySQL 租户：information_schema.STATISTICS | 该视图用于记录表索引信息。 | |
| SQL 详情 | Oracle 租户：DBA_IND_COLUMNS | 该视图用于查看数据库所有表的索引的索引列信息。 | |
| SQL 详情 | Oracle 租户：DBA_INDEXES | 该视图用于查看数据库所有表的索引信息。 | |
| SQL 详情 | Oracle 租户：DBA_CONSTRAINTS | 该视图用于查看数据库中所有表的约束信息。 | |
| Outline |
| 该视图用于查看所有的分区索引的分区信息。 | |
| Outline |
| 该视图用于展示本租户的限流规则 Outline 相关信息。 | |
| Outline | 该视图用于展示本租户的执行计划和限流规则 Outline 历史信息。 | ||
| 索引推荐 | MySQL 租户：information_schema.COLUMNS | 该视图用于记录列信息。 | |
| 索引推荐 | MySQL 租户：oceanbase.DBA_TAB_COL_STATISTICS | 该视图用于展示数据库中所有表的列统计信息和直方图信息。 | |
| 索引推荐 | Oracle 租户：DBA_TAB_COLUMNS | 该视图用于查看数据库中所有的表、视图的所有列。 | |
| 索引推荐 | Oracle 租户：DBA_TAB_COL_STATISTICS | 该视图用于查看数据库中所有的表、视图的所有列。 | |
| SQL 采集 |
| 该视图用于展示当前 OBServer 节点的每一次 SQL 请求的来源、执行状态等统计信息。 | 该视图是按照租户拆分的，除了系统租户，其他租户不能跨租户查询。 |
| SQL 采集 |
| 该视图用于展示所有 OBServer 节点上每一次 SQL 请求的来源、执行状态等统计信息。 | 该视图是按照租户拆分的，除了系统租户，其他租户不能跨租户查询。 |
| SQL 采集 |
| 该视图用于展示缓存在当前 OBServer 节点的计划缓存中的物理执行计划。 | |
| SQL 采集 |
| 该视图用于展示缓存在全部的 OBServer 节点中的计划缓存中的物理执行计划。 | |
| SQL 采集 |
| 该视图用于展示当前租户在所有 OBServer 节点上的计划缓存中缓存的每一个缓存对象的状态。 | 该视图不仅缓存了 SQL 计划对象，也缓存了 PL 对象（如匿名块、PL Package 以及 PL Function），某些字段只在特定对象下有效。 |
| SQL 采集 |
| 该视图用于展示当前租户在当前 OBServer 节点上的计划缓存中缓存的每一个缓存对象的状态。 | 该视图不仅缓存了 SQL 计划对象，也缓存了 PL 对象（例如匿名块、PL Package 以及 PL Function），某些字段只在特定对象下有效。 |
| 性能报告 | SYS 租户：GV$OB_SERVERS | 该视图用于展示所有 OBServer 节点的信息（资源统计）。 | |
| 性能报告 |
| 该视图用于展示租户所在的 OBServer 节点的 Unit 信息（资源统计）。 | 系统租户看到本集群所有 OBServer 节点的 Unit 信息。 |
| 性能报告 | SYS 租户：oceanbase.DBA_OB_UNITS | 该视图用于展示所有租户的 Unit 信息。 | 需要 join 其他视图 |
| 事务诊断 |
| 该视图用于展示所有 OBServer 节点上的事务参与者信息。 | |
| 死锁检测 | 该视图用于展示死锁事件的历史记录。 | 该视图在非 root 租户下展示本租户的历史记录。 | |
| 会话管理 |
| 该视图用于展示租户所在的所有 OBServer 节点的会话信息。 | |
诊断相关视图查询示例
获取数据库列表
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取数据库列表。
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 = <tenant_id> and OBJECT_TYPE = 'DATABASE';返回结果如下：
+-----------+-------------+--------------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令进行表的统计数据查询。
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 = <table_schema> and table_name = <table_name>) C left join (select * from oceanbase.DBA_TAB_COL_STATISTICS where owner = <table_schema> and table_name = <table_name>) S on S.owner = C.table_schema and S.table_name = C.table_name and S.column_name = C.column_name;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令进行表的统计数据查询。
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 = <table_schema> and table_name = <table_name>) C left join (select * from oceanbase.DBA_TAB_COL_STATISTICS where owner = <table_schema> and table_name = <table_name>) S on S.owner = C.table_schema and S.table_name = C.table_name and S.column_name = C.column_name;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令进行表的统计数据查询。
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 = <table_schema> and table_name = <table_name>) C left join (select * from DBA_TAB_COL_STATISTICS where owner = <table_schema> and table_name = <table_name>) S on S.owner = C.owner and S.table_name = C.table_name and S.column_name = C.column_name;
-
查询限流 Outline
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询限流 Outline。
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;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询限流 Outline。
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;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询限流 Outline。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询普通 outline。
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;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询普通 outline。
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;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询普通 outline。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询历史 Outline。
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, <val>) > CURDATE() and is_deleted = 'NO';
-
-
MySQL 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询历史 Outline。
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, <val>) > CURDATE() and is_deleted = 'NO';
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询历史 Outline。
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 + <val>) and is_deleted = 'NO';
-
查询索引信息
查询索引信息，如索引名称，索引列名称，注释等。
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询索引信息。
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 = <tenant_id> and I.table_owner = <table_owner> and I.table_name = <table_name> and I.status = 'VALID' group by index_name;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询索引信息。
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;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询索引信息。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取原生的 sql_audit 数据。
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 = <tenant_id> and request_id >= <val> and request_id <= <val> and (request_time + elapsed_time) >= <val> and (request_time + elapsed_time) < <val> and query_sql not like <SQL> order by request_id limit <val>) t;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令获取原生的 sql_audit 数据。
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 = <tenant_id> and request_id >= <val> and request_id <= <val> and (request_time + elapsed_time) >= <val> and (request_time + elapsed_time) < <val> and query_sql not like <SQL> order by request_id limit <val>) t;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令获取原生的 sql_audit 数据。
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 = <tenant_id> and request_id >= <val> and request_id <= <val> and (request_time + elapsed_time) >= <val> and (request_time + elapsed_time) < <val> and query_sql not like <SQL> and rownum < <val> order by request_id) t;
-
执行计划的统计信息
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询执行计划的统计信息。
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 = <tenant_id> and plan_id > <plan_id> and object_type = 'sql_plan' order by plan_id limit <val>;返回结果如下：
+------------------+-----------+--------+---------+--------------+------------------+------------+-----------+------------+------------+---------------+-------------+-----------------------+-----------------------+-------------------+----------------+--------------+----------+--------------+----------------------+------------+---------------+------------------+----------------------------------+------+---------------------+----------------+-----------+-------------------------------------------------------------------------------+ | 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*/ | +------------------+-----------+--------+---------+--------------+------------------+------------+-----------+------------+------------+---------------+-------------+-----------------------+-----------------------+-------------------+----------------+--------------+----------+--------------+----------------------+------------+---------------+------------------+----------------------------------+------+---------------------+----------------+-----------+-------------------------------------------------------------------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询执行计划的统计信息。
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 = <tenant_id> and plan_id > <plan_id> and object_type = 'sql_plan' order by plan_id limit <val>;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询执行计划的统计信息。
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 = <tenant_id> and plan_id > <plan_id> and to_char(object_type) = 'SQL_PLAN' and rownum < <val> order by plan_id;
-
执行计划的详情
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询执行计划的详情。
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 = <tenant_id> and plan_id = <plan_id> and svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882';
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询执行计划的详情。
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 = <tenant_id> and plan_id = <plan_id> and svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882';
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询执行计划的详情。
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 = <tenant_id> and plan_id = <plan_id> and svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = '2882';
-
采集事务信息
采集事务信息，例如事务类型，事务 ID，所属日志流 ID，参与者列表，事务日志大小等。
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令采集事务信息。
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';
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令采集事务信息。
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';
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询事务采集。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令采集并行 SQL 的 sql_plan_monitor 。
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 = <tenant_id> and request_id >= <val> and request_id <= <val> order by request_id limit <val>;返回结果如下：
+-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | 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 | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令采集并行 SQL 的 sql_plan_monitor 。
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 = <tenant_id> and request_id >= <val> and request_id <= <val> order by request_id limit <val>;+-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | 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 | +-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+----------------------------+----------------------------+-------------------+------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令采集并行 SQL 的 sql_plan_monitor 。
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 = <tenant_id> and request_id >= <val> and request_id <= <val> and rownum < <val> order by request_id ;返回结果如下：
+-----------+------------+-----------------------------------+--------------+------------+----------------------+------------+-------------+------------------------------+------------------------------+--------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+----------------+-------------------+-----------------+--------------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令采集会话信息。
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> <val> or (sid = <val> and event_id > <val>)) order by sid, event_id limit <val>;返回结果如下：
+------------+----------+-----------+-------------+----------------+ | session_id | event_id | tenant_id | total_waits | time_waited_us | +------------+----------+-----------+-------------+----------------+ | 3221225472 | 10001 | 1 | 2 | 334 | +------------+----------+-----------+-------------+----------------+
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令采集会话信息。
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> <val> or (sid = <val> and event_id > <val>)) order by sid, event_id limit <val>;返回结果如下：
+------------+----------+-----------+-------------+----------------+ | session_id | event_id | tenant_id | total_waits | time_waited_us | +------------+----------+-----------+-------------+----------------+ | 3221487622 | 10001 | 1002 | 12 | 1869 | +------------+----------+-----------+-------------+----------------+
-
查询资源分配情况
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询资源分配情况。
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;返回结果如下：
+-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+ | 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 | +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+
查询租户副本数据大小
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询租户副本数据大小。
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 );返回结果如下：
+---------+-----------------+----------+-------+-----------+---------+---------+------------+------------+---------------------+---------------------+-------------+------------------+-----------------+ | 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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查询事务参与者信息。
obclient >SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = <val>;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令查询事务参与者信息。
obclient >SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = <val>;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令查询事务参与者信息。
obclient >SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = <val>;
-
获取死锁检测信息
-
sys 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取死锁检测信息。
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;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令获取死锁检测信息。
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;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令获取死锁检测信息。
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
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取 elapsed_time 排序最近 M 分钟内的 top N 的 SQL。
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;返回结果如下：
+-----------+-------------+-----------+-----------+-----------------+---------+-----------+---------------+-------------+--------------+--------------+----------------------------------+----------------------------+------------------------------------------------------------------------------------------------------+ | 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
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令按 QPS 排序获取业务租户最近 M 分钟执行次数最多的 TOP N 的 SQL。
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;返回结果如下：
+-----------+----------------------------------+--------+-------------------+-------------------+-----------------+------------------+--------------------+------------------------------------------------------------------------------------------------------+----------+ | 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 详情
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令按 SQLID 查找最近执行的 N 个 SQL 详情。
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;返回结果如下：
+-----------------+----------+------------+-------------+-----------------------------------+------+-----------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+ | 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
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看某租户在各 server 上最近 M 分钟的 QPS。
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;返回结果如下：
+-------+-----------+-----------------+-------------+----------------------+--------------------+--------------------+-------------------+ | 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
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令查看某租户在各 server 上最近 M 分钟的 QPS。
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;返回结果如下：
+----------------------------------+---------+------+--------------------+-------------------+------------------------------------------------------------------------------------------------------+ | 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 | 系统租户下展示所有租户的数据库元信息。 | |
| 巡检 | SYS 租户：oceanbase.CDB_OB_MAJOR_COMPACTION | 系统租户下展示所有租户的合并全局信息。 | |
| 巡检 |
| 该视图用于展示当前租户下的所有序列。 | |
| 巡检 | SYS 租户：oceanbase.CDB_PART_TABLES | 该视图用于展示所有租户的分区表的分区信息。 | |
| 巡检 | SYS 租户：oceanbase.CDB_TAB_COLS | 该视图用于展示所有租户的表、视图对象的列信息。 | |
| 巡检 | SYS 租户：oceanbase.CDB_TABLES | 该视图用于展示所有租户的 TABLE 信息。 | |
| 巡检 | SYS 租户：oceanbase.CDB_TAB_PARTITIONS | 该视图用于展示所有租户的分区表的一级分区信息。 | |
备份恢复
| 模块 | 视图名 | 视图说明 | 备注 |
|---|---|---|---|
| 物理备份 | SYS 租户：oceanbase.CDB_OB_ARCHIVELOG_SUMMARY | 该视图用于展示历史以及当前所有的日志归档的状态。 | |
| 物理备份 | SYS 租户：oceanbase.CDB_OB_BACKUP_JOBS | 该视图用于展示集群下所有租户的备份 JOB 任务。 | 用户发起一次备份任务，记录一条 JOB 任务记录。 |
| 物理备份 | SYS 租户：oceanbase.CDB_OB_BACKUP_JOB_HISTORY | 该视图用于展示集群下所有租户的备份 JOB 任务的历史记录。 | |
| 物理备份 | SYS 租户：oceanbase.CDB_OB_RESTORE_PROGRESS | 该视图用于展示物理恢复任务当前的执行进度。 | |
| 物理备份 | SYS 租户：oceanbase.CDB_OB_RESTORE_HISTORY | 该视图用于展示物理恢复任务完成时的状态，是恢复任务结束时，对应任务在 oceanbase.CDB_OB_RESTORE_PROGRESS 中对应记录的快照。 | |
| 物理备份 | SYS 租户：oceanbase.CDB_OB_ARCHIVELOG_PIECE_FILES | 该视图用于展示备份集中 Piece 的状态。 | |
| 物理备份 | SYS 租户：oceanbase.CDB_OB_BACKUP_SET_FILES | 该视图用于展示备份集的文件状态。 | |
| 物理备份 |
| 该视图用于展示备份参数。 | |
| 物理备份 |
| 该视图用于展示归档路径配置。 | |
| 物理备份 |
| 该视图用于展示备份参数。 | |
备份恢复相关视图查询示例
获取某租户最新一轮的日志备份任务
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取某租户最新一轮的日志备份任务。
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 = <tenant_id> AND round_id = (SELECT MAX(round_id) FROM oceanbase.CDB_OB_ARCHIVELOG_SUMMARY WHERE tenant_id = <tenant_id>);
获取集群所有的数据备份任务（租户维度）
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取集群所有的数据备份任务。
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;
获取租户恢复任务
说明
仅 sys 租户下可以查看。
-
使用 root 用户登录到集群的 sys 租户。
obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取租户恢复任务。
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 租户
-
使用 root 用户登录到集群的 sys 租户。
obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A -
执行以下命令获取清理策略。
obclient > SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = <policy_name>;
-
-
MySQL 租户
-
使用 root 用户登录到集群的 MySQL 租户。
obclient -h172.30.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A -
执行以下命令获取清理策略。
obclient > SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = <policy_name>;
-
-
Oracle 租户
-
使用 sys 用户登录到集群的 Oracle 租户。
obclient -h172.30.xx.xx -P2883 -usys@oracle001#cluster -p**** -A -
执行以下命令获取清理策略。
obclient > SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = <policy_name>;
-
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
