spm管理

来源：互联网发布：山东舜德数据怎么样编辑：程序博客网时间：2024/05/21 08:02

创建spm baseline

1、从内存中获取并创建spm

set serveroutput on ;
variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '3c932mj8rv588', PLAN_HASH_VALUE =>1483166007);

PL/SQL procedure successfully completed
cnt
---------
1

2、从AWR中获取sql的执行计划做SPM

EXEC DBMS_SQLTUNE.CREATE_SQLSET('smpsqlset001');
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(8176, 8177,'sql_id='||CHR(39)||'4xag7g6mj150g'||CHR(39)||' and plan_hash_value=3841355674',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('smpsqlset001', baseline_ref_cursor);
end;
/

set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => 'smpsqlset001',sqlset_owner => 'USERNAME',fixed => 'NO',enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/

检查是否生成

select a.sql_handle,a.plan_name,a.creator,a.enabled,a.accepted,a.fixed,a.reproduced,a.autopurge,a.sql_text from dba_sql_plan_baselines a;

检查sql信息

select b.sql_id,
a.sql_handle,
a.sql_text,
a.plan_name,
a.origin,
a.enabled,
a.accepted,
a.fixed,
to_char(a.created,'yyyy-mm-dd hh24:mi:ss'),
to_char(a.last_modified,'yyyy-mm-dd hh24:mi:ss')
from dba_sql_plan_baselines a, v$sql b
where a.signature = b.EXACT_MATCHING_SIGNATURE
and b.sql_id = 'b19d8up0g8qj4'

检查具体的执行计划
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_54569258c807f7f1')) t; --sql_handle

--固定执行计划
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_54569258c807f7f1',
plan_name => 'SQL_PLAN_58pnkb340gxzjd53bb3e2',
attribute_name => 'fixed',
attribute_value => 'YES');

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

0 0