HOW TO FORCE SQL ID TO USE A SPECIFIC HASH

HOW TO FORCE SQL ID TO USE A SPECIFIC HASH 28 28America/Sao_Paulo January 28America/Sao_Paulo 2019 Shows current plan select plan_table_output from table(dbms_xplan.display_cursor('',null,'ADVANCED')); Load SQL ID from cursor cache DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ''); END; / Load SQL ID from AWR BEGIN DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ''); END; / Verify the execution Plan of a SQL_ID in the STS select * from table(dbms_xplan.display_sqlset('','')); Load to SPM Baseline from SQL Tuning Set of Specific Hash Value DECLARE cur sys_refcursor; BEGIN open cur for select value(p) from table(dbms_sqltune.select_workload_repository( begin_snap => 12100, end_snap => 12301, basic_filter => 'sql_id IN ('''') AND plan_hash_value = ''''')) p; dbms_sqltune.load_sqlset('', cur); close cur; END; / Load all plans from SQL Tuning Set DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => ''); END; / Force a SQL statement to use specific hash SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => '', plan_name => '', attribute_name => 'fixed', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; / Options: enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted. fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans. autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time. plan_name : Used to amend the SQL plan name, up to a maximum of 30 character. description : Used to amend the SQL plan description, up to a maximum of 30 character. Shows execution plan of sql plan select t.* from table(dbms_xplan.display_sql_plan_baseline('',format => 'basic')) t; Show all sql baselines SELECT created, sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines ORDER BY 1 DESC; https://oraclesurvivaldiary.wordpress.com/2019/01/28/how-to-force-sql-id-to-use-a-specific-hash/