Baselines

Creating baselines based on a plan from AWR

Create a SQL Tuning sets

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'STS01',
    description => 'SQL Tuning Set for SQL Plan Baseline from AWR');
END;

Load AWR plans

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>start_awr_snapshot, end_snap=>end_awr_snapshot,basic_filter=>'sql_id = ''sql_id''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

Load the required plan

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS01', 
    basic_filter=>'plan_hash_value = ''good plan_hash_value'''
    );
END;
/

Confirm plan is loaded

SELECT * FROM dba_sql_plan_baselines ;