OceanBase 数据库名不对导致 Outline 不生效

问题描述

用户创建了 Outline，业务 SQL 却没有命中创建的 Outline（本文示例使用 MySQL 模式，Oracle 模式也存在一样的问题）。

1. 创建测试表。

   obclient [db_mysql]> create table t13(id int,r1 int,key idx_r1(r1));
   Query OK, 0 rows affected (1.107 sec)
   

2. 插入测试数据。

   obclient [db_mysql]> insert into t13 values (1,200),(2,10),(3,100);
   Query OK, 3 rows affected (0.081 sec)
   

3. 查询测试表内容。

   obclient [db_mysql]> select * from t13;
   

   输出结果如下：

   +------+------+
   | id   | r1   |
   +------+------+
   |    1 |  200 |
   |    2 |   10 |
   |    3 |  100 |
   +------+------+
   3 rows in set (0.106 sec)
   

4. 使用 SQL_TEXT 创建 OUTLINE。

   obclient [db_mysql]> create outline using_index_t13_r1 on select /*+ index(t13 idx_r1)*/ * from t13;
   Query OK, 0 rows affected (0.245 sec)
   

5. 执行查询语句，结果非预期。

   obclient [(none)]> select * from db_mysql.t13;
   

   输出结果如下：

   +------+------+
   | id   | r1   |
   +------+------+
   |    1 |  200 |
   |    2 |   10 |
   |    3 |  100 |
   +------+------+
   3 rows in set (0.052 sec)
   

6. sys 租户查询 gv$sql_audit 和表 gv$plan_cache_plan_stat 发现执行计划没有命中 Outline。

   obclient [oceanbase]> select * from __all_virtual_outline where database_id in (select database_id from__all_virtual_database where database_name='db_mysql');
   

   输出结果如下：

     +-----------+------------------+----------------------------+----------------------------+------------------+------------------+--------------------+----------------------+-------------------------------------------------------------------------------------------+-------------------------------------------+-------+------+---------------------------------------------------------+------------+---------+--------+----------------+----------------+--------+----------+
     | tenant_id | outline_id       | gmt_create                 | gmt_modified               | database_id      | schema_version   | name               | signature            | outline_content                                                                           | sql_text                                  | owner | used | version                                                 | compatible | enabled | format | outline_params | outline_target | sql_id | owner_id |
     +-----------+------------------+----------------------------+----------------------------+------------------+------------------+--------------------+----------------------+-------------------------------------------------------------------------------------------+-------------------------------------------+-------+------+---------------------------------------------------------+------------+---------+--------+----------------+----------------+--------+----------+
     |      1001 | 1100611139404777 | 2023-06-27 14:12:24.056446 | 2023-06-27 xx:xx:xx.xxxxx  | 1100611139404827 | 1687846344010456 | using_index_t13_r1 | select*from t13      | /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "db_mysql.t13"@"SEL$1" "idx_r1") END_OUTLINE_DATA*/ | select /*+ index(t13 idx_r1)*/* from t13  | root  |    0 | 20220429172811-141f0018b07e9f8d269bb5f6dbd020cd419eb3fc |          1 |       1 |      0 |                |                |        |     NULL |
     +-----------+------------------+----------------------------+----------------------------+------------------+------------------+--------------------+----------------------+-------------------------------------------------------------------------------------------+-------------------------------------------+-------+------+---------------------------------------------------------+------------+---------+--------+----------------+----------------+--------+----------+
     1 row in set (0.129 sec)
   

7. 查询结果中 outline_id 为 -1，表示没有命中。

   a. 通过视图确定 SQL_ID 值。

   obclient [oceanbase]> select plan_id,db_name,query_sql,sql_id from gv$sql_audit where query_sql like 'select * from db_mysql.t13' and tenant_id = 1001 order by request_time desc;
   

   输出结果如下：

   +---------+----------+-----------------------------+----------------------------------+
   | plan_id | db_name  | query_sql                   | sql_id                           |
   +---------+----------+-----------------------------+----------------------------------+
   |     227 | test     | select * from db_mysql.t13 | 04D88FBCA8329C9CC0059FB5AC4BB6BA |
   +---------+----------+-----------------------------+----------------------------------+
   1 rows in set (0.041 sec)
   

   b. 通过 OUTLINE_ID 值确定 OUTLINE 命中情况。

   obclient [oceanbase]> select outline_id,outline_data from gv$plan_cache_plan_stat where plan_id = 227 and sql_id = '04D88FBCA8329C9CC0059FB5AC4BB6BA';
   

   输出结果如下：

   +------------------+-------------------------------------------------------------------------------------------+
   | outline_id       | outline_data                                                                              |
   +------------------+-------------------------------------------------------------------------------------------+
   |               -1 | /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "db_mysql.t13"@"SEL$1") END_OUTLINE_DATA*/           |
   +------------------+-------------------------------------------------------------------------------------------+
   1 rows in set (0.057 sec)
   

问题原因

OceanBase 数据库创建 Outline 时需要在对应的数据库下进行创建，如果使用 Outline 时没有在对应的数据库下将会出现 Outline 不生效的现象。

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

解决方法

使用前，必须先切换到所属数据库方才可行。

1. 切换至所属数据库。

   obclient [(none)]> use db_mysql;
   Database changed
   

2. 查询测试表。

   obclient [db_mysql]> select  * from t13;
   

   输出结果如下：

   +------+------+
   | id   | r1   |
   +------+------+
   |    2 |   10 |
   |    3 |  100 |
   |    1 |  200 |
   +------+------+
   3 rows in set (0.159 sec)
   

3. 验证是否命中 Outline。

   1. 通过视图确定 SQL_ID 值。

   obclient [oceanbase]> select plan_id,db_name,query_sql,sql_id from gv$sql_audit where query_sql like 'select * from t13' and tenant_id = 1001 order by request_time desc;
   

   输出结果如下：

   +---------+----------+-----------------------------+----------------------------------+
   | plan_id | db_name  | query_sql                   | sql_id                           |
   +---------+----------+-----------------------------+----------------------------------+
   |      25 | db_mysql | select * from t13           | 464DB8CA0D9C6368F1D1253C0D1CA65F |
   +---------+----------+-----------------------------+----------------------------------+
   1 rows in set (0.041 sec)
   

4. 通过 OUTLINE_ID 值确定 OUTLINE 命中情况。

   obclient [oceanbase]> select outline_id,outline_data from gv$plan_cache_plan_stat where plan_id in (25,227) and sql_id = '464DB8CA0D9C6368F1D1253C0D1CA65F';
   

   输出结果如下：

   +------------------+-------------------------------------------------------------------------------------------+
   | outline_id       | outline_data                                                                              |
   +------------------+-------------------------------------------------------------------------------------------+
   | 1100611139404777 | /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "db_mysql.t13"@"SEL$1" "idx_r1") END_OUTLINE_DATA*/ |
   +------------------+-------------------------------------------------------------------------------------------+
   1 rows in set (0.057 sec)