Capture current 10g execution plans before upgrading to 11g

We are planning upgrading our major databases from 10g to 11g. It is desirable to save the current execution plans before upgrading.

Oracle offers a SQL Tuning Set (STS) feature which can be used to achieve our goal. First of all we can create a SQL set:

-- create sql set --
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (
 sqlset_name => 'SPM_STS',
 description => '10g plans');
END;
/

Secondly, we can populate an STS from the workload repository, another STS or from the cursor cache. Below statement is to capture the SQL statements and their execution plans from the cursor cache:

DECLARE
  stscur dbms_sqltune.sqlset_cursor;
BEGIN
   OPEN stscur FOR
   SELECT VALUE(P)
     FROM TABLE(dbms_sqltune.select_cursor_cache( 'parsing_schema_name <> ''SYS''',
      null, null, null, null, 1, null, 'ALL')) P;
-- populate the sqlset
   dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS', populate_cursor => stscur);
END;
/

We can query the DBA_SQLSET to see how many statements captured in the STS:

-- query dba_sqlset 
   SQL> select * from dba_sqlset;
  
 ID NAME            OWNER                DESCRIPTION          CREATED   LAST_MODI STATEMENT_COUNT
---------- --------------- -------------------- -------------------- --------- --------- ---------------
  1 SPM_STS         OPS$VZZZZZZ          10g plans            23-MAR-12 23-MAR-12           10688

Given a SQL_ID, we can otaine the execution plan captured in the STS, for an example:

select * from table (DBMS_XPLAN.DISPLAY_SQLSET(
      'SPM_STS'       -- sqlset_name       
    , '3ubgcaay2xjd8' -- sql_id           
    ,  null           -- plan_hash_value  
    , 'ALLSTATS'       -- format  'TYPICAL', 'ALLSTATS' etc 
    , null            -- sqlset_owner  
 )) ;  

  
 PLAN_TABLE_OUTPUT
 ---------------------------------------------------------------------------------------------------
 SQL Tuning Set Name: SPM_STS
 SQL Tuning Set Owner: OPS$VZZZZZZ
 SQL_ID: 3ubgcaay2xjd8
 SQL Text: SELECT DISTINCT a.BO_STATUS_CODE   FROM            BO_MAPPING a,   CPF_PRODUCT b
    WHERE           a.BO_ID=b.BO_ID AND   a.VOL_PARENT_BO_ID=:1 AND   b.PACKAGE_ID=:2 AND
    a.NODE_TYPE_ID=800
 --------------------------------------------------------------------------------
  
 Plan hash value: 2194803941
  
 -----------------------------------------------------------------
 | Id  | Operation                     | Name           | E-Rows |
 -----------------------------------------------------------------
 |   1 |  HASH UNIQUE                  |                |      1 |
 |   2 |   NESTED LOOPS                |                |      1 |
 |   3 |    TABLE ACCESS BY INDEX ROWID| BO_MAPPING     |      1 |
 |   4 |     INDEX RANGE SCAN          | BO_MAPPING_FK5 |     13 |
 |   5 |    TABLE ACCESS BY INDEX ROWID| CPF_PRODUCT    |      1 |
 |   6 |     INDEX UNIQUE SCAN         | CPF_PRODUCT_PK |      1 |
 -----------------------------------------------------------------
  
 Note
 -----
    - Warning: basic plan statistics not available. These are only collected when:
        * hint 'gather_plan_statistics' is used for the statement or
        * parameter 'statistics_level' is set to 'ALL', at session or system level
  
  

We can also obtain the execution statistics of a SQL, such as buffer gets per execution

SELECT sql_id, plan_hash_value, executions, buffer_gets/executions from
    table(dbms_sqltune.select_sqlset(
    'SPM_STS'
 , 'sql_id = ''3ubgcaay2xjd8'''
 ));  
  


 SQL_ID        PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS/EXECUTIONS
 ------------- --------------- ---------- ----------------------
 3ubgcaay2xjd8      2194803941   16075233              79.076609
  

Finally, here is an example to obtain SQL_IDs given the partail sql text:

SELECT sql_id, sql_text, plan_hash_value, executions, buffer_gets/executions from
    table(dbms_sqltune.select_sqlset(
    'SPM_STS'
 , 'sql_text like ''select%MASTER_SERVICE%'''
 ));  

References


http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm
http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php


DBMS_SQLTUNE.SELECT_SQLSET (
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
ranking_measure1 IN VARCHAR2 := NULL,
ranking_measure2 IN VARCHAR2 := NULL,
ranking_measure3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := 1,
result_limit IN NUMBER := NULL)
attribute_list IN VARCHAR2 := NULL,
plan_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED;


DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;