如何确认一个sql使用hint指定了弱读生效了

[AntTech_NHY7X8]

oceanbaes 4.2.5版本
测试环境 3observer(全功能副本)+3obproxy

1. 目前我只想让研发部分sql通过使用hint的方式来进行弱读，并不想修改obproxy参数来全局设置，如何实现呢？

有的文章说如果实现弱读必须，先修改obproxy参数（alter proxyconfig set proxy_route_policy=“follower_first”;）设置为优先度，然后才能使用hint的方式弱读？对这里我有些疑惑，即然obproxy参数都全局开启了，还需要sql指定hint吗？怎么确认一个sql的弱读生效了呢？我通过explain route没有发现弱读的信息呢？
explain route select /*+ read_consistency(weak) / metric_id,app_id from app_metrics where app_id like “d%” limit 5;
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Route Plan |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Trans Current Query:"explain route select /+ read_consistency(weak) */ metric_id,app_id from app_metrics where app_id like “d%” limit 5"

Route Prompts

ROUTE_INFO
[INFO] Will do table partition location lookup to decide which OBServer to route to
PARTITION_ID_CALC_DONE
[WARN] Fail to use partition key value to calculate first part idx

Route Plan

SQL_PARSE:{cmd:“OB_MYSQL_COM_QUERY”, table:“app_metrics”}
ROUTE_INFO:{route_info_type:“USE_PARTITION_LOCATION_LOOKUP”, route_policy:“MERGE_IDC_ORDER”}
LOCATION_CACHE_LOOKUP:{mode:“oceanbase”}
TABLE_ENTRY_LOOKUP_DONE:{table:“app_metrics”, table_id:“500051”, table_type:“USER TABLE”, partition_num:6, entry_from_remote:false}
PARTITION_ID_CALC_START:{}
EXPR_PARSE:{col_val:“there are no part keys”}
RESOLVE_EXPR:{part_range:"(MIN ; MAX)always true"}
CALC_PARTITION_ID:{error:-4002, part_description:“partition by key(VARCHAR<utf8mb4_general_ci>) partitions 6”}
PARTITION_ID_CALC_DONE:{error:-4002, partition_id:-1, level:1}
ROUTE_INFO:{route_info_type:“USE_CACHED_SESSION”, svr_addr:“10.0.2.110:2881”}
ROUTE_POLICY:{route_policy:"", chosen_server:“Invalid”}
CONGESTION_CONTROL:{svr_addr:“10.0.2.110:2881”}

[try_again]

有多种方案，比如：
1、看执行计划里的这个部分
Used Hint:
/*+
READ_CONSISTENCY( WEAK )
*/
2、根据租户priamry_zone，看执行会话的svr_ip是否指向非leader节点ip
3、select svr_ip,user_client_ip,query_sql from gv$ob_sql_audit where query_sql like '%read_consistency%'结果里svr_ip字段可以看到执行observer ip是否为非leader副本

[AntTech_NHY7X8]

通过验证，确认可以通过explain route 和跟踪traceid在ob_sql_audit里进一步确认：
sql： where id in (16135704,16135041,16135698) 这里举例的三个id分别在3个分区，三个分区的leader在分别三个observer上

1. 通过explain route对比
   a. 弱读

b. 强读

2. 通过trace_id查看ob_sql_audit表
   a. 弱读，svr_ip肯定只有1个协调ip和执行sql的ip，他俩是一个Ip
   

   

   b. 强度，svr_ip肯定1个协调ip和3个执行sql的ip,共4个ip,其中有2个重复的
   

   

[AntTech_NHY7X8]

还可以通过gv$ob_sql_audit的CONSISTENCY_LEVEL字段确认：

• CONSISTENCY_LEVEL：事务一致性级别，取值如下：
  • -1：无效
  • 1：指定读存储在 SSTable 中的数据
  • 2：弱一致性读
  • 3：强一致性读

[独善其身]

看执行计划有没有区别啊

[AntTech_NHY7X8]

有区别呀，截图里说的很清楚，强读和弱读选择路由信息不一样，因为这个sql是分布式执行，所以执行的节点也不一样，一个在本地就完成了，一个需要去其他的leader执行获取分区数据

[Amber_www]

这个问题涉及到route和id的平衡，根据我的经验，适当调整app会有帮助。