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

2022-05-30 04:08:53

首先，什么是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> 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				    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‘

auwza0aq10mk0		 1	3694936490
select * 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				    10000

SQL> 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				    10000

SQL> 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在使用。


to be continue--
























简单讨论在11G，10G中如何稳定SQL执行计划,布布扣,bubuko.com

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

上一篇：【SQL Server DBA】日常巡检1：数据库空间、状态、使用的监控


下一篇：oracle 索引类型