************************************************************
第一部份：概念
************************************************************
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;
/
这两个参数最少要指定一个。
*************************************************
阅读背景：
oracle SPM 执行计划管理
来源：互联网
分享到：
