版本：5.7.25-OceanBase_CE-v4.3.5.1
版本：5.7.25-OceanBase_CE-v4.3.4.0
机器配置：8核16G
单个租户，租户分配按最高配置分配
这个语句只会输出一行1，但是执行却奇慢，我们有多个独立的Oceanbase，目前发现两个环境会有这样的情况。
这个简单的语句这么慢会是什么原因造成的？
3 个赞
系统中的字典列是不是缺少脚本执行
1 个赞
什么意思，方便具体说一下麽，不太了解。
1 个赞
这个sql有什么实际意义吗，具体用的时候。这里还是要加很多判断的
我试了一下 这样执行很快： select 1 from Information_schema.columns
1.可以explain select 1 from Information_schema.columns limit 1 看一下具体哪里慢
2.这个系统表数据大的话，也肯定是会慢的
3. 如果要分析这个问题可以参考
explain select 1 from Information_schema.columns limit 0,1
=================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------------------
|0 |LIMIT | |1 |21 |
|1 | TABLE SCAN|__all_virtual_information_columns|1 |20 |
=================================================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil), limit(1), offset(0)
1 - output([1]), filter([0 = sys_privilege_check('table_acc', ?, __all_virtual_information_columns.TABLE_SCHEMA, __all_virtual_information_columns.TABLE_NAME)]),
access([__all_virtual_information_columns.TABLE_SCHEMA], [__all_virtual_information_columns.TABLE_NAME]), partitions(p0)
=================================================================
explain select 1 from Information_schema.columns limit 0
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------------------
|0 |LIMIT | |0 |0 |
|1 | TABLE SCAN|__all_virtual_information_columns|0 |0 |
=================================================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil), limit(0), offset(nil)
1 - output([1]), filter([0 = sys_privilege_check('table_acc', ?, __all_virtual_information_columns.TABLE_SCHEMA, __all_virtual_information_columns.TABLE_NAME)]),
access([__all_virtual_information_columns.TABLE_SCHEMA], [__all_virtual_information_columns.TABLE_NAME]), partitions(p0)
explain select 1 from Information_schema.columns limit 1
=================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------------------
|0 |LIMIT | |1 |21 |
|1 | TABLE SCAN|__all_virtual_information_columns|1 |20 |
=================================================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil), limit(1), offset(nil)
1 - output([1]), filter([0 = sys_privilege_check('table_acc', ?, __all_virtual_information_columns.TABLE_SCHEMA, __all_virtual_information_columns.TABLE_NAME)]),
access([__all_virtual_information_columns.TABLE_SCHEMA], [__all_virtual_information_columns.TABLE_NAME]), partitions(p0)
很明显 加了limit 1会导致他去实际遍历数据，我的是ob3版本，ob421是没有问题的，可以参考一下
2 个赞
我们是C#.NET开发，使用了SqlSugar作为数据库访问框架，里面涉及到CodeFirst的时候SqlSugar使用了这个SQL作为检测连接字符串是否具备访问系统表权限的一种检测手段。这个我们改不了，每次都卡到这个语句的执行上。
1 个赞
特意试了多个版本的OB，没你说的这种情况，都不超过0.1s
1 个赞
Connection id: 3221571617
Current database: oceanbase
Current user: root@172.17.0.1
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.25 OceanBase_CE 4.3.5.1 (r101000042025031818-b6d5706eb3d2c5f501c7fa646ddbf32f3dc87069) (Built Mar 18 2025 18:13:36)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3881
Binary data as: Hexadecimal
Active --------------
mysql> select * from information_schema.columns limit 1\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: oceanbase
TABLE_NAME: __all_core_table
COLUMN_NAME: gmt_create
ORDINAL_POSITION: 1
COLUMN_DEFAULT: CURRENT_TIMESTAMP
IS_NULLABLE: YES
DATA_TYPE: timestamp
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: 6
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: timestamp(6)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,reference
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
1 row in set (7.20 sec)
同4.3.5.1版本，确实很慢
1 个赞
直连的时候 查询慢么？
1、show trace; --使用这个命令 链路跟踪 看看消耗在哪里
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000639971
2、 通过查看gv$ob_sql_audit审计视图确认影响执行耗时的等待事件。
3、 再获取 SQL 执行计划EXPLAIN EXTENDED
4、获取 sql plan monitor 信息，全过程可以参看如下操作。
1.登录sys租户设置 sql_plan_monitor 参数
确认 sql_plan_monitor 已经打开
show parameters like ‘enable_sql_audit’;
如果 enable_sql_audit = False 则将其打开
alter system enable_sql_audit = true;
-
登录业务租户，获取 sql 的执行计划
EXPLAIN EXTENDED sql语句; -
设置临时 trace 获取
SET ob_enable_show_trace=‘ON’; -
再次执行需要采集的 sql 语句
-
获取上一步执行的 sql 的 trace_id 信息
select last_trace_id(); -
临时关闭 plan monitor 数据，防止信息被覆盖
alter system enable_sql_audit = false; -
获取 plan monitor 的 sql ，将 xxxxx 替换为第 6 步返回的 trace_id ，以获取每个算子的吐行信息
select plan_line_id, plan_operation, sum(output_rows), sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) from oceanbase.gv$sql_plan_monitor where trace_id = ‘xxxxx’ group by plan_line_id, plan_operation order by plan_line_id;
8 恢复 sql_audit 参数
alter system enable_sql_audit = true;
4 个赞
可以具体查看一下 多少数据 select count(1) from Information_schema.columns;
1 个赞
你也可以查看 这个表多少数据
普通MySQL租户慢
mysql> select count(*) from information_schema.columns;
+----------+
| count(*) |
+----------+
| 13161 |
+----------+
1 row in set (9.33 sec)
mysql> select count(*) from oceanbase.__all_virtual_information_columns;
+----------+
| count(*) |
+----------+
| 13161 |
+----------+
1 row in set (7.36 sec)
集群sys租户不慢
mysql> select count(*) from information_schema.columns;
+----------+
| count(*) |
+----------+
| 21239 |
+----------+
1 row in set (0.05 sec)
mysql> select count(*) from oceanbase.__all_virtual_information_columns;
+----------+
| count(*) |
+----------+
| 21239 |
+----------+
1 row in set (0.08 sec)
mysql> select 1 from information_schema.columns limit 0,1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.03 sec)
2 个赞
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> select 1 from Information_schema.columns limit 0,1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (38.811 sec)
obclient [oceanbase]>
obclient [oceanbase]> select count(1) from Information_schema.columns;
+----------+
| count(1) |
+----------+
| 49376 |
+----------+
1 row in set (39.126 sec)
obclient [oceanbase]>
1 个赞
你通过sys租户这样查询慢么？select count(1) from Information_schema.columns;
1 个赞
看普通租户执行内存消耗很大，sys租户比较正常
-- 普通租户sql_audit
mysql> select /*+query_timeout(3600000000) parallel(8)*/ usec_to_time(request_time) as request_time, DATE_ADD( usec_to_time(request_time), INTERVAL elapsed_time / 1000000 second ) as execute_end_time, concat( 'select DBMS_XPLAN.DISPLAY_CURSOR(', char(39), plan_id, char(39), ',', char(39), 'ADVANCED', char(39), ',', char(39), svr_ip, char(39), ',', char(39), svr_port, char(39), ',', char(39), tenant_id, char(39), ')' ) sql_explain, svr_ip, query_sql, PARAMS_VALUE, plan_id, sql_id, ret_code, elapsed_time / 1000000 as "收到请求到结束总时间(S elapsed_time)", execute_time / 1000000 as "SQL执行时间(S execute_time)", get_plan_time / 1000000 as "get_plan_time(S)", round(request_memory_used / 1024 / 1024, 2) "request_memory_used(MB)", request_memory_used, TX_ID, plan_hash, trace_id, sid, tenant_id, tenant_name, DB_ID, DB_NAME, retry_cnt, RETURN_ROWS, AFFECTED_ROWS, PARTITION_CNT, rpc_count, case when PLAN_TYPE = 0 then '无执行计划' when PLAN_TYPE = 1 then '本地执行计划' when PLAN_TYPE = 2 then '远程执行计划' when PLAN_TYPE = 3 then '分布式执行计划' else NULL end as PLAN_TYPE, CASE WHEN REQUEST_TYPE = 0 THEN '非法请求' WHEN REQUEST_TYPE = 1 THEN '内部请求' WHEN REQUEST_TYPE = 2 THEN '本地请求' WHEN REQUEST_TYPE = 3 THEN '远程请求' WHEN REQUEST_TYPE = 4 THEN '分布式请求' WHEN REQUEST_TYPE = 5 THEN 'SQL prepare 请求' WHEN REQUEST_TYPE = 6 THEN 'SQL execute stmt 请求' WHEN REQUEST_TYPE = 7 THEN 'SQL 得到 Fetch 请求' WHEN REQUEST_TYPE = 8 THEN 'SQL 得到 send_piece 请求' WHEN REQUEST_TYPE = 9 THEN 'SQL 得到 get_piece 请求' WHEN REQUEST_TYPE = 10 THEN 'SQL 得到 send_long_data 请求' WHEN REQUEST_TYPE = 11 THEN '当前 SQL 是 PL 调用的内部 SQL' END AS REQUEST_TYPE, TABLE_SCAN, IS_HIT_PLAN, DISK_READS, CASE WHEN IS_INNER_SQL = 1 THEN '内部 SQL' WHEN IS_INNER_SQL = 0 THEN '非内部 SQL' ELSE NULL END AS IS_INNER_SQL, CASE WHEN IS_EXECUTOR_RPC = 1 THEN 'RPC调用' WHEN IS_EXECUTOR_RPC = 0 THEN '非RPC 调用' ELSE NULL END AS IS_EXECUTOR_RPC, EVENT, IS_BATCHED_MULTI_STMT from GV$OB_SQL_AUDIT t where 1 = 1 and query_sql like 'select * from information_schema.columns%' ORDER BY REQUEST_TIME DESC\G
*************************** 1. row ***************************
request_time: 2025-04-17 02:15:58.579254
execute_end_time: 2025-04-17 02:16:06.764254
sql_explain: select DBMS_XPLAN.DISPLAY_CURSOR('1858','ADVANCED','172.17.0.3','2882','1002')
svr_ip: 172.17.0.3
query_sql: select * from information_schema.columns limit 1
PARAMS_VALUE:
plan_id: 1858
sql_id: 64EC441CE236DFF9C1E7DA1558D1B411
ret_code: 0
收到请求到结束总时间(S elapsed_time): 8.1850
SQL执行时间(S execute_time): 8.1823
get_plan_time(S): 0.0012
request_memory_used(MB): 30293.63
request_memory_used: 31765170624
TX_ID: 0
plan_hash: 3435247239800877365
trace_id: YB42AC110003-000632E08EBCF791-0-0
sid: 3221565838
tenant_id: 1002
tenant_name: test
DB_ID: 201001
DB_NAME: oceanbase
retry_cnt: 1
RETURN_ROWS: 1
AFFECTED_ROWS: 0
PARTITION_CNT: 1
rpc_count: 0
PLAN_TYPE: 本地执行计划
REQUEST_TYPE: 本地请求
TABLE_SCAN: 1
IS_HIT_PLAN: 1
DISK_READS: 0
IS_INNER_SQL: 非内部 SQL
IS_EXECUTOR_RPC: 非RPC 调用
EVENT:
IS_BATCHED_MULTI_STMT: 0
1 row in set (0.11 sec)
-- sys租户sql_audit
*************************** 1. row ***************************
request_time: 2025-04-17 10:24:15.071556
execute_end_time: 2025-04-17 10:24:15.179156
sql_explain: select DBMS_XPLAN.DISPLAY_CURSOR('107510','ADVANCED','172.17.0.3','2882','1')
svr_ip: 172.17.0.3
query_sql: select * from information_schema.columns limit 1
PARAMS_VALUE:
plan_id: 107510
sql_id: 64EC441CE236DFF9C1E7DA1558D1B411
ret_code: 0
收到请求到结束总时间(S elapsed_time): 0.1076
SQL执行时间(S execute_time): 0.1071
get_plan_time(S): 0.0003
request_memory_used(MB): 50.73
request_memory_used: 53189184
TX_ID: 0
plan_hash: 1677510974882263170
trace_id: YB42AC110003-000632E08EBCF9F5-0-0
sid: 3221529132
tenant_id: 1
tenant_name: sys
DB_ID: 201001
DB_NAME: oceanbase
retry_cnt: 0
RETURN_ROWS: 1
AFFECTED_ROWS: 0
PARTITION_CNT: 1
rpc_count: 0
PLAN_TYPE: 本地执行计划
REQUEST_TYPE: 本地请求
TABLE_SCAN: 1
IS_HIT_PLAN: 1
DISK_READS: 0
IS_INNER_SQL: 非内部 SQL
IS_EXECUTOR_RPC: 非RPC 调用
EVENT:
IS_BATCHED_MULTI_STMT: 0
@远航
按照下面的步骤 拿一下诊断信息
obclient [test]> select count() from test2;
±---------+
| count() |
±---------+
| 0 |
±---------+
1 row in set (0.003 sec)
obclient [test]> select last_trace_id();
±----------------------------------+
| last_trace_id() |
±----------------------------------+
| YB420BA1CC68-000615A0A8EA6511-0-0 |
±----------------------------------+
1 row in set (0.002 sec)
obclient [test]> select * from oceanbase.gv$ob_sql_audit where trace_id=‘YB420BA1CC68-000615A0A8EA6511-0-0’;
[root@x.x.x.x ~]$ grep “YB420BA1CC68-000615A0A8EA6511-0-0” rootservice.log
[root@x.x.x.x ~]$ grep “YB420BA1CC68-000615A0A8EA6511-0-0” observer.log
通过诊断信息的plan_id查一下 执行计划
select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP = ‘xx.xx.xx.xx’ and SVR_PORT = ‘2882’ and plan_id = 1131097 and tenant_id = 1002;
show create table __all_virtual_information_columns\G;
你发起过转储或者合并没 ？ 我这第一次慢 ，其他的也不慢。 普通租户
我合并试试，确实是新环境，用的官方的docker hub中提供的容器快速启动的一个4.3.5.1版本单机
