Month: October 2017

Loading Execution Plan for AWR

by

Create a new tuning set

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => '0uff0s72zg781_tuning_task10');
END;
/

Load the execution plan into the tuning set

DECLARE
  cur sys_refcursor;
BEGIN
open cur for
 select value(p) from table(dbms_sqltune.select_workload_repository(
      begin_snap => 77002,
      end_snap => 77003,
      basic_filter => 'sql_id IN (''0uff0s72zg781'') AND plan_hash_value = ''2294492662''')) p;
    dbms_sqltune.load_sqlset('0uff0s72zg781_tuning_task10', cur);
  close cur;
END;
/

Apply the plan to the statement

DECLARE
 my_plans PLS_INTEGER;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
   sqlset_name => '0uff0s72zg781_tuning_task10',
    fixed => 'YES');
END;
/