SQL - force good plan

run the report find the hash value

SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL planhash.html
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
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 = 'gd1by1cgjj853'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;
SPOOL OFF
SET MARKUP HTML OFF

SET ECHO ON

Step -1- Create SQL Tuning Set

BEGIN  
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_TEST',description => 'SQL Tuning Set for loading plan into SQL Plan Baseline'); 
END; 
/

Step -2- Load the STS from AWR snap id 

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


SQL>


Step -3


SELECT * FROM dba_sql_plan_baselines ;

Step -4  - force hash value to use the SQL 

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

Step -5


-- List out SQL Tuning Set contents to check we got what we wanted
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_TEST'));

Drop the STS

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'STS_TEST');
END;


   
   
To display the contents of an STS:

SQL>

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
       ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'STS_TEST' ) );


SELECT name, owner FROM dba_sqlset WHERE name='STS_TEST'


You can see the SQL statements loaded in the SQL Tuning Set once it's loaded, by using the following query:

SELECT SQL_TEXT FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STS_TMSUAT') );



SQL> select SQL_TEXT from DBA_SQLSET_STATEMENTS where SQLSET_NAME='STS_TEST';

no rows selected

SQL>




select * from table(dbms_xplan.display_sqlset('STS_TEST','gd1by1cgjj853'));



SQL> select instance_name from v$instance;




SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_TEST',description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/  2    3    4

PL/SQL procedure successfully completed.

SQL> DECLARE  cur sys_refcursor;
  2  BEGIN
  3  OPEN cur FOR
  4       SELECT VALUE(P)
  5       FROM TABLE(dbms_sqltune.select_workload_repository(begin_snap=>12739, end_snap=>12740,basic_filter=>'sql_id =''gd1by1cgjj853''',attribute_list=>'ALL')) p;
  6        DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_TEST', populate_cursor=>cur);
  7   CLOSE cur;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> select name from v$database;



SQL> DECLARE
  2  my_plans pls_integer;
  3  BEGIN
  4    my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
  5    sqlset_name => 'STS_TEST',
  6     basic_filter=>'plan_hash_value = ''494989362''');
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>


SQL> select SQL_TEXT from DBA_SQLSET_STATEMENTS where SQLSET_NAME='STS_TEST';

FYI

Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (Doc ID 1955195.1)

SQL> START coe_xfr_sql_profile.sql <sql_id> or <plan hash value for good plan>


Unzip sqlt.zip and navigate to the sqlt directory
Navigate to the subdirectory utl
Find the SQL_ID and the Plan Hash Value (PHV) for the plan you want to base the profile on. You can get the SQL_ID from SQLT in the "SQL Identification" section and the PHV from the "Execution Plans" section: