计划绑定

计划绑定是通过对SQL语句创建Outline来实现的，Outline本质是一组Hint，在执行SQL语句时，匹配对应的Outline，将Hint写入到SQL中来控制优化器输出绑定的计划

为什么引入计划绑定

在数据库运行过程中，由于统计信息的更新、表结构的变更、索引的添加或删除等多种因素，优化器可能会为相同的SQL查询生成不同的执行计划。这种变化有时会导致查询性能显著下降，特别是在复杂查询和频繁执行的查询中更为明显。

当发现了性能问题，可以经过分析后在SQL上添加Hint来使优化器输出我们想要的计划，但是对于已经上线的业务，想要修改原SQL并不容易，故需要一种在不修改原SQL的基础上也能改变执行计划的手段，为此引入了计划绑定功能。

Outline视图

DBA用户可以通过V_DBA_OUTLINE视图查询，普通用户可以通过V_USER_OUTLINE视图查询，各字段及含义如下。

列名	类型	描述
OUTLINE_ID	OID	对象标识符
OUTLINE_NAME	NAME	对象名字
OUTLINE_OWNER	OID	对象所有者
OUTLINE_ON_SQL	TEXT	绑定SQL的计划来源
OUTLINE_MATCH_SQL	TEXT	绑定的SQL
OUTLINE_DATA	TEXT	用来固定计划的Hint组
OUTLINE_ENABLE	BOOLEAN	是否启用

使用Outline进行计划绑定

创建Outline语法见 Create Outline

Outline_Data可以通过 Explain Outline 命令获取

-- 启用outline
set ENABLE_USE_OUTLINE = TRUE;

-- 创建环境
CREATE TABLE T1(A INT);
INSERT INTO T1 VALUES(1);
ANALYZE T1;

-- 查看计划
EXPLAIN ANALYZE SELECT * FROM T1,T1 TT WHERE T1.A = TT.A;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Nested Loop(Inner Join)  (cost=0.00..4.01 rows=1 width=8) (actual time=0.06..0.10 rows=4 loops=1)
   Join Filter: ("outer".A = "inner".A)
   ->  Seq Scan on T1  (cost=0.00..2.00 rows=1 width=4) (actual time=0.02..0.03 rows=2 loops=1)
   ->  Seq Scan on T1 TT  (cost=0.00..2.00 rows=1 width=4) (actual time=0.01..0.02 rows=2 loops=2)
 Planning Time: 0.49 msec
 Execution Time: 0.23 msec
(6 rows)

-- 此时可以看到因为表行数比较少所以选择了 nestloop 连接
-- 接着向表中插入大量数据后不更新统计信息进行查询
SELECT COUNT(*) FROM T1;
 COUNT
-------
  1024
(1 row)

-- 可以看到因为统计信息未更新，导致优化器选择了一个性能较差得计划
EXPLAIN ANALYZE SELECT * FROM T1,T1 TT WHERE T1.A = TT.A;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Nested Loop(Inner Join)  (cost=0.00..14.03 rows=1 width=8) (actual time=0.06..870.51 rows=1048576 loops=1)
   Join Filter: ("outer".A = "inner".A)
   ->  Seq Scan on T1  (cost=0.00..7.01 rows=1 width=4) (actual time=0.03..0.74 rows=1024 loops=1)
   ->  Seq Scan on T1 TT  (cost=0.00..7.01 rows=1 width=4) (actual time=0.01..0.55 rows=1024 loops=1024)
 Planning Time: 0.20 msec
 Execution Time: 964.08 msec
(6 rows)

-- 通过增加Hint找到了一个更优的计划
EXPLAIN ANALYZE SELECT /*+ USE_HJ(T1,TT) */ * FROM T1,T1 TT WHERE T1.A = TT.A;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Hash Join(Inner Join)  (cost=7.02..14.05 rows=1 width=8) (actual time=1.94..255.37 rows=1048576 loops=1)
   Hash Cond: ("outer".A = "inner".A)
   ->  Seq Scan on T1  (cost=0.00..7.01 rows=1 width=4) (actual time=0.02..1.16 rows=1024 loops=1)
   ->  Hash  (cost=7.01..7.01 rows=1 width=4) (actual time=1.89..1.89 rows=0 loops=1)
         Buckets: 1033  Batches: 1  Memory Usage: 28kB
         ->  Seq Scan on T1 TT  (cost=0.00..7.01 rows=1 width=4) (actual time=0.02..0.89 rows=1024 loops=1)
 Planning Time: 0.25 msec
 Execution Time: 351.65 msec
(8 rows)

-- 通过创建 Outline 将更快的计划绑定到当前SQL上
CREATE OUTLINE OUT1 ON SELECT /*+ USE_HJ(T1,TT) */ * FROM T1,T1 TT WHERE T1.A = TT.A MATCH SELECT * FROM T1,T1 TT WHERE T1.A = TT.A;

-- 查看系统视图可以看到绑定的计划
        select OUTLINE_NAME,OUTLINE_MATCH_SQL,OUTLINE_DATA  from V_DBA_OUTLINE;

 OUTLINE_NAME |             OUTLINE_MATCH_SQL             |                                                     OUTLINE_DATA
--------------+-------------------------------------------+----------------------------------------------------------------------------------------------------------------------
 OUT1         | SELECT * FROM T1,T1 TT WHERE T1.A = TT.A  | /*+ FULL(@SEL$1 SYSDBA.T1@SEL$1) FULL(@SEL$1 SYSDBA.TT@SEL$1) USE_HJ(@SEL$1 (SYSDBA.T1@SEL$1),(SYSDBA.TT@SEL$1)) */
(1 row)

Outline_Data是优化器为了绑定指定得计划生成的一组Hint，Hint详情参见 HINT使用参考

Outline_Data可以通过 Explain Outline 命令获取

EXPLAIN OUTLINE  SELECT * FROM T1,T1 TT WHERE T1.A = TT.A;
                            QUERY PLAN
------------------------------------------------------------------
 Hash Join(Inner Join)  (cost=7.02..14.05 rows=1 width=8)
   Hash Cond: ("outer".A = "inner".A)
   ->  Seq Scan on T1  (cost=0.00..7.01 rows=1 width=4)
   ->  Hash  (cost=7.01..7.01 rows=1 width=4)
         ->  Seq Scan on T1 TT  (cost=0.00..7.01 rows=1 width=4)


 Outline
 -------

     FULL(@SEL$1 SYSDBA.T1@SEL$1)
     FULL(@SEL$1 SYSDBA.TT@SEL$1)
     USE_HJ(@SEL$1 (SYSDBA.T1@SEL$1),(SYSDBA.TT@SEL$1))

 Note: use outline OUT1
(15 rows)

确定计划绑定生效

-- 此时再执行已经进行计划绑定的SQL
-- 从计划上可以看出执行计划已经变成了绑定的计划，并且会提示 Note: use outline OUT1
EXPLAIN ANALYZE SELECT * FROM T1,T1 TT WHERE T1.A = TT.A;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Hash Join(Inner Join)  (cost=7.02..14.05 rows=1 width=8) (actual time=1.03..257.10 rows=1048576 loops=1)
   Hash Cond: ("outer".A = "inner".A)
   ->  Seq Scan on T1  (cost=0.00..7.01 rows=1 width=4) (actual time=0.03..1.40 rows=1024 loops=1)
   ->  Hash  (cost=7.01..7.01 rows=1 width=4) (actual time=0.98..0.98 rows=0 loops=1)
         Buckets: 1033  Batches: 1  Memory Usage: 28kB
         ->  Seq Scan on T1 TT  (cost=0.00..7.01 rows=1 width=4) (actual time=0.01..0.50 rows=1024 loops=1)
 Planning Time: 0.49 msec
 Execution Time: 356.96 msec

 Note: use outline OUT1
(10 rows)

修改Outline

修改Outline语法见 Alter Outline

修改Outline名字

ALTER OUTLINE OUT1 RENAME TO OUT2;

        SELECT OUTLINE_NAME,OUTLINE_MATCH_SQL FROM V_DBA_OUTLINE;
OUTLINE_NAME |             OUTLINE_MATCH_SQL
--------------+-------------------------------------------
OUT2         | SELECT * FROM T1,T1 TT WHERE T1.A = TT.A
(1 row)

重建Outline

随着数据的变化，之前固定的计划可能不是最优的，此时可以对Outline进行重建，重新固定为最优的执行计划。

CREATE TABLE T1(A INT);
INSERT INTO T1 VALUES(1);
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 SELECT * FROM T1;
INSERT INTO T1 VALUES(2);
ANALYZE T1;

SELECT COUNT(*) FROM T1;
 COUNT
-------
  8193
(1 row)

-- 创建Outline
CREATE OUTLINE OUT1 ON SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 2 MATCH SELECT * FROM T1 WHERE A = 2;

-- 此时OUTLINE中固定的是顺序扫描计划
SELECT OUTLINE_NAME,OUTLINE_DATA FROM SYS_OUTLINE;
 OUTLINE_NAME |             OUTLINE_DATA
--------------+--------------------------------------
 OUT1         | /*+ FULL(@SEL$1 SYSDBA.T1@SEL$1) */
(1 row)

-- 创建索引后，应该走索引更快，所以重建后应该走索引扫描
CREATE INDEX IDX1 ON T1(A);

-- 重建OUTLINE
ALTER OUTLINE OUT1 REBUILD;

-- 此时OUTLINE中固定的是索引扫描计划
SELECT OUTLINE_NAME,OUTLINE_DATA FROM SYS_OUTLINE;
SELECT OUTLINE_NAME,OUTLINE_DATA FROM SYS_OUTLINE;
 OUTLINE_NAME |                OUTLINE_DATA
--------------+--------------------------------------------
 OUT1         | /*+ INDEX(@SEL$1 SYSDBA.T1@SEL$1 IDX1) */
(1 row)

开启/禁用OUTLINE

set ENABLE_USE_OUTLINE = TRUE;
CREATE TABLE T1(A INT);
CREATE INDEX IDX1 ON T1(A);
-- 创建Outline
CREATE OUTLINE OUT1 ON SELECT /*+ FULL(T1) */ * FROM T1 WHERE A = 1 MATCH SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 1;

-- 禁用outline
ALTER OUTLINE out1 DISABLE;
EXPLAIN SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using IDX1(Fast Index Scan) on T1  (cost=0.00..1.41 rows=41 width=4)
   Index Key: (A = 1)
(2 rows)

-- 启用outline
ALTER OUTLINE out1 ENABLE;
EXPLAIN SELECT /*+ INDEX(T1) */ * FROM T1 WHERE A = 1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on T1  (cost=0.00..110.40 rows=41 width=4)
   Scan Key: (A = 1)

 Note: use outline OUT1
(4 rows)

删除Outline

创建Outline语法见 Drop Outline

DROP OUTLINE OUT1;

OUTLINE_REBUILD_ADVISOR

为了防止绑定的计划随着数据的变化导致性能严重下降，提供了OUTLINE_REBUILD_ADVISOR视图来建议哪些Outline需要重建。

此视图的工作原理为计算Outline在REBUILD前后的计划代价，如果REBUILD后能得到一个更好的计划，则输出这条OUTLINE，DBA可以定期查询视图后对OUTLINE进行手动REBUILD

--CUR_OUTLINE_COST为当前Outline计划代价，REBUILD_OUTLINE_COST为重建后Outline计划代价
select * from OUTLINE_REBUILD_ADVISOR;

 OUTLINE_ID | OUTLINE_NAME | OUTLINE_OWNER | CUR_OUTLINE_COST | REBUILD_OUTLINE_COST
------------+--------------+---------------+------------------+----------------------
      34829 | OUT1         |             1 |              154 |                    1
(1 row)