oracleSPM执行计划管理(四)
N_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从数