简单讨论在11G，10G中如何稳定SQL执行计划 - 数据库编程

首先，什么是SQL的执行计划，包括为什么一个SQL会有多个不同执行计划等类似问题，我这里就不做讨论了，各位可以网上百度

在这里，我主要讨论一下，当一个常用的SQL，执行计划忽然发生改变，我们如何最快速度的将其执行计划稳定为平日使用的合理执行计划。

11G现在比较多，我们先讨论11G

11G，有个新特性，或者说一个新的功能包（package) ,SPM(Sql Plan Management)

有两个初始化参数与其相关

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean	 FALSE
optimizer_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				    10000

SQL> 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	1624320650
select * 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				    10000

SQL> ex

简单讨论在11G，10G中如何稳定SQL执行计划(一)