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的导入、导出等。
如果在创建索引之前为语句创建了基线,然后创建索引。这时候,走索引进行查询可能比全表扫描要高效。
分享标题:sqlplanbaselines(一)
分享链接:http://myzitong.com/article/ieppdi.html