SPM 演示：
SPM有两个相关的系统变量
● optimizer_use_sql_plan_baselines
表示当SQL生成新的计划时是否使用基线计划进行演进。
● optimizer_capture_sql_plan_baselines
表示当新的计划优于基线计划时，是否捕获新的计划作为基线。
注意事项
1. 在测试环境中进行SQL调优的过程中不要开启SPM。会导致新建的索引无法立即生效，看不出优化效果。
2. 由于分布式计划的复杂性，不同server上产生的基线计划在其他server上可能无法直接使用或者在其它server上并不是最优的计划。因此在分布式计划数量占比多的环境中，SPM在部分SQL上可能无法达到预期的效果。
OB_SYS_4.2.5 [root@oceanbase:2883] > show variables like '%baseline%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| optimizer_capture_sql_plan_baselines | OFF |
| optimizer_use_sql_plan_baselines | OFF |
+--------------------------------------+-------+
2 rows in set (0.005 sec)OB_SYS_4.2.5 [root@oceanbase:2883] > select ob_version();
+--------------+
| ob_version() |
+--------------+
| 4.2.5.3 |
+--------------+
1 row in set (0.001 sec)
OB_ORACLE_4.2.5 [SYS@SYS] > set global optimizer_use_sql_plan_baselines = true;
Query OK, 0 rows affected (0.061 sec)OB_ORACLE_4.2.5 [SYS@SYS] > set global optimizer_capture_sql_plan_baselines = true;
Query OK, 0 rows affected (0.055 sec)开启方式，注意是否需要 global 开启：
set optimizer_use_sql_plan_baselines = true;
set optimizer_capture_sql_plan_baselines = true;创建表：
CREATE TABLE t1(c1_pk INT PRIMARY KEY, c2 INT, c3 INT);
create index idx_c2 on t1(c2);使用 ODC 模拟数据:
其中 c2_skew 数据分布为: 10w 行 100， 1 行 111; 比例为 0.00001
insert into t1(c1_pk,c2,c3) select level as c1_pk ,100 as c2,dbms_random.value(100000,100) as c3 from dual connect by level <=100000;
insert into t1(c1_pk,c2,c3) values (100001,111,9660.4276251889046576029283550448552666);OB_ORACLE_4.2 [SKY@SKY] > select distinct(c2),count(*) from t1 group by c2;
+------+----------+
| C2 | COUNT(*) |
+------+----------+
| 100 | 100000 |
| 111 | 1 |
+------+----------+执行小账号 SQL，生成不良的执行计划，预期走索引
select sum(c1_pk + c2 + c3) from t1 where c2 = 111;OB_ORACLE_4.2 [SKY@SKY] > explain extended select sum(c1_pk + c2 + c3) from t1 where c2 = 111;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |7 | |
| |1 |+-TABLE RANGE SCAN|T1(IDX_C2)|1 |7 | |
| ======================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_SUM(T1.C1_PK(0x7f1f6e425270) + T1.C2(0x7f1f6e422f30)(0x7f1f6e424000) + T1.C3(0x7f1f6e4255a0)(0x7f1f6e424a90))(0x7f1f6e423560)]), filter(nil), rowset=16 |
| group(nil), agg_func([T_FUN_SUM(T1.C1_PK(0x7f1f6e425270) + T1.C2(0x7f1f6e422f30)(0x7f1f6e424000) + T1.C3(0x7f1f6e4255a0)(0x7f1f6e424a90))(0x7f1f6e423560)]) |
| 1 - output([T1.C1_PK(0x7f1f6e425270)], [T1.C2(0x7f1f6e422f30)], [T1.C3(0x7f1f6e4255a0)]), filter(nil), rowset=16 |
| access([T1.C1_PK(0x7f1f6e425270)], [T1.C2(0x7f1f6e422f30)], [T1.C3(0x7f1f6e4255a0)]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([T1.C2(0x7f1f6e422f30)], [T1.C1_PK(0x7f1f6e425270)]), range(111,MIN ; 111,MAX), |
| range_cond([T1.C2(0x7f1f6e422f30) = 111(0x7f1f6e4db5b0)(0x7f1f6e4dace0)]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| INDEX(@"SEL$1" "SKY"."T1"@"SEL$1" "IDX_C2") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.1.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T1: |
| table_rows:100001 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:1 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[IDX_C2, T1] |
| unstable_index_name:[T1] |
| stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| LOCAL |
| Parameters: |
| :0 => 111 |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
54 rows in set (0.006 sec)BASELINE 信息：
select * from DBA_SQL_PLAN_BASELINES\G*************************** 2. row ***************************
SIGNATURE: 4981698109179497188
SQL_HANDLE: 41042C167F4ABBF7556D815BC622DC6A
SQL_TEXT: select sum(c1_pk + c2 + c3) from t1 where c2 = 111
PLAN_NAME: 4981698109179497188
CREATOR: SKY
ORIGIN: AUTO-CAPTURE
PARSING_SCHEMA_NAME: NULL
DESCRIPTION: NULL
VERSION: 4.2.5.3
CREATED: 08-APR-25 03.58.54.068941 PM
LAST_MODIFIED: 08-APR-25 03.58.54.068941 PM
LAST_EXECUTED: 1744099134068156
LAST_VERIFIED: 1744099134068156
ENABLED: YES
ACCEPTED: YES
FIXED: NO
REPRODUCED: YES
AUTOPURGE: YES
ADAPTIVE: NO
OPTIMIZER_COST: -1
MODULE: NULL
ACTION: NULL
EXECUTIONS: 1
ELAPSED_TIME: 3173
CPU_TIME: 99
BUFFER_GETS: NULL
DISK_READS: NULL
DIRECT_WRITES: NULL
ROWS_PROCESSED: NULL
FETCHES: NULL
END_OF_FETCH_COUNT: NULL清空 PLAN CACHE
alter system flush plan cache global;OB_ORACLE_4.2 [SKY@SKY] > select sum(c1_pk + c2 + c3) from t1 where c2 = 100;
+------------------+
| SUM(C1_PK+C2+C3) |
+------------------+
| 10003673540 |
+------------------+
1 row in set (0.109 sec)
OB_ORACLE_4.2 [SKY@SKY] > explain select sum(c1_pk + c2 + c3) from t1 where c2 = 100;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |4136 | |
| |1 |+-TABLE FULL SCAN|T1 |99 |4134 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_SUM(T1.C1_PK + T1.C2 + T1.C3)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_SUM(T1.C1_PK + T1.C2 + T1.C3)]) |
| 1 - output([T1.C1_PK], [T1.C2], [T1.C3]), filter([T1.C2 = 100]), rowset=256 |
| access([T1.C1_PK], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T1.C1_PK]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
14 rows in set (0.004 sec)select sql_id, evolution, evo_executions, outline_data from GV$OB_PLAN_CACHE_PLAN_STAT where statement = 'select sum(c1_pk + c2 + c3) from t1 where c2 = ?';能看到较之前，多生成了一条全表扫描的 PLAN，且加入演进了：
evolution =1 表示正在演进，evo_executions 表示已经演进的次数，目前=100 和=111 各执行了一次。
OB_SYS_4.2.5 [root@oceanbase:2883] > select sql_id, evolution, evo_executions, outline_data from GV$OB_PLAN_CACHE_PLAN_STAT where statement = 'select sum(c1_pk + c2 + c3) from t1 where c2 = ?';
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_id | evolution | evo_executions | outline_data |
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| 9617D7AA84029C15DFA0F0FCCF3DBD8C | 1 | 0 | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SKY"."T1"@"SEL$1") PARALLEL( AUTO ) OPTIMIZER_FEATURES_ENABLE('4.2.1.0') END_OUTLINE_DATA*/ |
| 9617D7AA84029C15DFA0F0FCCF3DBD8C | 1 | 1 | /*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "SKY"."T1"@"SEL$1" "IDX_C2") PARALLEL( AUTO ) OPTIMIZER_FEATURES_ENABLE('4.2.1.0') END_OUTLINE_DATA*/ |
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.019 sec)继续执行=111的
删除 BASELINE 信息的 SQL：
select DBMS_SPM.DROP_SQL_PLAN_BASELINE(
'9617D7AA84029C15DFA0F0FCCF3DBD8C',
NULL
) from dual;最终演进到了正确的 PLAN：
OB_ORACLE_4.2 [SKY@SKY] > select sum(c1_pk + c2 + c3) from t1 where c2 = 100;
+------------------+
| SUM(C1_PK+C2+C3) |
+------------------+
| 10003673540 |
+------------------+
1 row in set (0.097 sec)
OB_ORACLE_4.2 [SKY@SKY] > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()) ;
+-----------------------------------------------------------------------------------------------------------+
| COLUMN_VALUE |
+-----------------------------------------------------------------------------------------------------------+
| ========================================================================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| |
| --------------------------------------------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |263053 |1 |113063 |0 |3471 | |
| |1 |+-TABLE RANGE SCAN|T1(IDX_C2)|100001 |261240 |100000 |113063 |0 |109233 | |
| ========================================================================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_SUM(T1.C1_PK + T1.C2 + T1.C3)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_SUM(T1.C1_PK + T1.C2 + T1.C3)]) |
| 1 - output([T1.C1_PK], [T1.C2], [T1.C3]), filter(nil), rowset=256 |
| access([T1.C1_PK], [T1.C2], [T1.C3]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([T1.C2], [T1.C1_PK]), range(100,MIN ; 100,MAX), |
| range_cond([T1.C2 = 100]) |
+-----------------------------------------------------------------------------------------------------------+
15 rows in set (0.002 sec)只剩一个 PLAN BASELINE：
OB_SYS_4.2.5 [root@oceanbase:2883] > select sql_id, evolution, evo_executions, outline_data from GV$OB_PLAN_CACHE_PLAN_STAT where statement = 'select sum(c1_pk + c2 + c3) from t1 where c2 = ?';
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_id | evolution | evo_executions | outline_data |
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| 9617D7AA84029C15DFA0F0FCCF3DBD8C | 1 | 27 | /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SKY"."T1"@"SEL$1") PARALLEL( AUTO ) OPTIMIZER_FEATURES_ENABLE('4.2.1.0') END_OUTLINE_DATA*/ |
| 9617D7AA84029C15DFA0F0FCCF3DBD8C | 1 | 123 | /*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "SKY"."T1"@"SEL$1" "IDX_C2") PARALLEL( AUTO ) OPTIMIZER_FEATURES_ENABLE('4.2.1.0') END_OUTLINE_DATA*/ |
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.020 sec)
OB_SYS_4.2.5 [root@oceanbase:2883] > select sql_id, evolution, evo_executions, outline_data from GV$OB_PLAN_CACHE_PLAN_STAT where statement = 'select sum(c1_pk + c2 + c3) from t1 where c2 = ?';
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_id | evolution | evo_executions | outline_data |
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| 9617D7AA84029C15DFA0F0FCCF3DBD8C | 0 | 123 | /*+BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "SKY"."T1"@"SEL$1" "IDX_C2") PARALLEL( AUTO ) OPTIMIZER_FEATURES_ENABLE('4.2.1.0') END_OUTLINE_DATA*/ |
+----------------------------------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.025 sec)