关于执行计划缓存的使用疑问咨询

zhenxing · 2022 年6 月 23 日 22:16

【使用环境测试环境
【 OB 】
【使用版本】5.6.25 OceanBase 3.1.3 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977)
【问题描述】
– 相关参考的官方文档链接

咨询一下关于文档描述的问题,有看到社区版对 [执行计划缓存] 中 [手动淘汰] 的方式
ALTER SYSTEM FLUSH PLAN CACHE [ [SQL_identifier] [database_list] tenant_list ] [global];
没看理解对于普通租户如果要清理自身的查询计划缓存的命令该怎么写,以下面操作为例,用普通用户的root权限登录租户,执行下面的操作,要么是Access denied,要么是语法错误,想咨询下正确的语句该怎么编写,具体操作如下所示复现步骤所示

【操作记录】
– 租户用户权限
root@10.186.61.159[oceanbase]> show grants;
±---------------------------------------------------------+
| Grants for root@% |
±---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’ WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON mysql.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON oceanbase.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON test.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON information_schema.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON __public.* TO ‘root’ |
| GRANT ALL PRIVILEGES ON __recyclebin.* TO ‘root’ |
±---------------------------------------------------------+
7 rows in set (0.31 sec)

– 执行的清理查询缓存的操作
root@10.186.61.159[oceanbase]> alter system flush plan cache;
ERROR 1227 (42501): Access denied

root@10.186.61.159[oceanbase]>
root@10.186.61.159[oceanbase]> alter system flush plan cache global;
ERROR 1227 (42501): Access denied

root@10.186.61.159[oceanbase]> alter system flush plan cache databases=‘test’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘databases=‘test’’ at line 1

root@10.186.61.159[oceanbase]> alter system flush plan cache databases=‘test’ tenant=‘zhenxing’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘databases=‘test’ tenant=‘zhenxing’’ at line 1

【疑问咨询】
咨询以下几个问题

对于社区版我们如何手工清理租户的查询缓存
对于社区版文档中的描述是否正确或者是否方便补充一下使用示例

zhenxing · 2022 年6 月 23 日 22:28

补充疑问: 有看到社区版和企业版对会触发查询计划缓存更新的场景描述都只包含了以下2个场景

表结构的变更
触发数据合并时会自动重新收集统计信息并更新查询缓存

实际有了解到存在另一个场景如: SQL进行outline计划绑定变更时，该SQL对应的执行计划会被刷新，更新为按绑定的outline生成的执行计划

想了解下,这个是文档描述存在缺失吗

xuyu · 2022 年6 月 24 日 13:16

请升级下3.1.4，至于文档的确没有比较翔实的用例，后续我们会改进下

xuyu · 2022 年6 月 24 日 13:19

后面的补充疑问可以给出更具体的例子么？

zhenxing · 2022 年6 月 24 日 13:49

您好,3.1.4版本目前是还没对外发布吗,在社区版官网以及github都只看到3.1.3及3.1.3_CE_BP1版本

xuyu · 2022 年6 月 24 日 13:59

喔喔对，不好意思，这个功能目前是带在3.1.4中，正在详细测试中，7月初就可以体验到了，到时也会出个带例子的文章介绍不同粒度下plan cache淘汰

zhenxing · 2022 年6 月 24 日 14:08

root@10.186.61.159[test]> select count(*) from test_order where order_id>0;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.92 sec)

## 未绑定outline时,生成的plan_id为244
root@10.186.61.159[test]> select tenant_id,svr_ip,svr_port,sql_id, plan_id, statement, outline_id, outline_data
    -> from oceanbase.gv$plan_cache_plan_stat
    -> where statement like 'select count(*) from test_order where order_id>?'
    -> and statement not like '%plan_cache_plan_stat%'\G
*************************** 1. row ***************************
   tenant_id: 1004
      svr_ip: 10.186.61.159
    svr_port: 2882
      sql_id: 29F485C4A509417392C7C49DC8B5F379
     plan_id: 244
   statement: select count(*) from test_order where order_id>?
  outline_id: -1
outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test.test_order"@"SEL$1") END_OUTLINE_DATA*/
1 row in set (1.04 sec)

## 创建outline
root@10.186.61.159[test]> create outline otl_test_order_idx_status on "29F485C4A509417392C7C49DC8B5F379" using hint /*+ index(test_order idx_status) */;
Query OK, 0 rows affected (0.54 sec)

## 触发匹配outline的SQL操作
root@10.186.61.159[test]> select count(*) from test_order where order_id>0;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.07 sec)

## 查看plan_id已经更新为了256,也就是我理解这条SQL的plan cache已经做了自动更新
root@10.186.61.159[test]> select tenant_id,svr_ip,svr_port,sql_id, plan_id, statement, outline_id, outline_data from oceanbase.gv$plan_cache_plan_stat  where statement like 'select count(*) from test_order where order_id>?' and statement not like '%plan_cache_plan_stat%'\G
*************************** 1. row ***************************
   tenant_id: 1004
      svr_ip: 10.186.61.159
    svr_port: 2882
      sql_id: 29F485C4A509417392C7C49DC8B5F379
     plan_id: 256
   statement: select count(*) from test_order where order_id>?
  outline_id: 1103909674288106
outline_data: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.test_order"@"SEL$1" "idx_status") END_OUTLINE_DATA*/
1 row in set (0.26 sec)

## 清理outline
root@10.186.61.159[test]> drop outline otl_test_order_idx_status;
Query OK, 0 rows affected (0.17 sec)

## 触发匹配outline的SQL操作
root@10.186.61.159[test]> select count(*) from test_order where order_id>0;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.07 sec)

## 查看plan_id已经更新为了258,这条SQL的plan cache在outline删除时也触发了自动更新
root@10.186.61.159[test]> select tenant_id,svr_ip,svr_port,sql_id, plan_id, statement, outline_id, outline_data from oceanbase.gv$plan_cache_plan_stat  where statement like 'select count(*) from test_order where order_id>?' and statement not like '%plan_cache_plan_stat%'\G
*************************** 1. row ***************************
   tenant_id: 1004
      svr_ip: 10.186.61.159
    svr_port: 2882
      sql_id: 29F485C4A509417392C7C49DC8B5F379
     plan_id: 258
   statement: select count(*) from test_order where order_id>?
  outline_id: -1
outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test.test_order"@"SEL$1") END_OUTLINE_DATA*/
1 row in set (0.05 sec)

xuyu · 2022 年6 月 24 日 17:21

例子是没问题，关注下plan_id和outline_id就可以了，但还是没明白你的文档缺失问题

zhenxing · 2022 年6 月 24 日 23:06

这边我意思是社区版和企业版文档中并没有提及绑定outline会触发执行计划缓存更新

备注: 社区版和企业版的官方文档没提到这个,但OBCP的培训材料中有提及,所以比较疑惑,正常我理解官方文档才是最全的,培训材料一般会做一些精简, 实际这个场景相当于反过来了