oracle SPM 执行计划管理
浏览数：61 /
时间：2015年06月12日
************************************************************
第一部分：概念
************************************************************
SQL 计划管理是一种随Oracle Database 11g 引入的新功能，通过维护所谓的“SQL
计划基线(SQL plan baseline(11g))”来使系统能够自动控制SQL 计划演变。启用此功能后，
只要证明新生成的SQL 计划与SQL 计划基线相集成不会导致性能回归，就可以进行此项集成。
因此，在执行某个SQL 语句时，只能使用对应的SQL 计划基线中包括的计划。可以使用SQL
优化集自动加载或植入SQL 计划基线。
SQL 计划管理功能的主要优点是系统性能稳定，不会出现计划回归。此外，
该功能还可以节省DBA 的许多时间，这些时间通常花费在确定和分析SQL 性能回归以及
寻找可用的解决方案上.
（1） 即时捕获：
使用自动计划捕获，方法是：将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELIN
ES 设置为TRUE。默认情况下，该参数设置为FALSE。将该参数设置为TRUE
将打开自动标识可重复SQL 语句，以及自动为此类语句创建计划历史记录的功能。
（2） 成批加载：
使用DBMS_SPM 程序包；该程序包支持手动管理SQL
计划基线。使用此程序包，可以将SQL 计划从游标高速缓存或现有的SQL
优化集(STS) 直接加载到SQL计划基线中。对于要从STS 加载到SQL 计划基线的SQL
语句，需要将其SQL计划存储在STS中。使用DBMS_SPM
可以将基线计划的状态从已接受更改为未接受（以及从未接受更改为已接受），还
可以从登台表导出基线计划，然后使用导出的基线计划将SQL
计划基线加载到其它数据库中。
NOTE:
SQL计划管理使用一种叫做SQL计划基准机制。计划基线是针对sql优化器
允许使用并接受的执行计划的一个集合。
在典型使用情况下，数据库只接受那些通过验证并执行良好的执行计划到计划基线中。
-----设置参数：启用sql_plan_baseline
show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
alter system set optimizer_capture_sql_plan_baselines=true;
show parameter optimizer_capture_sql_plan_baselines
---1.准备测试环境
create table t2
(
sid number not null ,
sname varchar2(10)
)
tablespace test;
--循环导入数据
declare
maxrecords constant int:=20000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t2 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => true);
************************************************************
第二部分：为sql 调优集中的sql语句创建计划基线
************************************************************
创建基线的几种方式
1.自动捕获基线
2.从SQL调优集合中加载，通过使用包dbms_spm.load_plans_from_sqlset
3.从库缓存中加载，通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线
----------------------------------*
方式1.自动捕获基线
----------------------------------*
--------案例演示
步骤1：简单查询
set autotrace on;
var v varchar2(5);
exec :v :=1000;
select * from t2 where sid<=:v;
set autotrace off;
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 12000 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1000 | 12000 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SID"<=TO_NUMBER(:V))
步骤2：简单查询
set autotrace on;
var v varchar2(5);
exec :v :=1000;
select * from t2 where sid<=:v;
set autotrace off;
步骤3：查看SQL PLAN BASELINE
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select * from t2 where sid<=:v%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES
步骤4：新建索引
create index index_01 on t2(sid);
exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => true);
步骤5：简单查询
set autotrace on;
var v varchar2(5);
exec :v :=1000;
select * from t2 where sid<=:v;
set autotrace off;
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 12000 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1000 | 12000 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SID"<=TO_NUMBER(:V))
Note
-----
- SQL plan baseline "SQL_PLAN_61zp6hdfv5u8mb860bcf2" used for this statement
步骤6：查看sql plan baseline
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select * from t2 where sid<=:v%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8m8d82fa42 YES NO --未启用
SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES
----------------------------------*
方式2.从SQL调优集合中加载
----------------------------------*
通过使用包dbms_spm.load_plans_from_sqlset
步骤1.新建STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCPYANG_STS'
);
END;
/
---新建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SYS',
description => 'ocpyangtest');
END;
/
步骤2.填充STS
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur);
end;
/
输入 begin_snap 的值: 11647
egin Snapshot Id specified: 11647
输入 end_snap 的值: 11859
nd Snapshot Id specified: 11859
--或指明sql_id
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur);
end;
/
步骤3：从SQLSET中加载即将DBMS_SPM作为输入为sql调优集中包含的每一个查询创建计划基线
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'OCPYANG_STS'
);
END;
/
/********语法
DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES'
commit_rows IN NUMBER := 1000)
RETURN PLS_INTEGER;
**************/
步骤4：查看相关计划基线
select sql_handle,plan_name,sql_text from dba_sql_baselines;
----------------------------------*
方式3.从库缓存中加载
----------------------------------*
通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线.
----方法1：导入一个指定的sqlid
--查看sql_id和hash_value值
select sql_id,hash_value from v$sql where sql_text
like '%select count(1) from scott.tblorders where orderstatus>0 %';
declare
u int;
begin
u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'57pk967xw5jqn',PLAN_HASH_VALUE=>'2002323537');
DBMS_OUTPUT.put_line('导入完成!' );
end;
/
declare
ret varchar2(100);
begin
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id=>'fwjgwwp18z7ad',
--plan_hash_value=>'1601196873'
plan_hash_value=>NULL
);
end;
/
如果执行计划的哈希值没有指定或指定为NULL,则给定SQL语句的所有可用执行计划都会被加载.
---方法2：同时导入多条
declare
u int;
begin
u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'57pk967xw5jqn',PLAN_HASH_VALUE=>'2002323537');
u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'57pk967xw5jqk',PLAN_HASH_VALUE=>'2002323538');
u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'57pk967xw5jqm',PLAN_HASH_VALUE=>'2002323539');
DBMS_OUTPUT.put_line('导入完成!' );
end;
/
----方法3：为某个用户的游标创建基线
declare
ret varchar2(100);
begin
ret := dbms_spm.load_plans_from_cursor_cache(
attribute_name=>'parsing_schema_name',
attribute_value=>'SCOTT');
end;
/
----方法4：为library cache中每一条文本中包含字符串t1的SQL语句创建一个SQL计划基线：
declare
ret varchar2(100);
begin
ret := dbms_spm.load_plans_from_cursor_cache(
attribute_name=>'sql_text',
attribute_value=>'%t1%');
end;
/
/*****语法
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_text IN CLOB,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_handle IN VARCHAR2,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2,
fixed IN VARCHAR2 := 'NO',
enabled IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
******/
---查看是否存在执行计划
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select sid,sname from t5 where sid<=:v%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_e0c42f010eb9d50f SQL_PLAN_f1j1g047bmp8gb73cade2 YES YES
SQL_a9e4491f6b5d9737 SQL_PLAN_amt293xppv5tr14816fa9 YES YES
SQL_93ffdec9273ee793 SQL_PLAN_97zyyt4mmxtwm95fcfc25 YES YES
----查看某个查询是否使用了sql plan baseline
select sql_id,child_number,sql_plan_baseline,sql_text
from v$sql
where sql_plan_baseline is not null
and sql_text like '%select count(*) from scott.tblorders%';
-------案例演示:
select count(1) from scott.tblorders where orderstatus>0;
select sql_id,hash_value from v$sql where sql_text
like '%select count(1) from scott.tblorders where orderstatus>0 %';
declare
u int;
begin
u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'g5f5cz344h5dz',PLAN_HASH_VALUE=>'3360167359');
DBMS_OUTPUT.put_line('导入完成!' );
end;
/
---查看是否存在执行计划
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select count(1) from scott.tblorders where orderstatus>0%';
************************************************************
第三部分：sql plan baseline修改
************************************************************
----1.修改新计划的ACCEPTED为YES
/*********语法
使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法：
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL, --> NULL 表示针对所有SQL
plan_name IN VARCHAR2 := NULL,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
verify IN VARCHAR2 := 'YES',
commit IN VARCHAR2 := 'YES' )
RETURN CLOB;
这里由两个标记控制：
o Verify
+ YES (只有性能更好的计划才会被演化)
+ NO (演化所有的计划)
o Commit
+ YES (直接演化)
+ NO (只生成报告)
这里可以通过不同的排列组合，达到不同的效果：
o 自动接收所有性能更好的执行计划 (Verify->YES, Commit->YES)
o 自动接收所有新的执行计划 (Verify->NO, Commit->YES)
o 比较性能，生成报告，人工确认是否演化 (Verify->NO, Commit->NO)
*********/
SET SERVEROUTPUT ON
DECLARE
l_plans_altered clob;
BEGIN
l_plans_altered := dbms_spm.evolve_sql_plan_baseline(
sql_handle => 'SQL_60fea6835db2e913',
plan_name => 'SQL_PLAN_61zp6hdfv5u8m8d82fa42',
verify =>'NO',
commit =>'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
----2.:修改已有的Baseline
/*********语法
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2 )
RETURN PLS_INTEGER;
************/
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_60fea6835db2e913',
plan_name => 'SQL_PLAN_61zp6hdfv5u8mb860bcf2',
attribute_name => 'ENABLED',
attribute_value => 'NO');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
----3.删除已有的Baseline
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SQL_3a8461388a9bfa52',
plan_name => NULL);
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select * from t2 where sid<=:v%';
SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8m8d82fa42 YES YES
SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8mb860bcf2 NO YES
set autotrace on;
var v varchar2(5);
exec :v :=1000;
select * from t2 where sid<=:v;
set autotrace off;
执行计划
----------------------------------------------------------
Plan hash value: 1194324917
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 12000 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1000 | 12000 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_01 | 180 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
************************************************************
第四部分：sql plan baseline迁移
************************************************************
迁移步骤:
1.使用dbms_spm包和create_stgtab_baseline创建一个过程表
2.使用dbms_spm.pack_stgtab_baseline将计划基线填充到第一步中新建的过程表
3.使用数据库链接或数据泵复制到目标数据库中
4.使用DBMS_SPM.unpack_stgtab_baseline导入计划基线到目标数据库中
---1.创建舞台表
BEGIN
dbms_spm.create_stgtab_baseline(
table_name => 'BASELINE_STG01',
table_owner => 'SCOTT', --不能新建在SYS账户下
tablespace_name=>'USERS');
END;
/
/****** dbms_spm.create_stgtab_baseline语法
This procedure creates a staging table used
for transporting SQL plan baselines from one system to another.
Syntax
DBMS_SPM.CREATE_STGTAB_BASELINE (
table_name IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL);
The creation of staging table is the first step.
To migrate SQL plan baselines from one system to another,
the user/DBA has to perform a series of steps as follows:
Create a staging table in the source system
Select SQL plan baselines in the source system and pack them into the staging table
Export staging table into a flat file using Oracle EXP utility or Data Pump
Transfer flat file to the target system
Import staging table from the flat file using Oracle IMP utility or Data Pump
Select SQL plan baselines from the staging table and unpack them into the target system
************/
----2.将sql plan baseline从数据字典复制到舞台表
declare
k int;
begin
k:=dbms_spm.pack_stgtab_baseline(
TABLE_NAME=>'BASELINE_STG01',
TABLE_OWNER=>'SCOTT'
);
end;
/
declare
v_ret number(100);
begin
v_ret := dbms_spm.pack_stgtab_baseline(
table_name =>'mystgtab',
table_owner=>user,
sql_handle=>'SQL_e436abaac44f99d8',
--plan_name=>'SQL_PLAN_f8dpbpb24z6fs94ecae5c',
);
end;
/
/**********语法:
DBMS_SPM.PACK_STGTAB_BASELINE (
table_name IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
sql_text IN CLOB := NULL,
creator IN VARCHAR2 := NULL, origin IN VARCHAR2 := NULL,
enabled IN VARCHAR2 := NULL,
accepted IN VARCHAR2 := NULL,
fixed IN VARCHAR2 := NULL,
module IN VARCHAR2 := NULL,
action IN VARCHAR2 := NULL)
RETURN NUMBER;
*****/
----3.用expdp\impdp或exp,imp工具从测试库将表移到目标库
----4.将sql plan baseline从舞台表复制到数据字典
---4.1 将所有sql plan baseline从舞台表复制到数据字典
SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name =>'BASELINE_STG01',
table_owner => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
---4.2 将sql plan baseline中有关T1表的从舞台表复制到数据字典
declare
v_ret varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
table_name =>'BASELINE_STG01',
table_owner=>'SCOTT',
sql_text=>'%FROM t1%'
);
end;
/
/*********语法:
DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
sql_text IN CLOB := NULL,
creator IN VARCHAR2 := NULL, origin IN VARCHAR2 := NULL,
enabled IN VARCHAR2 := NULL,
accepted IN VARCHAR2 := NULL,
fixed IN VARCHAR2 := NULL,
module IN VARCHAR2 := NULL,
action IN VARCHAR2 := NULL)
RETURN NUMBER;
如果只指定table_name与table_owner,就是处理所有sql plan baseline。
sql_handle与plan_name一起能精确识别一个sql plan baseline,plan_name为可选项。
sql_text里面区分大小写
/********
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select sid,sname from t4 where sid<=:v%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_4e6155ac1d5b5962 SQL_PLAN_4wsapphfpqqb214816fa9 YES YES
---删除sql计划基线
declare
v_ret varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
sql_handle=>'mystgtab',
plan_name=>'swew223'
);
end;
/
这两个参数至少要指定一个。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
