首先，什么是SQL的执行计划，包括为什么一个SQL会有多个不同执行计划等类似问题，我这里就不做讨论了，各位可以网上百度
在这里，我主要讨论一下，当一个常用的SQL，执行计划忽然发生改变，我们如何最快速度的将其执行计划稳定为平日使用的合理执行计划。
11G现在比较多，我们先讨论11G
11G，有个新特性，或者说一个新的功能包（package) ,SPM(Sql Plan Management)
有两个初始化参数与其相关
NAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_use_sql_plan_baselines boolean TRUE
我们用来稳定执行计划的，就是SPM中的SQL baseline。
第二个参数很好理解
第一个参数，则是是否开启捕获baseline ，什么意思呢
当一个新SQL 执行，并产生执行计划时，如果 第一个参数为true时，oracle会自动为其产生一个baseline，该baseline对应这个执行计划（
当该SQL因为统计信息，或什么其他东西 而导致执行计划发生改变时，那么会执行一次，并产生一个新的baseline，但是下一次再执行时，依旧会使用第一个baseline（起到稳定执行计划的作用），只有当DBA确认 第二个执行计划确实比第一个好时，DBA可以通过DBMS_SPM包进行调整，使用第二个执行计划产生的basaline，那么以后该sql的执行计划就会使用第二个执行计划。
默认，我们捕获baseline是关闭的，所以一个SQL在执行时，不会有baseline产生。
我们通过从library cache中load一个SQL合理的执行计划 为该SQL的baseline，从而保证其计划稳定
SQL> create table haha(a varchar2(30),b number);Table created.SQL> insert into haha select object_id,object_id from dba_objects;75407 rows created.SQL> create index haha_idx on haha(a);Index created.
SQL>exec dbms_stats.gather_table_stats('SYS','HAHA',NO_INVALIDATE =>FALSE);PL/SQL procedure successfully completed.SQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> select sql_id,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%haha where%';SQL_ID CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------SQL_TEXT--------------------------------------------------------------------------------auwza0aq10mk0 0 1624320650select * from haha where a='10000'我们收集了统计信息，并且执行语句select * from haha where a=10000;通过查v$sql视图获取它的sql_id,child_number,我们现在查看执行计划
QL> select * from table(dbms_xplan.display_cursor('auwza0aq10mk0',0));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID auwza0aq10mk0, child number 0-------------------------------------select * from haha where a='10000'Plan hash value: 1624320650----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| || 1 | TABLE ACCESS BY INDEX ROWID| HAHA | 1 | 11 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | HAHA_IDX | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("A"='10000')19 rows selected.这个执行计划使用了索引，执行计划是良好的。现在我们通过修改统计信息，优化器模式，改变执行计划（产生新的执行计划并使用它，这个执行计划是不良好的，也即是错误的执行计划）。
SQL> alter session set optimizer_mode=first_rows_1;PL/SQL procedure successfully completed.SQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> exec dbms_stats.set_index_stats(ownname=>user,indname=>'HAHA_IDX',NUMROWS=>1,numdist=>1,no_invalidate=>false);PL/SQL procedure successfully completed.SQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> select sql_id,child_number,plan_hash_value,sql_text from v$sql where sql_text like '%haha where%';SQL_ID CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------SQL_TEXT------------------------------------------------------------------------------------------------------------------------------------------------------auwza0aq10mk0 0 1624320650select * from haha where a='10000'auwza0aq10mk0 1 3694936490select * from haha where a='10000'
我们可以看到，这里已经产生了两个不同的执行计划，注意看PLAN_HASH_VALUE列。
我们来查询一下新产生的PLAN
SQL> select * from table(dbms_xplan.display_cursor('auwza0aq10mk0',1));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID auwza0aq10mk0, child number 1-------------------------------------select * from haha where a='10000'Plan hash value: 3694936490--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 32 (100)| ||* 1 | TABLE ACCESS FULL| HAHA | 1 | 11 | 32 (4)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"='10000')18 rows selected.这个新的PLAN，为全表扫描，是不正确的执行计划
现在我在当前环境下执行该sql，会一直采用这个错误的plan，在业务系统中是很致命的。
SQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID auwza0aq10mk0, child number 1-------------------------------------select * from haha where a='10000'Plan hash value: 3694936490--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 32 (100)| ||* 1 | TABLE ACCESS FULL| HAHA | 1 | 11 | 32 (4)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"='10000')18 rows selected.那么我想将正确的执行计划稳定上，该如何做？
通过使用11G的新包，DBMS_SPM就可以实现
SQL BASELINE有两个状态，一个是ENABLED，一个是ACCEPTED，两个都为true，oracle才会使用，如果有同一个SQL的多个SQL_BASELINE存在，且状态都为true，那么会根据成本大小进行判断。（注：当自动捕获开启时，一个SQL，每产生一个不同的执行计划，都会产生一个baseline，enabled都为true，但只有第一个获取的baseline，accepted才为true，其他都为false）
SQL>variable cnt number; SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'auwza0aq10mk0',plan_hash_value=>1624320650);PL/SQL procedure successfully completed.SQL> SELECT sql_handle, sql_text, plan_name, enabled,accepted FROM dba_sql_plan_baselines;SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- ---SQL_32507237f9e5cfe6 select * from haha where a='10000' SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YES现在，我们再从当前环境下执行该SQL
SQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID auwza0aq10mk0, child number 3-------------------------------------select * from haha where a='10000'Plan hash value: 1624320650----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 14685 (100)| || 1 | TABLE ACCESS BY INDEX ROWID| HAHA | 1 | 11 | 14685 (1)| 00:02:57 ||* 2 | INDEX RANGE SCAN | HAHA_IDX | 1 | | 178 (0)| 00:00:03 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("A"='10000')Note----- - SQL plan baseline SQL_PLAN_34n3k6zwybmz6dd4bd292 used for this statement执行计划已经变为了正常的索引扫描，注意Note，说明有一个SQL_BASELINE在使用。
如果我们不想使用该SQL_BASELINE了，该如何呢？
可以有两个方法实现
第一、将该BASELINE的属性，Enabled，Accepted 改为false（Accepted在11r2里面改不了，只能改enabled）
第二、将该BASELINE删除(SQL_SPM.DROP_SQL_PLAN_BASELINE）
我们来一一演示
第一、修改属性
SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC------------------------------ ----------------------------------- ------------------------------ --- ---SQL_32507237f9e5cfe6 select * from haha where a='10000' SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YESSQL>variable cnt number; SQL>EXECUTE :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle =>'SQL_32507237f9e5cfe6' ,plan_name =>'SQL_PLAN_34n3k6zwybmz6dd4bd292',attribute_name=>'enabled',attribute_value =>'NO');PL/SQL procedure successfully completed.SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC------------------------------ ----------------------------------- ------------------------------ --- ---SQL_32507237f9e5cfe6 select * from haha where a='10000' SQL_PLAN_34n3k6zwybmz6dd4bd292 NO YES
看，我们执行完DBMS_SPM.ALTER_SQL_PLAN_BASELINE之后，通过查询dba_sql_plan_baselines可以看到 Enabled属性已经变成了NO
现在我们执行select * from haha where a='10000';
SQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID auwza0aq10mk0, child number 0-------------------------------------select * from haha where a='10000'Plan hash value: 3694936490--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 32 (100)| ||* 1 | TABLE ACCESS FULL| HAHA | 1 | 11 | 32 (4)| 00:00:01 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"='10000')18 rows selected.看，这里的执行计划走成了全表扫描，而且note也没有了。（baseline不起作用了）
第二、删除sql plan baseline
我们除了修改属性以外，还有一种方法，那就是删除sql plan baseline
现在我们将属性改回（改回不是必须的，不过为了模拟场景而已），并删除该baseline
SQL>variable cnt number; SQL>EXECUTE :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle =>'SQL_32507237f9e5cfe6' ,plan_name =>'SQL_PLAN_34n3k6zwybmz6dd4bd292',attribute_name=>'enabled',attribute_value =>'YES');
好，现在我们查看，并删除（SQL_SPM.DROP_SQL_PLAN_BASELINE 该函数很简单，只有两个元素（输入值）)
SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC------------------------------ ----------------------------------- ------------------------------ --- ---SQL_32507237f9e5cfe6 select * from haha where a='10000' SQL_PLAN_34n3k6zwybmz6dd4bd292 YES YESSQL>variable cnt number; SQL>EXECUTE :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle =>'SQL_32507237f9e5cfe6' ,plan_name =>'SQL_PLAN_34n3k6zwybmz6dd4bd292');PL/SQL procedure successfully completed.SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines;no rows selectedSQL> select * from haha where a='10000';A B------------------------------ ----------10000 10000SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID auwza0aq10mk0, child number 0-------------------------------------select * from haha where a='10000'Plan hash value: 3694936490--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 32 (100)| ||* 1 | TABLE ACCESS FULL| HAHA | 1 | 11 | 32 (4)| 00:00:01 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A"='10000')18 rows selected.我们看到执行计划中note也消失了，且走为全表扫描。
to be continue--