Loading SQL Plans into SPM using AWR

Often you may need to load SQL Plans into SPM but isn’t possibile because query is out of cache.
There is a simple procedure for loading SQL plans into SPM using AWR data.
The first step, if not exists, is to create a SQL Tuning Set (STS).
exec dbms_sqltune.create_sqlset(sqlset_name => ‘1ffbnq9wwkhtz_sqlset_test’,
                                description => ‘query test’);
Name and description are customizable. In sqlset_name I using even sql_id because in this way I can remember the query.
Next step is to take initial and final snapshot id. I prefer generate AWR report to make sure that the query was running. Obviously I using:
SQL> @?/rdbms/admin/awrrpt.sql
for generate AWR.
For example my snap_id are 5868 and 5869.
Now I can load query in STS.
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(5868, 5869,
                               ‘sql_id=’||CHR(39)||’1ffbnq9wwkhtz’||CHR(39)||”,
                               NULL, NULL, NULL, NULL, NULL, NULL, ‘ALL’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(‘1ffbnq9wwkhtz_sqlset_test’, baseline_ref_cur);
end;
/
 In this way I load every execution plan in STS. If I want load only one execution plan (for example the better plan) and I know plan hash value, I can use this:
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(5868, 5869,          
                            ‘sql_id=’||CHR(39)||’1ffbnq9wwkhtz’||CHR(39)||’ and
                            plan_hash_value=1705166898′, NULL, NULL, NULL, NULL,
                            NULL, NULL, ‘ALL’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(‘1ffbnq9wwkhtz_sqlset_test’, baseline_ref_cur);
end;
/
 Now I check if STS is corrected crated:
SELECT NAME,OWNER, CREATED, STATEMENT_COUNT
FROM DBA_SQLSET
WHERE name=’1ffjfq6wychsz_sqlset_test’;
 Now I check if execution plan that I want load is correct:
 
SELECT *
FROM table(dbms_xplan.display_sqlset(‘1ffbnq9wwkhtz_sqlset_test’,’1ffbnq9wwkhtz’));
 And I check how many baseline already exist:
SELECT count(*)
FROM dba_sql_plan_baselines;
 Now I can load SPM from STS:
 set serveroutput on
declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => ‘1ffbnq9wwkhtz_sqlset_test’,
sqlset_owner => ‘SYS’,
fixed => ‘YES’,
enabled => ‘YES’);
DBMS_OUTPUT.PUT_line(my_int);
end;
/
With FIXED=’YES’ parameter I can fix an exact execution plan. If I want load some plan but not all there is basic_filter parameter (for example:  basic_filter => ‘sql_id=”1ffbnq9wwkhtz” and plan_hash_value =1705166898′).
Finally I check from dba_sql_pan_baseline if loading is ok:
SELECT SQL_HANDLE, PLAN_NAME, SQL_TEXT, ORIGIN, ENABLED, ACCEPTED,
       FIXED, MODULE, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES;