Load SQL Plans into SQL Plan Management (SPM) 
from the Automatic Workload Repository (AWR) 

Managing performance can be tricky thing specially when one is dealing with SQL Performance. There are many varied reasons which could be result in performance degradation. Usually, when there is change in profile of data or introduction of new data structures or upgrades. This can be achieved by plan stability and it is widely used feature of Oracle DB. The Baseline is key to achieving plan stability. 

When one has performance issues with SQL, one can achieve the stable plan either from history or from cached statement, known to have good plan. 
This note provide steps for loading a SQL plan into SQL plan baseline from AWR.

As a starting point one has to be clear about the culprit causing performance bottleneck. 
you need to gather info about sql_id and other related info which can be used to achieve desired result. 

1. As a first step one needs to create SQL Tuning Set. You can see it as a kind of container object that holds the statements and plan for your statement.                                                                                                                                                
exec dbms_sqltune.create_sqlset(sqlset_name => '76cmg604xv5gm_sqlset_test',description => 'sqlset test HS');

PL/SQL procedure successfully completed.

2. Next step is, since we want to load plan from AWR repository, is to identify the range of snap id which contains the sql statement and plan, we are after. 

One can use following two views to get that info. 
To find snapshot related info - dba_hist_snapshot 
To Identify the snap_id in which the sql belongs  - dba_hist_sqlstat 

Use following code to load data from AWR to SQL Tuning Set. 

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(27437,27440,'sql_id='||CHR(39)||'76cmg604xv5gm'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('76cmg604xv5gm_sqlset_test', baseline_ref_cur);
end;
/

PL/SQL procedure successfully completed.

To load all the sqls captured in AWR snapshot use “NULL” in basic_filter.

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('all_sqlset_test', baseline_ref_cur);
end;
/

Now check if the SQL Set is populated with data or not...

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


NAME                           OWNER                          CREATED  
------------------------------ ------------------------------ ---------
STATEMENT_COUNT
---------------
76cmg604xv5gm_sqlset_test      SYS                            03-FEB-15
              1
1 row selected.

To check statements from SQL Set - 

select sql_id, substr(sql_text,1, 15) text from dba_sqlset_statements
where sqlset_name = '76cmg604xv5gm_sqlset_test';

To Check the contents of the SQL Set -                                                                              
select * from table(dbms_xplan.display_sqlset('76cmg604xv5gm_sqlset_test','&sql_id'));

Enter sql_id : 76cmg604xv5gm       

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Error: cannot fetch explain plan for the specified SQL tuning set and sql_id    
1 row selected.

If you see at the moment, the SQL Plan Baselines will be empty. 

select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         0
1 row selected.

3. Next, load SQL execution plans from SQLSET into SQL Baseline 

Use following code to load sql plan from STS to baselines. 

declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (sqlset_name => '76cmg604xv5gm_sqlset_test', sqlset_owner => 'SYS',fixed => 'NO', enabled => 'YES');
end;
/

PL/SQL procedure successfully completed.

You can also set basic filter as follows

basic_filter => 'sql_id="76cmg604xv5gm"'

One done, check the SQL Plan Baselines, and you will see that it has baselines created for that specific SQL. 

SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM   DBA_SQL_PLAN_BASELINES;

Hope this will help you in trouble shooting your severe performance hangs.