Oracle SPMベースライン登録

select name,value from v$parameter where name like'%sql_plan%' order by name
;Copy

SQL> alter system set optimizer_capture_sql_plan_baselines=FALSE
  2  ;

システムが変更されました。

SQL> alter system set optimizer_use_sql_plan_baselines=TRUE
  2  ;

システムが変更されました。

SQL> set lin 200
SQL> COLUMN name FORMAT A40
SQL> COLUMN value FORMAT A5
SQL> select name,value from v$parameter where name like'%sql_plan%' order by name
  2  ;

NAME                                     VALUE
---------------------------------------- -----
optimizer_capture_sql_plan_baselines     FALSE
optimizer_use_sql_plan_baselines         TRUE

SQL>Copy

select
 t.INST_ID  -- どこのインスタンスなのか
,t.SQL_ID   -- 
,t.CHILD_NUMBER  -- 共有プールからパージする際に利用
,t.ADDRESS       -- 共有プールからパージする際に利用
,t.HASH_VALUE
,t.PLAN_HASH_VALUE     -- ベースライン登録時に利用
,t.EXACT_MATCHING_SIGNATURE    -- 正規化後SQLシグネチャ
,t.SQL_PLAN_BASELINE
,t.FIRST_LOAD_TIME
,t.PARSING_SCHEMA_NAME
,t.SQL_TEXT 
from gv$sql t where t.SQL_ID = 'banf5vm5rpd12' 
order by t.INST_ID,t.CHILD_NUMBER
;Copy

declare
    aa number;
begin
    aa:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
    sql_id =>'banf5vm5rpd12',
    plan_hash_value => 2470095318);
end
;Copy

select t.SIGNATURE
,t.SQL_HANDLE   -- これを後に使用します
,t.PLAN_NAME
,t.CREATOR
,t.CREATED
,t.MODULE
,t.ORIGIN
,t.ACCEPTED
,t.EXECUTIONS
--,t.SQL_TEXT
 from  dba_sql_plan_baselines t 
where  t.ACCEPTED ='YES'
order by t.CREATED desc
;Copy

select
 s.INST_ID
,s.SQL_ID   -- この値を取得
,s.CHILD_NUMBER
,s.SQL_TEXT
from gv$sql s 
where s.SQL_TEXT like '%* g7j2u2mg11752 *%'
;Copy

declare
    aa number;
begin
    aa:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
    sql_id =>'1wykrqt6a3r0y',
    sql_handle => 'SQL_81de8173c820318d');
end;
Copy

SELECT
 b.SIGNATURE
,b.SQL_HANDLE
,b.PLAN_NAME
--,b.SQL_TEXT
,b.CREATED
,b.ORIGIN
,b.ACCEPTED
,b.EXECUTIONS 
FROM dba_sql_plan_baselines b
where b.SQL_HANDLE='SQL_81de8173c820318d'
and b.ORIGIN='MANUAL-LOAD-FROM-CURSOR-CACHE'
order by b.CREATED desc
;Copy

declare
    aa number;
begin
    aa:= DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    sql_handle =>'SQL_81de8173c820318d',
    plan_name => 'SQL_PLAN_83rn1fg420ccd441fc3ea');
end;Copy

declare
exesql varchar2(800);
BEGIN
FOR CUR IN (select  SQL_HANDLE,PLAN_NAME from  dba_sql_plan_baselines order by CREATED desc fetch first 10 rows only ) LOOP
exesql := 'declare aa number; begin aa:= DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle =>'''|| CUR.SQL_HANDLE||''',plan_name => '''|| CUR.PLAN_NAME||'''); end;';
EXECUTE IMMEDIATE exesql;
DBMS_OUTPUT.PUT_LINE(exesql);
END LOOP;
END;Copy

select
 b.SIGNATURE
,b.SQL_HANDLE
,b.PLAN_NAME
--,b.SQL_TEXT
,b.CREATED
,b.VERSION
,b.CREATOR
,b.CREATED
,b.MODULE
,b.ORIGIN
,b.ACCEPTED
,b.EXECUTIONS
FROM dba_sql_plan_baselines b
where b.SQL_HANDLE='SQL_81de8173c820318d'
order by b.CREATED
;Copy

exec sys.dbms_shared_pool.purge('0000000892AE9F70,3725630626','C')
;Copy

select
 t.INST_ID
,t.SQL_ID
,t.CHILD_NUMBER
,t.ADDRESS
,t.HASH_VALUE
,t.PLAN_HASH_VALUE
,t.SQL_PLAN_BASELINE
,t.FIRST_LOAD_TIME
,t.PARSING_SCHEMA_NAME
 from gv$sql t
 where t.SQL_ID = 'g7j2u2mg11752'
order by t.INST_ID,t.CHILD_NUMBER
;
Copy

select * from table(
   dbms_xplan.display_sql_plan_baseline(
   plan_name=>'SQL_PLAN_83rn1fg420ccd2720d6b7',
   format=>'basic'
   )
)Copy

select b.PLAN_NAME,b.SIGNATURE,b.SQL_HANDLE,b.CREATED
FROM dba_sql_plan_baselines b where b.ACCEPTED ='YES'
order by b.CREATED,b.SQL_HANDLE desc
;Copy