如何有效使用 outline 功能？

作者：胡呈清，爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：[简书 | 轻松的鱼]，欢迎讨论。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1000 字，预计阅读需要 3 分钟。

为了防止某些 SQL 的执行计划发生变化，我们通常会创建 outline 来绑定执行计划。但是为什么实际过程中，我们创建 outline 并验证时总会遇到不生效的情况？

阅读本文你将了解：

1. 如何获取 SQL ID
2. 如何创建 outline
3. 如何正确验证 outline 效果

下面以一个实例来演示 outline 的正确使用方法。

实例演示

我们得知业务中 SELECT count(*) FROM ACT_HI_COMMENT; 这个 SQL 选错索引了，需要指定走 IDX_ACT_HI_COMMENT_02 这个索引。

获取 SQL ID

可以从 gv$sql_audit 中获取 SQL ID。

obclient [SYS]> select query_sql,sql_id,svr_ip,plan_id,tenant_id from gv$sql_audit where query_sql like 'SELECT%count%ACT_HI_COMMENT%';
+-------------------------------------------+----------------------------------+------------+---------+-----------+
| QUERY_SQL                                 | SQL_ID                           | SVR_IP     | PLAN_ID | TENANT_ID |
+-------------------------------------------+----------------------------------+------------+---------+-----------+

| SELECT count(*) FROM ACT_HI_COMMENT;      | AC1ED40EC4D5E1A9D75944216745063A | 26.0.8.170 |   99968 |      1001 |
| SELECT count(*) FROM ACT_HI_COMMENT       | 46815AF386F959D17293BCF931FEEAF1 | 26.0.8.170 |   99798 |      1001 |
+-------------------------------------------+----------------------------------+------------+---------+-----------+
2 rows in set (8.695 sec)

此时我们发现一个问题：结果中有两条 SQL（对应两个 SQL ID），区别是一个有分号，一个没分号。哪个是业务下发的 SQL？

答案是没有分号的那个。

理由

1. 应用程序发起的 SQL 请求，以及 obclient 客户端执行的 SQL ，在 gv$sql_audit 中都是没有分号的。
2. 在 ODC 中执行的 SQL 无论加不加分号，在 gv$sql_audit 中都有分号。

创建 outline

因此我们用第二个SQL ID 来绑定执行计划，注意这里要在对应的 schema 下创建，常见的误区是在 sys 用户下创建，这是不会生效的：

-- 创建outline
conn JTZJGL;
create outline test_outline on '46815AF386F959D17293BCF931FEEAF1' using hint /*+ index(ACT_HI_COMMENT IDX_ACT_HI_COMMENT_02) */;

-- 查询outline
obclient [JTZJGL]> select OUTLINE_ID,DATABASE_NAME,OUTLINE_NAME,OUTLINE_SQL,SQL_ID,OUTLINE_CONTENT from gv$outline;
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
| OUTLINE_ID       | DATABASE_NAME | OUTLINE_NAME | OUTLINE_SQL | SQL_ID                           | OUTLINE_CONTENT                                   |
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
| 1100611139404781 | JTZJGL        | TEST_OUTLINE |             | 46815AF386F959D17293BCF931FEEAF1 | /*+index(ACT_HI_COMMENT IDX_ACT_HI_COMMENT_02) */ |
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
1 row in set (0.005 sec)

验证效果

误区

认为可以通过 explain 观察执行计划的方式来验证 outline 的效果是一个误区。explain 无法验证 outline 效果（执行计划不会改变）。

obclient [JTZJGL]> explain SELECT count(*) FROM ACT_HI_COMMENT\G
*************************** 1\. row ***************************
Query Plan: ==========================================================================
|ID|OPERATOR       |NAME                                 |EST. ROWS|COST |
--------------------------------------------------------------------------
|0 |SCALAR GROUP BY|                                     |1        |89615|
|1 | TABLE SCAN    |ACT_HI_COMMENT(IDX_ACT_HI_COMMENT_01)|210872   |81567|
==========================================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter(nil), 
      access([ACT_HI_COMMENT.TASK_ID_]), partitions(p0)

1 row in set (0.004 sec);

正确的方式

应该执行原始 SQL 来验证。 前面我们讲了 ODC 会自动给 SQL 加分号，所以只能在 obclient 客户端中执行 SQL，然后在 gv$plan_cache_plan_stat 视图中查询这个 SQL 对应的 OUTLINE_ID。

OUTLINE_ID 只要不为 -1 就说明生效了，OUTLINE_DATA 中也可以看出使用了我们指定的 IDX_ACT_HI_COMMENT_02 索引：

--执行原始SQL
obclient [JTZJGL]> SELECT count(*) FROM ACT_HI_COMMENT;
+----------+
| COUNT(*) |
+----------+
|   210872 |
+----------+
1 row in set (0.110 sec)

--然后在gv$plan_cache_plan_stat视图中查询这个SQL对应的 OUTLINE_ID，只要不为 -1 就说明生效了
obclient [SYS]> select SQL_ID,PLAN_ID,QUERY_SQL,OUTLINE_ID,OUTLINE_DATA from gv$plan_cache_plan_stat where sql_id='46815AF386F959D17293BCF931FEEAF1';
+----------------------------------+---------+-------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------+
| SQL_ID                           | PLAN_ID | QUERY_SQL                           | OUTLINE_ID       | OUTLINE_DATA                                                                                                      |
+----------------------------------+---------+-------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------+
| 46815AF386F959D17293BCF931FEEAF1 |   99996 | SELECT count(*) FROM ACT_HI_COMMENT | 1100611139404781 | /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "JTZJGL.ACT_HI_COMMENT"@"SEL$1" "IDX_ACT_HI_COMMENT_02") END_OUTLINE_DATA*/ |
+----------------------------------+---------+-------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.057 sec)

总结

1. 永远推荐 使用 SQL ID 来创建 outline，因为 SQL 文本即使多一个空格都会导致 outline 不生效。
2. 通过 gv$sql_audit 获取SQL_ID，注意区分业务 SQL 在 gv$sql_audit 中不会带有分号。
3. 创建 outline 时需要登录业务租户，并在对应的 schema 下创建，不能在 sys 用户下创建。

验证 outline 注意事项：

• explain 看执行计划是不会改变的，不能用来验证 outline 是否生效。
• 执行原始 SQL 时：
• 执行原始 SQL 后：