Load SQL Plan from Cusrsor Cache to SQL Plan Baseline

Follow the below steps, if you want to load a specific plan hash value of a SQL from Cursor Cache to SQL Plan Baseline.

You can check in V$SQLAREA view to confirm that your plan hash value is available in the curcor cache.

SELECT sql_id, plan_hash_value, executions, elapsed_time from v$sqlarea where sql_id = '&sql_id';


Step 1 : Load the plan hash value from Cursor Cache to Plan Baseline.

SET SERVEROUTPUT ON

declare
my_int pls_integer;
begin
my_int := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'ghdgm4dampwd5', plan_hash_value => 1831906407, fixed => 'NO', enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/

2. Purge the SQL from the cursor Cache, so that the optimizer will pick the new plan from the baseline.

select 'DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from V$SQLAREA where SQL_ID = 'ghdgm4dampwd5';

exec DBMS_SHARED_POOL.PURGE ('000000051A0961B0, 1997643887', 'C');
------------------------------------------------------------------------------------------------

If the plan is not available in the cursor cache, but it is available in the AWR repository, (Which you can confirm from the view DBA_HIST_SQLSTAT), then you can follow the below step to load the plan hash value from AWR repository to SQL Plan Baseline.

** Run all the steps from SYS Login.

1. Create an empty Tuning Set
exec dbms_sqltune.create_sqlset(sqlset_name => 'ghdgm4dampwd5_1_sqlset_test',description => 'ghdgm4dampwd5_1 sqlset descriptions');

2. Load the SQL Plan hash value from AWR to SQL Tuning set.

set serveroutput ON
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(13371, 13372,'sql_id='||CHR(39)||'ghdgm4dampwd5'||CHR(39)||' and plan_hash_value=2495666596',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('ghdgm4dampwd5_1_sqlset_test', baseline_ref_cur);
end;
/

3. Load the plan hash value from the SQL Tuning Set to SQL Plan Baseline

set serveroutput ON
declare
my_int pls_integer;
begin
my_int := DBMS_SPM.load_plans_from_sqlset(sqlset_name => 'ghdgm4dampwd5_1_sqlset_test', sqlset_owner => 'SYS', fixed => 'NO', enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/

** Since the plan is not available in the cursor cache you do not need to purge the SQL detail from the Cache.