Sql plan management practice

--To check if optimizer is using baseline or not
select * from v$parameter where name='optimizer_use_sql_plan_baselines'

--Load baseline from cursor cache
--specific plan hash value also givenas it was best plan identified
--Fixed baseline so that no new plan baselines will be captured
DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '1b26c3w3un3a4',
    plan_hash_value => '2168508692',
    fixed => 'YES');
END;
/

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '8tx67bgzqwnvc',
    plan_hash_value => '3455307351',
    fixed => 'YES');
END;
/

--to check query etc.
SELECT plan_hash_value FROM V$SQL WHERE SQL_ID='8tx67bgzqwnvc';

--to check existing plan baselines and handle
SELECT * FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%INSERT INTO sim_res%'
AND    sql_text NOT LIKE '%dba_sql_plan_baselines%';


--Check plan
set serveroutput on size unlimited
set line 1000
set pagesize 1000
select * from table(dbms_xplan.display_CURSOR('8tx67bgzqwnvc', 0, 'ADVANCED'));

--change parameter autodrop
SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_830a045284cdac6c',
    plan_name       => 'SQL_PLAN_862h4aa2cvb3c89f28544',
    attribute_name  => 'fixed',
    attribute_value => 'NO');

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

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_8da397267248b10a',
    plan_name       => 'SQL_PLAN_8v8wr4tt4jc8a1931a7ad',
    attribute_name  => 'autopurge',
    attribute_value => 'NO');

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


--To drop baselines
SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => NULL,
    plan_name  => 'SQL_PLAN_8v8wr4tt4jc8a1931a7ad');
   
  DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/