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

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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

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

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

本文题目：sqlplanbaselines（一）
文章转载：http://chengdu.cdxwcx.cn/article/ieppdi.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

sqlplanbaselines（一）

其他资讯

腾讯云服务器打字乱码腾讯云服务器问题

重启linux网关命令 linux重启网络配置命令

智能聊天国内不能用怎么办 chatroulette在中国能用吗

域名该怎么使用域名该怎么使用网络

云锁服务器安全防护云服务器安全设置

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

sqlplanbaselines（一）

其他资讯

腾讯云服务器打字乱码 腾讯云服务器问题

重启linux网关命令 linux重启网络配置命令

智能聊天国内不能用怎么办 chatroulette在中国能用吗

域名该怎么使用 域名该怎么使用网络

云锁服务器安全防护 云服务器安全设置

成都网站建设设计将想法与焦点和您一起共享

腾讯云服务器打字乱码腾讯云服务器问题

域名该怎么使用域名该怎么使用网络

云锁服务器安全防护云服务器安全设置