sql plan baselines（一）

sql plan baselines

 

在一个成熟的生产系统上，我们总是希望运行越稳定越好，执行计划越确定越好。但由于统计信息的变化、优化参数的修改、系统设置的修改、profile的创建等，都有可能造成执行计划的变化，从而性能上会有变化。这种变化不一定都是差的方向，比如优化器版本的升级，一般会使执行计划更加优化，但作为运维DBA，要确保万无一失，可能还是会相信自己。

为了应对可能的执行计划变化，oracle提供了baseline，我们翻译为基线，用于保证执行计划的稳定。

 

以下用实验来说明：

 

sql baseline有两种生成方式：

（1）需要通过AWR报告或者sql tuning set（STS）生成，因此，首先需要查看当前系统中可用的STS：

 

[oracle@localhost ~]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 23 00:10:50 2012

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> conn scott/scott

Connected.

SQL> select * from dba_sqlset;

 

ID    NAME          OWNER       DESCRIPTI     CREATED   LAST_MODI STATEMENT_COUNT

---------- ---------------------- -------------- ---------------------------  ---------------- --------------------- ---------------------------

     2 my_sql_tuningset    SCOTT  i/o intensive workload  22-DEC-12   22-DEC-12              0

 

SQL> declare  

  2  my_plans pls_integer;

  3  begin

  4  my_plans:=dbms_spm.load_plans_from_sqlset(sqlset_name=>'my_sql_tuningset');

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

如果是从AWR报告中提取，则需要先用AWR报告生成STS，再执行以上步骤。

（2）从shared_pool_area中获取sql

比如我们刚刚执行了select * from dept where deptno=10，想要将此语句归入baseline中，操作如下：

SQL> select sql_id  from v$sql where sql_text like 'select * from dept%';

 

SQL_ID

-------------

96kbu89824wkq

 

SQL> declare

  2  my_plans pls_integer;

  3  begin

  4  my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'96kbu89824wkq');

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

每次数据库编译一条sql语句的时候，优化器首先基于成本找到最优的计划，然后在基线中查找是否有匹配的计划，如果找到了，就使用匹配的计划；如果没找到，就对基线中的计划进行比较，选出成本最低的。最开始产生的最优计划不会被使用，而是作为一个不可接受的计划放入计划历史中，如果由于系统变化使得所有基线中的计划都不可用，才会使用刚才选出来的最优计划。

如果oracle确认不可接受的计划不会造成性能问题（比基线中的计划性能好），就会将其放入基线中。

SQL> show parameter optimizer_use_sql

--此参数的值为true表示允许使用基线

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_sql_plan_baselines     boolean     TRUE

 

SQL> set autot on

SQL> select * from dept where deptno=10;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2852011669

 

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DEPTNO"=10)

Note

-----

   - SQL plan baseline "SQL_PLAN_1tkkvscv7fp020348d329" used for this statement

通过以上查询我们可以看到：语句使用了基线"SQL_PLAN_1tkkvscv7fp020348d329"

 

通过如下语句显示基线的内容：

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(

  2  plan_name=>'SQL_PLAN_1tkkvscv7fp020348d329',format=>'basic'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

SQL handle: SYS_SQL_1cca5bc336775402

SQL text: select * from dept where deptno=10

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_1tkkvscv7fp020348d329         Plan id: 55104297

Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD

--------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2852011669

 

-----------------------------------------------

| Id  | Operation                   | Name    |

-----------------------------------------------

|   0 | SELECT STATEMENT            |         |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |

|   2 |   INDEX UNIQUE SCAN         | PK_DEPT |

-----------------------------------------------

 

20 rows selected.

里面包含了语句的内容、执行计划等信息。

如果不知道plan_name，可以通过如下方式获得：

select * from dba_sql_plan_baselines;

 

下面我们看一下在索引被删除，无法用baseline的情况能否正常运行语句，对基线是否有影响：（仅用于实验，实际中不要删除主键）

SQL> alter table dept drop index PK_DEPT;

alter table dept drop index PK_DEPT

                      *

ERROR at line 1:

ORA-00905: missing keyword

 

SQL>  drop index PK_DEPT;

 drop index PK_DEPT

            *

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

--由于存在主键约束，无法删除索引

SQL> alter table dept drop constraint pk_dept;

alter table dept drop constraint pk_dept

                                 *

ERROR at line 1:

ORA-02273: this unique/primary key is referenced by some foreign keys

--由于存在外键约束，无法删除主键

SQL> drop table emp;

 

Table dropped.

 

SQL> alter table dept drop constraint pk_dept;

 

Table altered.

SQL> set autot on

SQL> select * from dept where deptno=10;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3383998547

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("DEPTNO"=10)

 

--没办法使用基线，只能全表扫描了。那么这时的基线还存在吗？

SQL> select plan_name from dba_sql_plan_baselines;

 

PLAN_NAME

------------------------------

SQL_PLAN_1tkkvscv7fp020348d329

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_dxw60bwfynb5h0e23be79

--基线依然存在

尽管如此，但我们重新建立索引pk_dept后，基线还是不能用了：

SQL> create index pk_dept on dept(deptno);

 

Index created.

 

SQL> select * from dept where deptno=10;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2985873453

 

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     1 |    20 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | PK_DEPT |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("DEPTNO"=10)

 

通过手工方式，可以将我们认为性能没有问题的执行计划（对应的基线）插入到系统基线中，这时候oracle并不会检查插入的基线性能是否真的好。

我们将上面的语句使用全表扫描查询，并将其生成基线

SQL> set autot on

SQL> select /*+full(dept)*/* from dept where deptno=10;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3383998547

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DEPTNO"=10)

 

SQL> select sql_id  from v$sql where sql_text like 'select /*+full(dept)*/* from dept%';

 

SQL_ID

-------------

1gbphzt5d0159

 

SQL> declare

  2   plan pls_integer;

  3   begin

  4  plan:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1gbphzt5d0159');

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

然而这个语句并不会被select * from dept where deptno=10;使用到。即使是下面的语句也不能用到基线：

SQL> select /*+full(dept)*/* from dept where deptno='10';

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3383998547

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("DEPTNO"=10)

Oracle认为两个语句是不同的，只有在除了空格数以外完全相同的语句，才算是同一个语句。

 

如何删除一个基线呢？

dbms_spm中只提供了drop_sql_plan_baseline函数

找到dbms_spm的源码，该函数的声明为：

  FUNCTION drop_sql_plan_baseline( sql_handle         IN VARCHAR2 := NULL,
                                   plan_name          IN VARCHAR2 := NULL
                                 )
  RETURN PLS_INTEGER;

因此需要定义一个pls_integer类型的变量去接受结果：

SQL> declare

ret pls_integer;

 begin

ret:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_1cca5bc336775402',

plan_name=> 'SQL_PLAN_1tkkvscv7fp020348d329');

end;

/  2    3    4    5    6 

 

PL/SQL procedure successfully completed.

 

SQL> select plan_name from dba_sql_plan_baselines;

 

PLAN_NAME

------------------------------

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_1tkkvscv7fp02da6909c3

SQL_PLAN_dxw60bwfynb5h0e23be79

--可以看到，SQL_PLAN_1tkkvscv7fp020348d329被删除了

 

本次主要讲了baseline的生成、查看和删除。Baseline对于系统中sql的稳定性、性能有重要作用，因此下一部分实验baseline的导入、导出等。

如果在创建索引之前为语句创建了基线，然后创建索引。这时候，走索引进行查询可能比全表扫描要高效。