created: 20190623003755119
creator: vplesnila
modified: 20190906141112050
modifier: vplesnila
tags: Oracle
title: SQL Baseline from AWR
type: text/plain

-- Création d'un SQL baseline à partir du AWR
---------------------------------------------


-- Création d'un SQLSET
---------------------
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'MySTS01');
END;
/
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MySTS01',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/


-- Dans le SQLSET=MySTS01 créé précédemment, nous chargons l'historique du SQL_ID=d1khdngkga3nm 
-- entre les snapshot AWR 50947 et 50951
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(
          begin_snap=>50947, 
          end_snap=>50951,
          basic_filter=>'sql_id = ''d1khdngkga3nm''',
          attribute_list=>'ALL')
        ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

-- Pour voir ce que contient le SQLSET=MySTS01
-----------------------------------------------
SELECT
  buffer_gets              ,
  optimizer_cost           ,
  plan_hash_value          ,
  sql_id                   
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
             )
/


-- On crée un SQL Baseline à partir du plan_hash_value=473902782 du SQLSET=MySTS01
----------------------------------------------------------------------------------
DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS01',
    basic_filter=>'plan_hash_value = ''473902782'''
    );
END;
/


-- Liste des SQL baseslines
----------------------------
set lines 180 pages 999
col created for a20 trunc
col signature for 9999999999999999999

select signature, sql_handle, plan_name, enabled, accepted, fixed, origin,created 
from   dba_sql_plan_baselines;


-- Pour fixer un SQL basesline
------------------------------
begin   dbms_output.put_line(dbms_spm.ALTER_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_8uy4magb69vtuf8f30e4c',attribute_name=>'fixed',attribute_value=>'yes'));
end;
/


-- Obtenir le PLAN_ID et le OUTLINE d'un SQL basesline
-- A partir de la SIGNATURE=9005682359107037619 du SQL Baseline, nous retrouvons le PLAN_ID
-------------------------------------------------------------------------------------------
SELECT TO_CHAR(so.signature) signature
  ,      so.plan_id
  ,      DECODE(ad.origin, 1, 'MANUAL-LOAD',
                        2, 'AUTO-CAPTURE',
                        3, 'MANUAL-SQLTUNE',
                        4, 'AUTO-SQLTUNE',
                        5, 'STORED-OUTLINE',
                           'UNKNOWN') origin
  ,      DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO') enabled
  ,      DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO') accepted
  ,      DECODE(BITAND(so.flags, 64), 64, 'NO', 'YES') reproduced
  FROM   sys.sqlobj$        so
  ,      sys.sqlobj$auxdata ad
  WHERE  ad.signature = so.signature
    AND    ad.plan_id   = so.plan_id
    AND    so.signature = 9005682359107037619;


-- Pour obtenir le OUTLINE du SQL Baseline, il nous faut connaître 
-- la SIGNATURE=9005682359107037619 et le PLAN_ID=263533726
------------------------------------------------------------------
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints 
    from   xmltable('/outline_data/hint' 
           passing (select xmltype(comp_data) xml 
                    from   sys.sqlobj$data 
                    where  signature = 9005682359107037619
                    and    plan_id   = 263533726)) x;  

                    

-- Supression du SQL Baseline ayant le SQL_HANDLE=SQL_7cfa9c643693a9b3 et 
-- le PLAN_NAME=SQL_PLAN_7tynwchv97admdbd90e8e
-------------------------------------------------------------------------
set serveroutput ON
DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => 'SQL_7cfa9c643693a9b3',
     plan_name=>'SQL_PLAN_7tynwchv97admdbd90e8e'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/  

      
-- Afficher le plan d'exécution d'un SQL basesline
-- Dans notre exemple, le SQL Baseline a le SQL_HANDLE=SQL_24c0db16ff852641
------------------------------------------------------------------------------
set lines 200 pages 0
select * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_24c0db16ff852641'));