模拟OceanBase数据库SQL执行计划突变

概念描述

​ 在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)