How to load sql plan baseline from AWR

1. Find out the SQL ID and Number of SQL Plan exist in AWR History.

SELECT SQL_ID, COUNT (*)
    FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
    WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;

2. Find out the SQL Plan Hash Value using SQL ID.

SELECT DISTINCT PLAN_HASH_VALUE,SQL_ID  FROM DBA_HIST_SQLSTAT

WHERE SQL_ID='&SQL_ID';

3. Check the SQL Execution Plan Cost and choose the best one. Note that you have to select Hash Vale and SNAP_ID for which you want to load into your Plan Baseline.

SELECT SS.SNAP_ID,

     SS.INSTANCE_NUMBER,

     BEGIN_INTERVAL_TIME,

     SQL_ID,

     PLAN_HASH_VALUE,OPTIMIZER_COST,

     DISK_READS_TOTAL,

     BUFFER_GETS_TOTAL,

     ROWS_PROCESSED_TOTAL,

     CPU_TIME_TOTAL,

     ELAPSED_TIME_TOTAL,

     IOWAIT_TOTAL,

     NVL (EXECUTIONS_DELTA, 0) EXECS,

       (  ELAPSED_TIME_DELTA

        / DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))

     / 1000000

        AVG_ETIME,

     (  BUFFER_GETS_DELTA

      / DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))

        AVG_LIO

FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

WHERE     SQL_ID = '&SQL_ID'

     AND SS.SNAP_ID = S.SNAP_ID

     AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER

     AND EXECUTIONS_DELTA > 0

ORDER BY 1, 2, 3;

4. Check that STS has already existed or not in history if it is exist chose a different name of drop the existing one.

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22'));

BEGIN

   DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_f9948h4y2aa22');

END;

5. Create STS.

BEGIN

  DBMS_SQLTUNE.CREATE_SQLSET(

    sqlset_name => 'STS_f9948h4y2aa22',

    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');

END;

6. Load STS 

DECLARE

  cur sys_refcursor;

BEGIN

  OPEN cur FOR

    SELECT VALUE(P)

    FROM TABLE(

       dbms_sqltune.select_workload_repository(begin_snap=>6450, end_snap=>6451,basic_filter=>'sql_id = ''f9948h4y2aa22''',attribute_list=>'ALL')

              ) p;

     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_f9948h4y2aa22', populate_cursor=>cur);

  CLOSE cur;

END;

7. Check the loaded Plan details.

SELECT

  first_load_time          ,

  executions as execs              ,

  parsing_schema_name      ,

  elapsed_time  / 1000000 as elapsed_time_secs  ,

  cpu_time / 1000000 as cpu_time_secs           ,

  buffer_gets              ,

  disk_reads               ,

  direct_writes            ,

  rows_processed           ,

  fetches                  ,

  optimizer_cost           ,

  sql_plan                ,

  plan_hash_value          ,

  sql_id                   ,

  sql_text

   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22')

             );

8. In final Stage Load SQL Plan from STS (AWR History) using plan Hash Value.

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    sqlset_name => 'STS_f9948h4y2aa22', 

    basic_filter=>'plan_hash_value = ''3166698470'''

    );

END;

9. Check your loaded plan from baseline.

SELECT * FROM dba_sql_plan_baselines ;