17认证网概念描述
在OceanBase中，当某张表中数据分布不均衡时，查询传参传入了查询数据量多的参数，会走表的全扫描，而再次传入查询数据量少的参数，不会走索引扫描，而是继续执行表的全扫描。
测试验证
创建用户和测试数据
- 创建测试用户
[root@ocp ~]# obclient -h192.168.10.36 -P2883 -usys@obtest#obdemo -p'AAli88@@1688' -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 33191
Server version: OceanBase 3.2.4.5 (r105000072023110117-085120114430e0e63a31695a653a89c985b21ac2) (Built Nov 1 2023 18:05:06)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [SYS]> create user dbmt identified by dbmt;
Query OK, 0 rows affected (0.057 sec)
obclient [SYS]> grant connect,resource to dbmt;
Query OK, 0 rows affected (0.052 sec)
- 创建测试表并插入测试数据，其中id=1插入10行数据，id=2的插入5000000行数据，模拟数据分布不均衡，在id列上创建索引。
[root@ocp ~]# obclient -h192.168.10.36 -P2883 -udbmt@obtest#obdemo -pdbmt -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 33210
Server version: OceanBase 3.2.4.5 (r105000072023110117-085120114430e0e63a31695a653a89c985b21ac2) (Built Nov 1 2023 18:05:06)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [DBMT]> create table dbmt.test_plan(id number, name varchar(100), idate date);
Query OK, 0 rows affected (0.086 sec)
obclient [DBMT]> insert into dbmt.test_plan select 1,rpad('a',80,'b'),sysdate from dual connect by rownum<11;
Query OK, 10 rows affected (0.031 sec)
Records: 10 Duplicates: 0 Warnings: 0
obclient [DBMT]> insert into dbmt.test_plan select 2,rpad('a',80,'b'),sysdate from dual connect by rownum<5000001;
Query OK, 5000000 rows affected (1 min 53.039 sec)
Records: 5000000 Duplicates: 0 Warnings: 0
obclient [DBMT]> commit;
Query OK, 0 rows affected (9.887 sec)
obclient [DBMT]> create index dbmt.idx_test_plan_id on dbmt.test_plan(id);
Query OK, 0 rows affected (25.003 sec)
obclient [DBMT]> select id,count(*) from dbmt.test_plan group by id;
+------+----------+
| ID | COUNT(*) |
+------+----------+
| 1 | 10 |
| 2 | 5000000 |
+------+----------+
2 rows in set (3.147 sec)
新开直连会话，执行查询SQL，传入查询数据量少的id=1
[root@ocp ~]# obclient -h192.168.10.36 -P2881 -udbmt@obtest -pdbmt -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221689316
Server version: OceanBase 3.2.4.5 (r105000072023110117-085120114430e0e63a31695a653a89c985b21ac2) (Built Nov 1 2023 18:05:06)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [DBMT]> set ob_enable_trace_log=1;
Query OK, 0 rows affected (0.001 sec)
obclient [DBMT]> select count(name) from dbmt.test_plan where id=1;
+-------------+
| COUNT(NAME) |
+-------------+
| 10 |
+-------------+
1 row in set (0.073 sec)
obclient [DBMT]> show trace;
+------------------------------+------------------------------------------------------+-------+
| TITLE | KEYVALUE | TIME |
+------------------------------+------------------------------------------------------+-------+
| NULL | PHY_SCALAR_AGGREGATE | NULL |
| TEST_PLAN(IDX_TEST_PLAN_ID) | PHY_TABLE_SCAN | NULL |
+------------------------------+------------------------------------------------------+-------+
查询走索引扫描数据，在同一个session中再次传入数据量多的id=2，同样是走索引扫描
新开直连其他节点的会话，执行查询SQL，传入查询数据量多的id=2
[root@ocp ~]# obclient -h192.168.10.37 -P2881 -udbmt@obtest -pdbmt -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221971557
Server version: OceanBase 3.2.4.5 (r105000072023110117-085120114430e0e63a31695a653a89c985b21ac2) (Built Nov 1 2023 18:05:06)
Copyright (c) 2000, 2018, OB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [DBMT]> set ob_enable_trace_log=1;
Query OK, 0 rows affected (0.001 sec)
obclient [DBMT]> select count(name) from dbmt.test_plan where id=2;
+-------------+
| COUNT(NAME) |
+-------------+
| 5000000 |
+-------------+
1 row in set (5.866 sec)
obclient [DBMT]> show trace;
+-------------------------------+----------------------------------------------------+---------+
| TITLE | KEYVALUE | TIME |
+-------------------------------+----------------------------------------------------+---------+
| NULL | PHY_DIRECT_RECEIVE | NULL |
| NULL | PHY_DIRECT_TRANSMIT | NULL |
| NULL | PHY_SCALAR_AGGREGATE | NULL |
| TEST_PLAN | PHY_TABLE_SCAN | NULL |
+-------------------------------+----------------------------------------------------+---------+
此时SQL进行硬解析，重新生成了走表的全扫描的执行计划，在同一个session中再次传入数据量少的id=1，同样是走表的全扫描
查看是否有两个plan_id
obclient [DBMT]> select sql_id, plan_id, query_sql, avg_exe_usec, last_active_time
-> from gv$plan_cache_plan_stat
-> where query_sql like 'select count(name) from dbmt.test_plan%'
-> order by last_active_time;
+----------------------------------+---------+---------------------------------------------------+--------------+------------------------------+
| SQL_ID | PLAN_ID | QUERY_SQL | AVG_EXE_USEC | LAST_ACTIVE_TIME |
+----------------------------------+---------+---------------------------------------------------+--------------+------------------------------+
| 95CB7512FA9B4F9B15530EF391E14CB9 | 13149 | select count(name) from dbmt.test_plan where id=1 | 72236 | 07-NOV-23 02.39.00.931768 PM |
| 95CB7512FA9B4F9B15530EF391E14CB9 | 13160 | select count(name) from dbmt.test_plan where id=? | 5753913 | 07-NOV-23 02.44.21.568760 PM |
| 95CB7512FA9B4F9B15530EF391E14CB9 | 44 | select count(name) from dbmt.test_plan where id=2 | 5865775 | 07-NOV-23 02.44.21.912097 PM |
+----------------------------------+---------+---------------------------------------------------+--------------+------------------------------+
3 rows in set (0.059 sec)
查询SQL存在两个PLAN_ID，说明生成了两个执行计划。
解决方式
- 绑定OUTLINE
- 检查同一个SQLID上存在多个执行计划的SQL，清除这个SQL的所有执行计划，使其下次执行时重新生成执行计划
-- 使用 root 登录 sys 租户
[root@ocp ~]# mysql -h192.168.10.36 -P2883 -uroot@sys#obdemo -pAAli88@@1688 -c
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 36136
Server version: 5.6.25 OceanBase 3.2.4.5 (r105000072023110117-085120114430e0e63a31695a653a89c985b21ac2) (Built Nov 1 2023 18:05:06)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> alter system flush plan cache sql_id='95CB7512FA9B4F9B15530EF391E14CB9' tenant='obtest' global;
Query OK, 0 rows affected (0.021 sec)
想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
