常用视图汇总

运维相关视图

下边展示了 OceanBase 数据库运维过程中的常用视图。

集群运维相关视图查询示例

查看集群名称与集群 ID

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询集群名称与集群 ID。

   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 信息

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询 Zone 信息。

   obclient> SELECT `zone`, `status`, `region`, `idc` FROM oceanbase.DBA_OB_ZONES;
   

   返回结果如下所示。

   +-------+--------+----------------+-----+
   | zone  | status | region         | idc |
   +-------+--------+----------------+-----+
   | zone1 | ACTIVE | default_region |     |
   +-------+--------+----------------+-----+
   

根据 IP 和端口查看 OBServer 信息

根据 IP 和端口查看 OBServer 信息，如 OBServer 节点所属的 Zone、是否存在 Root Service 服务、OBServer 节点上次下线的时间、OBServer 节点被 stop 的时间等。

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查看 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 历史事件信息

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取集群字符集。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令获取集群字符集。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取某个租户的降级日志流。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令获取某个租户的降级日志流。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令获取某个租户的降级日志流。

     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 集群的仲裁服务信息

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 OceanBase 集群的仲裁服务信息。

   obclient> SELECT arbitration_service_key, arbitration_service, previous_arbitration_service, type FROM oceanbase.DBA_OB_ARBITRATION_SERVICE;
   

获取租户的合并进度

• sys 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取租户的合并进度。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A
     

  2. 执行以下命令获取租户的合并进度。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令获取租户的合并进度。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询租户列表。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A
     

  2. 执行以下命令查询租户列表。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询租户列表。

     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 规格列表

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询 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 |
   +----------------+-----------------+---------+---------+------------+------------+---------------+---------------------+---------------------+-------------+
   

查询资源池列表

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询资源池列表。

   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 列表

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查看租户数据量。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查看租户数据量。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查看租户数据量。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查看租户表大小统计。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查看租户表大小统计。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查看租户表大小统计。

     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 分布情况

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询租户 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 任务列表，用于租户运维后判断内核任务是否完成

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询租户 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 租户下查询所有租户的合并状态

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql#cluster -p**** -A
     

  2. 普通租户下查询当前租户的合并状态。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询租户列表。

     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 转储历史信息

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询所有租户的 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 迁移情况

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询 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

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计未使用的 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/内存/磁盘）

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计租户资源。

   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/内存/磁盘）

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计集群资源。

   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 |
   +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+
   

统计数据库资源

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计数据库资源。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询集群配置项。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询集群配置项。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询集群配置项。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询租户配置项。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询租户配置项。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询租户配置项。

     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 租户查询所有租户的配置项（包括隐藏配置项）

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 在 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询数据库列表。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询数据库列表。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查看用户列表、全局权限授权情况。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查看用户列表、全局权限授权情况。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查看数据库权限授权情况。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查看数据库权限授权情况。

     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              |
     +--------------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+------------+------------+------------------+----------------+
     

查看用户列表

1. 使用 sys 用户登录到集群的 Oracle 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
   

2. 执行以下命令查看用户列表。

   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 |
   +--------------------+-----------+-----------+
   

查看角色列表

1. 使用 sys 用户登录到集群的 Oracle 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
   

2. 执行以下命令查看角色列表。

   obclient> SELECT ROLE FROM DBA_ROLES;
   

   返回结果如下：

   +----------+
   | ROLE     |
   +----------+
   | CONNECT  |
   | RESOURCE |
   | DBA      |
   | PUBLIC   |
   +----------+
   

查看对象列表

1. 使用 sys 用户登录到集群的 Oracle 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
   

2. 执行以下命令查看对象列表。

   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');
   

查看系统权限授权情况

1. 使用 sys 用户登录到集群的 Oracle 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
   

2. 执行以下命令查看系统权限授权情况。

   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            |
   +-----------------------------+
   

查看角色授权情况

1. 使用 sys 用户登录到集群的 Oracle 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
   

2. 执行以下命令查看角色授权情况。

   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);
   

查看对象权限授权情况

1. 使用 sys 用户登录到集群的 oracle 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
   

2. 执行以下命令查看对象权限授权情况。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查看连接情况。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查看连接情况。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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 所在

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令判断 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 数量

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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 |
     +-----------+---------+------------+
     

统计所有租户表数量

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计租户表数量。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计系统事件。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计系统事件。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计系统事件。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计系统事件。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计系统事件。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计系统事件。

     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

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计磁盘 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 |
   +--------------+--------------+
   

统计系统任务执行时间

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计系统任务执行时间。

   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';
   

统计租户任务执行时间

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计租户任务执行时间。

   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 正在执行的任务执行时间

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计 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 所有任务执行时间

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计 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 快照时间

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计 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;
   

日志流同步延迟

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计日志流同步延迟。

   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;
   

查询索引状态

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询索引状态。

   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;
   

获取集群中无效索引的个数

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计索引错误数。

   obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from oceanbase.CDB_INDEXES where status in ('ERROR','UNUSABLE');
   

统计 memstore 信息

• sys 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令统计 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;
     

查看转储信息

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查看转储信息。

   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 |
   +----------------------------+--------------+-------------------+-------+--------+-------+-------------------------------------------------------------------------------+-------+--------+-------+--------+-------+--------+-------+--------+------------+-----------------+-------------+
   

查看每日合并耗时

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查看每日合并耗时。

   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 |
   +-----------+----------------------+----------------------------+----------------------------+-------------------+
   

查询合并信息（上一个已经完成合并的版本/最近一次合并的版本号/是否存在报错）

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计合并信息。

   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 租户的内存使用情况

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令按租户统计使用的内存。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令按租户统计使用的内存。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令按租户统计使用的内存。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令按模块统计使用的内存。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令按模块统计使用的内存。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令按模块统计使用的内存。

     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）

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令统计租户资源。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询有关 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令统计诊断信息。

     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 |
     +-----------+-----------------------------------------------+---------+--------+--------+----------------+------------------+-----------+-----------+
     

告警

告警相关视图查询示例

采集活跃会话数

• sys 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令采集活跃会话数。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令采集活跃会话数。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令采集活跃会话数。

     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 已分配百分比

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令采集 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令采集 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令采集 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令采集 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令采集 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令采集 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 个数

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取各个状态 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;
   

获取集群中无效索引的个数

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取集群索引无效的个数。

   obclient> SELECT /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from oceanbase.CDB_INDEXES where status in ('ERROR','UNUSABLE');
   

获取 OBServer 已分配内存百分比

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 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 |
   +-----------+--------------+-------------+--------------+--------------+----------------------+----------------------+
   

查询租户合并信息

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询租户合并信息。

   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 租户的内存占用

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 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 使用率

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取租户日志盘使用率。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令获取租户日志盘使用率。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令获取租户日志盘使用率。

     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 |
     +-----------+-----------------+---------------+
     

获取租户任务运行时长

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取租户任务运行时长。

   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 运行中的任务运行时长

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 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';
   

诊断

诊断相关视图查询示例

获取数据库列表

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取数据库列表。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令进行表的统计数据查询。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令进行表的统计数据查询。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令进行表的统计数据查询。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询限流 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询限流 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询限流 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询普通 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询普通 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询普通 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询历史 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询历史 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询历史 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询索引信息。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询索引信息。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询索引信息。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取原生的 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令获取原生的 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令获取原生的 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询执行计划的统计信息。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询执行计划的统计信息。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询执行计划的统计信息。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询执行计划的详情。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询执行计划的详情。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询执行计划的详情。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令采集事务信息。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令采集事务信息。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询事务采集。

     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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令采集并行 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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令采集并行 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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令采集并行 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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令采集会话信息。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令采集会话信息。

     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 |
     +------------+----------+-----------+-------------+----------------+
     

查询资源分配情况

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询资源分配情况。

   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 |
   +-----------------+----------+-----------+--------------+--------------+-----------------+----------------------+----------------------+-----------------------+---------------------+-------------------+----------------------+----------------------+--------------------+
   

查询租户副本数据大小

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查询租户副本数据大小。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令查询事务参与者信息。

     obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = <val>;
     

• MySQL 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令查询事务参与者信息。

     obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM oceanbase.GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = <val>;
     

• Oracle 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令查询事务参与者信息。

     obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ SVR_IP, SESSION_ID, TX_ID FROM GV$OB_TRANSACTION_PARTICIPANTS WHERE TX_ID = <val>;
     

获取死锁检测信息

• sys 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取死锁检测信息。

     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 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令获取死锁检测信息。

     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 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -hxxx.xxx.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令获取死锁检测信息。

     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

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取 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

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令按 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 详情

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令按 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

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查看某租户在各 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

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -hxxx.xxx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令查看某租户在各 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                       |
   +----------------------------------+---------+------+--------------------+-------------------+------------------------------------------------------------------------------------------------------+
   

巡检

备份恢复

备份恢复相关视图查询示例

获取某租户最新一轮的日志备份任务

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取某租户最新一轮的日志备份任务。

   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>);
   

获取集群所有的数据备份任务（租户维度）

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取集群所有的数据备份任务。

   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;
   

获取租户恢复任务

1. 使用 root 用户登录到集群的 sys 租户。

   obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. 执行以下命令获取租户恢复任务。

   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 租户

  1. 使用 root 用户登录到集群的 sys 租户。

     obclient -h172.30.xx.xx -P2883 -uroot@sys#cluster -p**** -A
     

  2. 执行以下命令获取清理策略。

     obclient> SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = <policy_name>; 
     

• MySQL 租户

  1. 使用 root 用户登录到集群的 MySQL 租户。

     obclient -h172.30.xx.xx -P2883 -uroot@mysql001#cluster -p**** -A
     

  2. 执行以下命令获取清理策略。

     obclient> SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = <policy_name>; 
     

• Oracle 租户

  1. 使用 sys 用户登录到集群的 Oracle 租户。

     obclient -h172.30.xx.xx -P2883 -usys@oracle001#cluster -p**** -A
     

  2. 执行以下命令获取清理策略。

     obclient> SELECT policy_name, recovery_window FROM oceanbase.DBA_OB_BACKUP_DELETE_POLICY WHERE policy_name = <policy_name>;