SQL Plan Baselines

Loading SQL Plans into SPM using AWR

Through this query, we can know the execution plans for specific sql_id.
SELECT plan_table_output FROM TABLE (Dbms_Xplan.display_awr('49xkga3u105ab'));

Create a SQL Tuning Set (STS)
EXEC Dbms_Sqltune.create_sqlset(sqlset_name => '49xkga3u105ab_sqlset', 
description => 'QUERY TEST');

Take initial and final snapshot id 

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Load every execution plan 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(8055, 8077,
                               'sql_id='||CHR(39)||'49xkga3u105ab'||CHR(39)||'',
                               NULL, NULL, NULL, NULL, NULL, NULL, 'ALL')) p;
Dbms_Sqltune.LOAD_SQLSET('49xkga3u105ab_sqlset', baseline_ref_cur);
END;
/
Load single execution plan 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)||' AND
                            plan_hash_value=1705166898', NULL, NULL, NULL, NULL,
                            NULL, NULL, 'ALL')) p;
Dbms_Sqltune.LOAD_SQLSET(‘1ffbnq9wwkhtz_sqlset_test’, baseline_ref_cur);
END;
/

Verify STS created is right
SELECT NAME,OWNER, CREATED, STATEMENT_COUNT
FROM DBA_SQLSET
WHERE NAME='49xkga3u105ab_sqlset';

Verify the execution plan right to load
SELECT * FROM TABLE(Dbms_Xplan.display_sqlset('49xkga3u105ab_sqlset','49xkga3u105ab'));

Load STS into SPM (SQL Plan Management)
DECLARE
my_int PLS_INTEGER;
BEGIN
my_int := Dbms_Spm.load_plans_from_sqlset (
sqlset_name => '49xkga3u105ab_sqlset',
sqlset_owner => 'SYS',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_LINE(my_int);
END;
/

Verify the execution plan is load into SQL Plan Baselines
SELECT SQL_HANDLE, PLAN_NAME, SQL_TEXT, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES;

Actually, you can disable the baselines plan anytime with query.
DECLARE
   l_plans PLS_INTEGER;
BEGIN
   l_plans := Dbms_Spm.alter_sql_plan_baseline (
      sql_handle         => 'SQL_c3ff4afc21522d6e',
      plan_name         => 'SQL_PLAN_c7zuazhhp4bbf90decb9c',
      attribute_name   => 'enabled',
      attribute_value  => 'NO'
   );
END;