Create Baseline to change execution plan hash value

Issue:

When a query is having a new plan which is worst and you found by SQL tuning Advisor there is another plan which is having better execution time.














       

There is a better plan in AWR as in above Fig.

Steps to change plan hash value.

Step1:  Create SQL tuning set with SQL_ID attached.

Drop if same name exists before
 exec dbms_sqltune.DROP_SQLSET('sqlid_1vktnmmbp6cz3');

Create new one as follows.


 exec DBMS_SQLTUNE.CREATE_SQLSET('sqlid_1vktnmmbp6cz3');


Step2: Load plan  you want for the SQL ID , replace sql_id and plan hash value you want in following blocks.

Following will extract the plan from awr.

declare
 baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 begin
 open baseline_ref_cursor for
 select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1, 128,'sql_id='||CHR(39)||'1vktnmmbp6cz3'||CHR(39)||' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
 end;
 /
 above when executed will throw out correct snapshot ids as follows

 08:35:15 SQL> declare
 08:35:34   2   baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 08:35:34   3   begin
 08:35:34   4   open baseline_ref_cursor for
 08:35:34   5   select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1, 128,'sql_id='||CHR(39)||'1vktnmmbp6cz3'||CHR(39)||' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 08:35:34   6   DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
 08:35:34   7   end;
 08:35:34   8   /
 declare
 *
 ERROR at line 1:
 ORA-13768: Snapshot ID must be between 83431 and 93052.
 ORA-06512: at "SYS.DBMS_SQLTUNE", line 4715
 ORA-06512: at line 6

Now replace start and end snap ids as follows

declare
 baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 begin
 open baseline_ref_cursor for
 select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(83431, 93052,'sql_id='||CHR(39)||'1vktnmmbp6cz3'||CHR(39)||' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
 end;
 /

 


 ***************** OR ***********************

Following to get plan from cursor cache.


 declare
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
  begin
  open baseline_ref_cursor for
  select value(p) from table(dbms_sqltune.select_cursor_cache('sql_id=''1vktnmmbp6cz3'' and plan_hash_value=487911821',NULL,NULL,NULL,NULL,1,NULL,'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET('sqlid_1vktnmmbp6cz3', baseline_ref_cursor);
  end;
 /

Step3: Queries to check plan.

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='sqlid_1vktnmmbp6cz3';

 select sql_id, substr(sql_text,1, 15) text
 from dba_sqlset_statements
 where sqlset_name = 'sqlid_1vktnmmbp6cz3'
 order by sql_id;

 SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('sqlid_1vktnmmbp6cz3','1vktnmmbp6cz3'));

select *  from dba_sql_plan_baselines;

 Step4: Create baseline and fix it.


 set serveroutput on
 declare
 my_integer pls_integer;
 begin
 my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => 'sqlid_1vktnmmbp6cz3',
                                               sqlset_owner => 'SYSTEM',
                                               fixed => 'YES',
                                               enabled => 'YES');
                                               DBMS_OUTPUT.PUT_line(my_integer);
 end;
 /