************************************************************
第一部分：概念
************************************************************
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优化器
允许使用并接受的执行计划的一个集合。
在典型使用情况下，数据库只接受那些通过验证并执行良好的执行计划到计划基线中。
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
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
;
/
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
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函数为一条已经在游标缓存中的语句创建基线.
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=>
NULL
);
end
;
/
如果执行计划的哈希值没有指定或指定为
NULL
,则给定SQL语句的所有可用执行计划都会被加载.
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
;
/
declare
ret varchar2(100);
begin
ret := dbms_spm.load_plans_from_cursor_cache(
attribute_name=>
'parsing_schema_name'
,
attribute_value=>
'SCOTT'
);
end
;
/
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
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修改
************************************************************
/*********语法
使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法：
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle
IN
VARCHAR2 :=
NULL
,
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
;
/
/*********语法
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
;
/
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导入计划基线到目标数据库中
BEGIN
dbms_spm.create_stgtab_baseline(
table_name =>
'BASELINE_STG01'
,
table_owner =>
'SCOTT'
,
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
************/
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'
,
);
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;
*****/
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
;
/
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
declare
v_ret varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
sql_handle=>
'mystgtab'
,
plan_name=>
'swew223'
);
end
;
/
这两个参数至少要指定一个。
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
