全局索引随机路由导致 SQL 执行耗时高的原因和处理方法

问题现象

当生产环境切换后，某一条 SQL 存在性能问题。绑定了覆盖索引，执行计划性能显示性能很好，同一条 SQL 反复执行，少部分次数部分执行可以在 20ms 完成，多次数执行在 2-3s。

通过 DBMS_XPLAN 发现执行快和执行慢的 SQL 计划完全一样，但是 SQL AUDIT 记录的 SRV IP 不同。执行快的 SQL 是在主可用区节点 SRV IP 执行，执行慢的 SQL 是在非主可用区的 SRV IP。该租户配置的是单个主可用区，没有打散。没有配置读写分离。

关键信息

全局索引随机路由的问题，排查步骤是确定是否计划走全局索引，primary zone 有没有打散。有没有配置读写分离，查询 proxy 配置参数，使用全局索引表路由需要开启 enable_index_route = true 和 enable_reroute = true 参数都没有开启。

问题原因

客户的 SQL 是 order by limit 分区表的场景，为了消除 top-n 算子走流式计划，加了全局索引引入了随机路由的问题，客户的 primary zone 的集中的数据表和索引表都集中在一个 Zone 里，SQL 被随机路由到非主副本执行慢，目前使用全局索引表路由需要开启 enable_index_route = true 和 enable_reroute = true，之前都没有开启，开启后正常。下面是排查步骤。

1. 查询 SQL AUDIT 记录确定路由节点和计划类型。

   

2. 查询租户 Leader 分布和 primary zone 情况。

   

3. 查询表结构和执行计划发现 T1 表为全局索引 is_global_index=true。

   ===================================================================================================================================================
   
   |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
   
   ---------------------------------------------------------------------------------------------------------------------------------------------------
   
   |0 |LIMIT | |21 |6088 |21 |20114 |0 |0 |
   
   |1 |└─NESTED-LOOP JOIN | |21 |6088 |21 |21170 |0 |0 |
   
   |2 | ├─DISTRIBUTED TABLE RANGE SCAN|t1(idx_sid_id_uid_rls_ua_v_lv,Reverse)|22 |8 |21 |20114 |0 |0 |
   
   |3 | └─DISTRIBUTED TABLE RANGE SCAN|t2 |1 |279 |21 |20114 |0 |0 |
   
   ===================================================================================================================================================
   
   Outputs & filters:
   
   -------------------------------------
   
   0 - output([t1.id], [t1.updated_at], [t1.rls], [t2.uid], [t1.value], [t1.last_value]), filter(nil), rowset=256
   
   limit(:2), offset(nil)
   
   1 - output([t1.id], [t1.updated_at], [t1.rls], [t2.uid], [t1.value], [t1.last_value]), filter(nil), rowset=256
   
   conds(nil), nl_params_([t1.user_id(:3)]), use_batch=false
   
   
   2 - output([t1.id], [t1.user_id], [t1.updated_at], [t1.rls], [t1.value], [t1.last_value]), filter(nil), rowset=256
   
   access([t1.id], [t1.user_id], [t1.updated_at], [t1.rls], [t1.value], [t1.last_value]), partitions(p0)
   
   is_index_back=false, is_global_index=true,
   
   range_key([t1.setting_id], [t1.id], [t1.user_id], [t1.rls], [t1.updated_at], [t1.value], [t1.last_value]), range(1700016,NULL,MAX,MAX,MAX,MAX,MAX
   
   ; 1700016,9223372036854775807,MAX,MAX,MAX,MAX,MAX),
   
   range_cond([t1.setting_id = :0], [t1.id <= :1])
   
   3 - output([t2.uid]), filter(nil), rowset=256
   
   access([t2.uid]), partitions(p[0-15])
   
   is_index_back=false, is_global_index=false,
   
   range_key([t2.id], [t2.uid]), range(MIN ; MAX),
   
   range_cond([:3 = t2.id])
   

4. 查询 proxy 配置参数，使用全局索引表路由需要开启 enable_index_route = true 和 enable_reroute = true 参数都没有开启。

   

问题的风险及影响

primary zone 没有打散情况下全局索引随机路由导致的 SQL 执行慢。

影响租户

影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。

适用版本

OceanBase 数据库 V4.x 版本。

解决方法及规避方式

排查步骤是确定是否计划走全局索引，primary zone 有没有打散。有没有配置读写分离，查询 proxy 配置参数，使用全局索引表路由需要开启 enable_index_route = true 和 enable_reroute = true 参数是否都开启了。