How to manually load an execution plan for an SQL statement into the SMB

Facts:
* Your customer has a query against a couple of tables with very volatile contents
* The problem is simply solved by analyzing the tables involved using dbms_stats.gather_table_stats. After that, the query runs with acceptable performance

Solution:
* Add the SQLs execution plan to the SMB as a FIXED statement, so the optimizer will use it next time

Here is how:

1. Make sure the parameter optimizer_use_sql_plan_baselines is set to TRUE.
This is default for 11g.

2. Find the sql_id for the query you want to mark as fixed.
Easiest is to use Enterprise Manager.

3. When found, make a note of the sql_id. Then create an STS as follows:

BEGIN
   DBMS_SQLTUNE.create_sqlset
   (sqlset_name  => 'ACCEPTED_NESTED_LOOP_QUERY',
    description  => 'Freeze of execution plan requested by customer.',
    sqlset_owner => 'DB_ADMIN'
    );
END;
/

4. Add the sql_id found in step 1 to your STS:

DECLARE
   sqlset_cur dbms_sqltune.sqlset_cursor;
   bf VARCHAR2(82);
BEGIN 
  bf := q'#UPPER(PARSING_SCHEMA_NAME) = 'SCOTT' AND UPPER(SQL_ID) = 'BHAANST01X9YB' #';
  OPEN sqlset_cur FOR 
  SELECT VALUE(P) FROM TABLE( 
   dbms_sqltune.select_cursor_cache(bf, NULL, NULL, NULL, NULL, 1, NULL, 'TYPICAL')) P;
   dbms_sqltune.load_sqlset( 
     sqlset_name     => 'ACCEPTED_NESTED_LOOP_QUERY',
     populate_cursor => sqlset_cur, 
     load_option     => 'MERGE', 
     update_option   => 'ACCUMULATE', 
     sqlset_owner    => 'DB_ADMIN');
END;
/

5. Load the plan into the SQL Management Base (SMB). Put the following into a file called manual_load.sql:

declare
   l_num_plans PLS_INTEGER;
begin
 l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET
  (
  'ACCEPTED_NESTED_LOOP_QUERY',
  'DB_ADMIN'
  ,NULL,
  'YES',
  'YES',
  1000
  );
 DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/

Result:

$ sqlplus /nolog

SQL> connect DB_ADMIN
Enter password:
Connected.
SQL> set serveroutput on
SQL> @manual_load.sql
Number of plans loaded: 1

PL/SQL procedure successfully completed.

6. Verify that the plan is indeed saved:

SELECT sql_text,
       plan_name,
       sql_handle,
       creator,
       origin,
       created, 
       last_executed,
       enabled,
       accepted,
       fixed,
       autopurge,
       module
FROM   DBA_SQL_PLAN_BASELINES
WHERE  SQL_TEXT like '%your sql text%';

SQL_TEXT	PLAN_NAME	SQL_HANDLE	CREATOR	ORIGIN	CREATED	LAST_EXECUTED	ENABLED	ACCEPTED	FIXED	AUTOPURGE	MODULE
your statement will be visible here	SYS_SQL_PLAN_50701d3df0871b58	SYS_SQL_c95f410d50701d3d	DBA_ADMIN	MANUAL-LOAD	06.11.2013 15:36:20.000000		YES	YES	YES	YES	TOAD background query session

To view the execution plan that is now fixed in the SMB, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:

sqlplus / as sysdba
SQL>
set long 10000
set lines 200
set pages 300
SELECT *
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_50701d3df0871b58'));


Further reading on the topic:
Oracle-base.com: SQL Plan Management in Oracle Database 11g Release 1
Oracle Database Performing tuning guide
Adaptive cursors and SQL Plan Managment by Arup Nanda