往期回顾：

当遇到Oracle优化器选择了错误的执行计划，且统计信息无法解决问题时，OUTLINE大纲技术提供了一种强制固定执行计划的解决方案。本文作为《左右SQL执行计划妙招》系列第十九篇，展示如何使用大纲技术控制SQL的执行路径。

场景设置：索引访问方式的对比

环境构造

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
create index idx_object_id on t(object_id);
set linesize 1000
set pagesize 2000

场景1：无约束时的执行计划

explain plan for select count(*) from t ;
select * from table(dbms_xplan.display());
执行计划：
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 53129 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------
Note
----
   - dynamic sampling used for this statement (level=2)

原因分析：由于object_id列允许为空，Oracle无法确保索引包含所有记录，因此选择全表扫描。

OUTLINE大纲的创建

关键步骤：建立执行计划大纲

-- 为当前执行计划创建大纲
create or replace outline myoutline
    for category mycategory
    on 
 select count(*) from t ; 
-- 验证大纲创建
select category,SQL_text,signature from user_outlines where name='MYOUTLINE' ;

大纲作用：将当前的TABLE ACCESS FULL执行计划固化，无论后续环境如何变化，都强制使用此计划。

环境变化后的执行计划对比

场景2：添加NOT NULL约束后的自然执行计划

alter table t modify object_id not null;
explain plan for select count(*) from t ;
select * from table(dbms_xplan.display());

执行计划

-------------------------------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    49   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 53129 |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
----
   - dynamic sampling used for this statement (level=2)

自然优化： NOT NULL约束让Oracle能够安全使用INDEX FAST FULL SCAN，成本从292降至49。

OUTLINE大纲的强制效果

启用大纲后的执行计划

-- 启用指定类别的大纲
alter session set use_stored_outlines =mycategory; 
explain plan for select count(*) from t ;
select * from table(dbms_xplan.display());

强制执行计划：

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 87235 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------
Note
----
   - outline "MYOUTLINE" used for this statement

大纲技术的核心机制

执行计划固定效果

关键特点

- 计划固化：无论环境如何变化（约束、统计信息等），都强制使用大纲中定义的执行计划。

- 优先级最高：大纲的优先级高于Oracle优化器的自动选择。

- 精确匹配：只对完全匹配的SQL语句生效。

OUTLINE的实际应用场景

适用情况

1. 优化器Bug：已知正确执行计划，但优化器始终选择错误路径

2. 统计信息问题：无法通过统计信息调整解决的执行计划问题

3. 稳定性要求：关键业务SQL需要固定执行计划避免波动

4. 临时修复：生产环境紧急修复执行计划问题

使用示例

-- 场景：关键报表查询需要固定执行计划
CREATE OR REPLACE OUTLINE critical_report_outline
    FOR CATEGORY production_outlines
    ON 
SELECT /*+ USE_NL(a b) INDEX(a idx_date) */ 
       sum(amount) 
FROM sales a, customers b 
WHERE a.customer_id = b.customer_id 
  AND a.sale_date BETWEEN DATE'2024-01-01' AND DATE'2024-12-31';
-- 启用生产环境大纲
ALTER SESSION SET use_stored_outlines = production_outlines;

大纲管理最佳实践

创建和维护

-- 1. 创建大纲
CREATE OR REPLACE OUTLINE outline_name
    FOR CATEGORY category_name
    ON your_sql_statement;
-- 2. 查看大纲信息
SELECT name, category, used, sql_text 
FROM user_outlines 
WHERE category = 'your_category';
-- 3. 启用大纲
ALTER SESSION SET use_stored_outlines = category_name;
-- 4. 禁用大纲
ALTER SESSION SET use_stored_outlines = false;
-- 5. 删除大纲
DROP OUTLINE outline_name;

注意事项

- 谨慎使用：大纲会阻止Oracle的自动优化

- 定期评估：随着数据变化，固定的执行计划可能不再最优

- 文档记录：记录大纲创建的原因和预期移除时间

- 测试验证：在测试环境验证大纲效果

总结

OUTLINE大纲技术提供了一种强制固定执行计划的方法，适用于优化器无法自动选择正确执行计划的特殊情况。本案例展示了即使在环境优化（添加NOT NULL约束）后，大纲仍能强制保持原有的执行计划。

虽然Oracle优化器通常很智能，能够根据统计信息选择正确的执行计划，但在遇到优化器Bug或特殊业务需求时，大纲技术为DBA提供了最后的控制手段。使用时需要谨慎评估，确保固定的执行计划确实是最优选择。

在这里插入图片描述

全文完

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑲（全文完） ——执行计划的固定