sqlplanbaselines（一）

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的导入、导出等。

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