oracleSPM執行計劃管理
************************************************************
第一部分：概念
************************************************************
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;
/
這兩個參數至少要指定一個。
