oracleSPM执行计划管理(二)
D_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',PLA