/*
|| Oracle 11g SQL Plan Management Listing 2
||
|| Demonstrates Oracle 11g SQL Plan Management (SPM) advanced techniques,
|| including:
|| - Capturing SQL Plan Baselines via manual methods with DBMS_SPM
|| - Transferring captured SQL Plan Baselines between Oracle 10g and 11g databases
||   to "pre-seed" the SQL Management Baseline (SMB) with the most optimal execution
||   plans before an upgrade of an Oracle 10g database to Oracle 11g
|| - Transferring captured SQL Plan Baselines between test and production environments
||   to "pre-seed" the SQL Management Baseline (SMB) with the most typical execution 
||   plans prior to deployment of a brand-new application
|| - Dropping existing SQL Plan Baselines from the SMB via manual methods
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Plan Management features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/

/*  
|| Listing 2.1:
|| Create and prepare to populate a SQL Tuning Set (STS)
|| for selected SQL statements. Note that this STS will capture
|| all SQL statements which are executed by the LDGN user account
|| within a 5-minute period, and Oracle will check every 5 seconds
|| for any new statements
*/

BEGIN
    DBMS_SQLTUNE.DROP_SQLSET(
        sqlset_name => 'STS_SPM_200'
    );
END;

@SPM_2_1.sql;
/
BEGIN
    DBMS_SQLTUNE.CREATE_SQLSET(
        sqlset_name => 'STS_SPM_200'
    );
    DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(                         
         sqlset_name => 'STS_SPM_200'
        ,basic_filter=> q'#sql_text LIKE '%SPM_2_1%' AND parsing_schema_name = 'LDGN'#'
        ,time_limit  => 300
        ,repeat_interval => 5
    ); 
END;
/

/*  
|| Listing 2.2:
|| "Packing up" and exporting the Oracle 10gR2 SQL Tuning Set prior to
|| its transport to Oracle 11g
*/

-----
-- Create a staging table to hold the SQL Tuning Set statements just created,
-- and then "pack up" (i.e. populate) the staging table
-----
DROP TABLE ldgn.sts_staging PURGE;
BEGIN
    DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
         table_name => 'STS_STAGING'
        ,schema_name => 'LDGN'
        ,tablespace_name => 'USERS'
    );
    DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
         sqlset_name => 'STS_SPM_200'
        ,sqlset_owner => 'SYS'
        ,staging_table_name => 'STS_STAGING'
        ,staging_schema_owner => 'LDGN'
    );
END;
/

-----
-- Invoke DataPump Export to export the table that contains the staged 
-- SQL Tuning Set statements
-----
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/log/*.log
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/log/*.dmp
expdp system/oracle PARFILE=DumpStagingTable.dpectl

#####
# Contents of DumpStagingTable.dpectl parameter file:
#####
JOB_NAME=DumpStagingTable
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=LDGN_STS_Staging.dmp
SCHEMAS=LDGN

>>> Results:

Export: Release 10.2.0.1.0 - Production on Monday, 18 February, 2008 19:03:57
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."DUMPSTAGINGTABLE":  system/******** PARFILE=DumpStagingTable.dpectl 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "LDGN"."STS_STAGING"                        22.67 KB       8 rows
. . exported "LDGN"."STS_STAGING_CPLANS"                 35.35 KB      25 rows
. . exported "LDGN"."STS_STAGING_CBINDS"                 9.476 KB       0 rows
. . exported "LDGN"."PLAN_TABLE"                             0 KB       0 rows
Master table "SYSTEM"."DUMPSTAGINGTABLE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.DUMPSTAGINGTABLE is:
  /u01/app/oracle/product/10.2.0/db_1/rdbms/log/LDGN_STS_Staging.dmp
Job "SYSTEM"."DUMPSTAGINGTABLE" successfully completed at 19:05:21

/* 
|| Listing 2.3:
|| Transporting, importing, and "unpacking" the staged Oracle 10gR2 SQL Tuning
|| Set on the target Oracle 11g database
*/

-----
-- Invoke DataPump Import to import the table that contains the staged 
-- SQL Tuning Set statements. Note that the default action of SKIPping
-- a table if it already exists has been overridden by supplying a value
-- of REPLACE for parameter TABLE_EXISTS_ACTION.
-----
impdp system/oracle PARFILE=LoadStagingTable.dpictl

#####
# Contents of LoadStagingTable.dpictl parameter file:
#####
JOB_NAME=LoadStagingTable
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=LDGN_STS_Staging.dmp
TABLE_EXISTS_ACTION=REPLACE

>>> Results of DataPump Import operation:

Import: Release 11.1.0.6.0 - Production on Monday, 18 February, 2008 19:09:29
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."LOADSTAGINGTABLE" successfully loaded/unloaded
Starting "SYSTEM"."LOADSTAGINGTABLE":  system/******** PARFILE=LoadStagingTable.dpictl 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LDGN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LDGN"."STS_STAGING"                        22.67 KB       8 rows
. . imported "LDGN"."STS_STAGING_CPLANS"                 35.35 KB      25 rows
. . imported "LDGN"."STS_STAGING_CBINDS"                 9.476 KB       0 rows
. . imported "LDGN"."PLAN_TABLE"                             0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "SYSTEM"."LOADSTAGINGTABLE" completed with 1 error(s) at 19:11:07

-----
-- Accept the SQL Tuning Set statements from the imported staging table
-- into the Oracle 11gR1 database
-----
BEGIN
    DBMS_SQLTUNE.DROP_SQLSET(
        sqlset_name => 'STS_SPM_200'
    );
END;
/
BEGIN
    DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
         sqlset_name => 'STS_SPM_200'
        ,sqlset_owner => 'SYS'
        ,replace => TRUE
        ,staging_table_name => 'STS_STAGING'
        ,staging_schema_owner => 'LDGN'
    );
END;
/

----- 
-- Listing 2.4:
-- Prove that the SQL Plan Baselines loaded into the SMB via manual methods are
-- actually being utilized by executing EXPLAIN PLANs against each statement from the
-- target Oracle 11g database
-----

SQL> EXPLAIN PLAN FOR
  2  SELECT /*SPM_2_1.1*/
  3       CTY.country_total_id
  4      ,PR.promo_total_id
  5      ,COUNT(S.amount_sold)
  6      ,SUM(S.amount_sold)
  7      ,SUM(S.quantity_sold)
  8    FROM
  9       sh.sales S
 10      ,sh.customers C
 11      ,sh.countries CTY
 12      ,sh.promotions PR
 13   WHERE S.cust_id = C.cust_id
 14     AND C.country_id = CTY.country_id
 15     AND S.promo_id = PR.promo_id
 16   GROUP BY
 17       CTY.country_total_id
 18      ,PR.promo_total_id
 19  ;

Explained.

SQL> SELECT *
  2    FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
  3  ;

Plan hash value: 491136032                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------------------------                                                                                                                                                  
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------                                                                                                                                                  
|   0 | SELECT STATEMENT        |            |     1 |    44 |       |  2325   (5)| 00:00:28 |       |       |                                                                                                                                                  
|   1 |  HASH GROUP BY          |            |     1 |    44 |       |  2325   (5)| 00:00:28 |       |       |                                                                                                                                                  
|*  2 |   HASH JOIN             |            |   918K|    38M|       |  2270   (3)| 00:00:28 |       |       |                                                                                                                                                  
|   3 |    TABLE ACCESS FULL    | PROMOTIONS |   503 |  3521 |       |    17   (0)| 00:00:01 |       |       |                                                                                                                                                  
|*  4 |    HASH JOIN            |            |   918K|    32M|       |  2246   (2)| 00:00:27 |       |       |                                                                                                                                                  
|   5 |     TABLE ACCESS FULL   | COUNTRIES  |    23 |   230 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                  
|*  6 |     HASH JOIN           |            |   918K|    23M|  1200K|  2236   (2)| 00:00:27 |       |       |                                                                                                                                                  
|   7 |      TABLE ACCESS FULL  | CUSTOMERS  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |                                                                                                                                                  
|   8 |      PARTITION RANGE ALL|            |   918K|    14M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                  
|   9 |       TABLE ACCESS FULL | SALES      |   918K|    14M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------                                                                                                                                                  
                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                
   2 - access("S"."PROMO_ID"="PR"."PROMO_ID")                                                                                                                                                                                                                   
   4 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")                                                                                                                                                                                                              
   6 - access("S"."CUST_ID"="C"."CUST_ID")                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                            
-----                                                                                                                                                                                                                                                           
   - SQL plan baseline "SYS_SQL_PLAN_587c0594825d2e47" used for this statement                                                                                                                                                                                  

27 rows selected.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT /*SPM_2_1.2*/
  3       CTY.country_id
  4      ,CTY.country_subregion_id
  5      ,CTY.country_region_id
  6      ,CTY.country_total_id
  7      ,PR.promo_total_id
  8      ,COUNT(S.amount_sold)
  9      ,SUM(S.amount_sold)
 10      ,SUM(S.quantity_sold)
 11    FROM
 12       sh.sales S
 13      ,sh.customers C
 14      ,sh.countries CTY
 15      ,sh.promotions PR
 16   WHERE S.cust_id = C.cust_id
 17     AND C.country_id = CTY.country_id
 18     AND S.promo_id = PR.promo_id
 19  GROUP BY
 20       CTY.country_id
 21      ,CTY.country_subregion_id
 22      ,CTY.country_region_id
 23      ,CTY.country_total_id
 24      ,PR.promo_total_id
 25  ;

Explained.

SQL> SELECT *
  2    FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
  3  ;

Plan hash value: 491136032                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------------------------                                                                                                                                                  
| Id  | Operation               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------                                                                                                                                                  
|   0 | SELECT STATEMENT        |            |   228 | 12312 |       |  2325   (5)| 00:00:28 |       |       |                                                                                                                                                  
|   1 |  HASH GROUP BY          |            |   228 | 12312 |       |  2325   (5)| 00:00:28 |       |       |                                                                                                                                                  
|*  2 |   HASH JOIN             |            |   918K|    47M|       |  2270   (3)| 00:00:28 |       |       |                                                                                                                                                  
|   3 |    TABLE ACCESS FULL    | PROMOTIONS |   503 |  3521 |       |    17   (0)| 00:00:01 |       |       |                                                                                                                                                  
|*  4 |    HASH JOIN            |            |   918K|    41M|       |  2246   (2)| 00:00:27 |       |       |                                                                                                                                                  
|   5 |     TABLE ACCESS FULL   | COUNTRIES  |    23 |   460 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                  
|*  6 |     HASH JOIN           |            |   918K|    23M|  1200K|  2236   (2)| 00:00:27 |       |       |                                                                                                                                                  
|   7 |      TABLE ACCESS FULL  | CUSTOMERS  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |                                                                                                                                                  
|   8 |      PARTITION RANGE ALL|            |   918K|    14M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                  
|   9 |       TABLE ACCESS FULL | SALES      |   918K|    14M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------                                                                                                                                                  
                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                
   2 - access("S"."PROMO_ID"="PR"."PROMO_ID")                                                                                                                                                                                                                   
   4 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")                                                                                                                                                                                                              
   6 - access("S"."CUST_ID"="C"."CUST_ID")                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                            
-----                                                                                                                                                                                                                                                           
   - SQL plan baseline "SYS_SQL_PLAN_54f64750825d2e47" used for this statement                                                                                                                                                                                  

27 rows selected.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT /*SPM_2_1.3*/
  3       CTY.country_total_id
  4      ,P.prod_id
  5      ,P.prod_subcategory_id
  6      ,P.prod_category_id
  7      ,P.prod_total_id
  8      ,CH.channel_id
  9      ,CH.channel_class_id
 10      ,CH.channel_total_id
 11      ,PR.promo_total_id
 12      ,COUNT(S.amount_sold)
 13      ,SUM(S.amount_sold)
 14      ,SUM(S.quantity_sold)
 15    FROM
 16       sh.sales S
 17      ,sh.customers C
 18      ,sh.countries CTY
 19      ,sh.products P
 20      ,sh.channels CH
 21      ,sh.promotions PR
 22   WHERE S.cust_id = C.cust_id
 23     AND C.country_id = CTY.country_id
 24     AND S.prod_id = P.prod_id
 25     AND S.channel_id = CH.channel_id
 26     AND S.promo_id = PR.promo_id
 27   GROUP BY
 28       CTY.country_total_id
 29      ,P.prod_id
 30      ,P.prod_subcategory_id
 31      ,P.prod_category_id
 32      ,P.prod_total_id
 33      ,CH.channel_id
 34      ,CH.channel_class_id
 35      ,CH.channel_total_id
 36      ,PR.promo_total_id
 37  ;

Explained.

SQL> SELECT *
  2    FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
  3  ;

Plan hash value: 2634317694                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
| Id  | Operation                 | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
|   0 | SELECT STATEMENT          |            |  5940 |   435K|       |  8393   (2)| 00:01:41 |       |       |                                                                                                                                                
|   1 |  HASH GROUP BY            |            |  5940 |   435K|    74M|  8393   (2)| 00:01:41 |       |       |                                                                                                                                                
|*  2 |   HASH JOIN               |            |   918K|    65M|       |  2593   (3)| 00:00:32 |       |       |                                                                                                                                                
|   3 |    TABLE ACCESS FULL      | PROMOTIONS |   503 |  3521 |       |    17   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  4 |    HASH JOIN              |            |   918K|    59M|       |  2569   (3)| 00:00:31 |       |       |                                                                                                                                                
|   5 |     TABLE ACCESS FULL     | PRODUCTS   |    72 |  1080 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  6 |     HASH JOIN             |            |   918K|    46M|       |  2560   (2)| 00:00:31 |       |       |                                                                                                                                                
|   7 |      TABLE ACCESS FULL    | COUNTRIES  |    23 |   230 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  8 |      HASH JOIN            |            |   918K|    37M|       |  2550   (2)| 00:00:31 |       |       |                                                                                                                                                
|   9 |       TABLE ACCESS FULL   | CHANNELS   |     5 |    45 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|* 10 |       HASH JOIN           |            |   918K|    29M|  1200K|  2541   (2)| 00:00:31 |       |       |                                                                                                                                                
|  11 |        TABLE ACCESS FULL  | CUSTOMERS  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |                                                                                                                                                
|  12 |        PARTITION RANGE ALL|            |   918K|    21M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                
|  13 |         TABLE ACCESS FULL | SALES      |   918K|    21M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                
   2 - access("S"."PROMO_ID"="PR"."PROMO_ID")                                                                                                                                                                                                                   
   4 - access("S"."PROD_ID"="P"."PROD_ID")                                                                                                                                                                                                                      
   6 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")                                                                                                                                                                                                              
   8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")                                                                                                                                                                                                               
  10 - access("S"."CUST_ID"="C"."CUST_ID")                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                            
-----                                                                                                                                                                                                                                                           
   - SQL plan baseline "SYS_SQL_PLAN_8ec1a5862d9d97db" used for this statement                                                                                                                                                                                  

33 rows selected.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT /*SPM_2_1.4*/
  3       CTY.country_total_id
  4      ,P.prod_category_id
  5      ,P.prod_total_id
  6      ,CH.channel_id
  7      ,CH.channel_class_id
  8      ,CH.channel_total_id
  9      ,PR.promo_total_id
 10      ,COUNT(S.amount_sold)
 11      ,SUM(S.amount_sold)
 12      ,SUM(S.quantity_sold)
 13    FROM
 14       sh.sales S
 15      ,sh.customers C
 16      ,sh.countries CTY
 17      ,sh.products P
 18      ,sh.channels CH
 19      ,sh.promotions PR
 20   WHERE S.cust_id = C.cust_id
 21     AND C.country_id = CTY.country_id
 22     AND S.prod_id = P.prod_id
 23     AND S.channel_id = CH.channel_id
 24     AND S.promo_id = PR.promo_id
 25   GROUP BY
 26       CTY.country_total_id
 27      ,P.prod_category_id
 28      ,P.prod_total_id
 29      ,CH.channel_id
 30      ,CH.channel_class_id
 31      ,CH.channel_total_id
 32      ,PR.promo_total_id
 33  ;

Explained.

SQL> SELECT *
  2    FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
  3  ;

Plan hash value: 2634317694                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
| Id  | Operation                 | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
|   0 | SELECT STATEMENT          |            |     8 |   568 |       |  2648   (5)| 00:00:32 |       |       |                                                                                                                                                
|   1 |  HASH GROUP BY            |            |     8 |   568 |       |  2648   (5)| 00:00:32 |       |       |                                                                                                                                                
|*  2 |   HASH JOIN               |            |   918K|    62M|       |  2593   (3)| 00:00:32 |       |       |                                                                                                                                                
|   3 |    TABLE ACCESS FULL      | PROMOTIONS |   503 |  3521 |       |    17   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  4 |    HASH JOIN              |            |   918K|    56M|       |  2569   (3)| 00:00:31 |       |       |                                                                                                                                                
|   5 |     TABLE ACCESS FULL     | PRODUCTS   |    72 |   792 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  6 |     HASH JOIN             |            |   918K|    46M|       |  2560   (2)| 00:00:31 |       |       |                                                                                                                                                
|   7 |      TABLE ACCESS FULL    | COUNTRIES  |    23 |   230 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  8 |      HASH JOIN            |            |   918K|    37M|       |  2550   (2)| 00:00:31 |       |       |                                                                                                                                                
|   9 |       TABLE ACCESS FULL   | CHANNELS   |     5 |    45 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|* 10 |       HASH JOIN           |            |   918K|    29M|  1200K|  2541   (2)| 00:00:31 |       |       |                                                                                                                                                
|  11 |        TABLE ACCESS FULL  | CUSTOMERS  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |                                                                                                                                                
|  12 |        PARTITION RANGE ALL|            |   918K|    21M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                
|  13 |         TABLE ACCESS FULL | SALES      |   918K|    21M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                
   2 - access("S"."PROMO_ID"="PR"."PROMO_ID")                                                                                                                                                                                                                   
   4 - access("S"."PROD_ID"="P"."PROD_ID")                                                                                                                                                                                                                      
   6 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")                                                                                                                                                                                                              
   8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")                                                                                                                                                                                                               
  10 - access("S"."CUST_ID"="C"."CUST_ID")                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                            
-----                                                                                                                                                                                                                                                           
   - SQL plan baseline "SYS_SQL_PLAN_96f761da2d9d97db" used for this statement                                                                                                                                                                                  

33 rows selected.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT /*SPM_2_1.5*/
  3       CTY.country_id
  4      ,CTY.country_subregion_id
  5      ,CTY.country_region_id
  6      ,CTY.country_total_id
  7      ,P.prod_id
  8      ,P.prod_subcategory_id
  9      ,P.prod_category_id
 10      ,P.prod_total_id
 11      ,CH.channel_id
 12      ,CH.channel_class_id
 13      ,CH.channel_total_id
 14      ,PR.promo_total_id
 15      ,COUNT(S.amount_sold)
 16      ,SUM(S.amount_sold)
 17      ,SUM(S.quantity_sold)
 18    FROM
 19       sh.sales S
 20      ,sh.customers C
 21      ,sh.countries CTY
 22      ,sh.products P
 23      ,sh.channels CH
 24      ,sh.promotions PR
 25   WHERE S.cust_id = C.cust_id
 26     AND C.country_id = CTY.country_id
 27     AND S.prod_id = P.prod_id
 28     AND S.channel_id = CH.channel_id
 29     AND S.promo_id = PR.promo_id
 30   GROUP BY
 31       CTY.country_id
 32      ,CTY.country_subregion_id
 33      ,CTY.country_region_id
 34      ,CTY.country_total_id
 35      ,P.prod_id
 36      ,P.prod_subcategory_id
 37      ,P.prod_category_id
 38      ,P.prod_total_id
 39      ,CH.channel_id
 40      ,CH.channel_class_id
 41      ,CH.channel_total_id
 42      ,PR.promo_total_id
 43  ;

Explained.

SQL> SELECT *
  2    FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'+NOTE'))
  3  ;

Plan hash value: 2634317694                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
| Id  | Operation                 | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
|   0 | SELECT STATEMENT          |            |   918K|    74M|       | 20707   (1)| 00:04:09 |       |       |                                                                                                                                                
|   1 |  HASH GROUP BY            |            |   918K|    74M|   168M| 20707   (1)| 00:04:09 |       |       |                                                                                                                                                
|*  2 |   HASH JOIN               |            |   918K|    74M|       |  2593   (3)| 00:00:32 |       |       |                                                                                                                                                
|   3 |    TABLE ACCESS FULL      | PROMOTIONS |   503 |  3521 |       |    17   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  4 |    HASH JOIN              |            |   918K|    68M|       |  2569   (3)| 00:00:31 |       |       |                                                                                                                                                
|   5 |     TABLE ACCESS FULL     | PRODUCTS   |    72 |  1080 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  6 |     HASH JOIN             |            |   918K|    55M|       |  2560   (2)| 00:00:31 |       |       |                                                                                                                                                
|   7 |      TABLE ACCESS FULL    | COUNTRIES  |    23 |   460 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|*  8 |      HASH JOIN            |            |   918K|    37M|       |  2550   (2)| 00:00:31 |       |       |                                                                                                                                                
|   9 |       TABLE ACCESS FULL   | CHANNELS   |     5 |    45 |       |     3   (0)| 00:00:01 |       |       |                                                                                                                                                
|* 10 |       HASH JOIN           |            |   918K|    29M|  1200K|  2541   (2)| 00:00:31 |       |       |                                                                                                                                                
|  11 |        TABLE ACCESS FULL  | CUSTOMERS  | 55500 |   541K|       |   406   (1)| 00:00:05 |       |       |                                                                                                                                                
|  12 |        PARTITION RANGE ALL|            |   918K|    21M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                
|  13 |         TABLE ACCESS FULL | SALES      |   918K|    21M|       |   498   (4)| 00:00:06 |     1 |    28 |                                                                                                                                                
----------------------------------------------------------------------------------------------------------------                                                                                                                                                
                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                             
---------------------------------------------------                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                
   2 - access("S"."PROMO_ID"="PR"."PROMO_ID")                                                                                                                                                                                                                   
   4 - access("S"."PROD_ID"="P"."PROD_ID")                                                                                                                                                                                                                      
   6 - access("C"."COUNTRY_ID"="CTY"."COUNTRY_ID")                                                                                                                                                                                                              
   8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")                                                                                                                                                                                                               
  10 - access("S"."CUST_ID"="C"."CUST_ID")                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                            
-----                                                                                                                                                                                                                                                           
   - SQL plan baseline "SYS_SQL_PLAN_816fca3a2d9d97db" used for this statement                                                                                                                                                                                  

33 rows selected.


/* 
|| Listing 2.5:
|| Prepare to deploy a simulated new application to the current Oracle 11g database.
|| Note that all SQL Plan Baselines that are currently tagged as SPM_2 statements
|| will first be purged from the SMB.
*/

-----
-- Clear all SQL Plan Baselines whose SQL text contains the tag "SPM_2"
-----
SET SERVEROUTPUT ON
VARIABLE nRtnCode NUMBER;
BEGIN
    :nRtnCode := 0;
    FOR r_SPMB IN (
	SELECT sql_handle, plan_name
          FROM dba_sql_plan_baselines
         WHERE sql_text LIKE '%SPM_2%'
	)
        LOOP
            :nRtnCode :=
    		DBMS_SPM.DROP_SQL_PLAN_BASELINE(r_SPMB.sql_handle, r_SPMB.plan_name);
		DBMS_OUTPUT.PUT_LINE('Drop of SPBs for Handle ' || r_SPMB.sql_handle
                || ' and Plan ' || r_SPMB.plan_name
                || ' completed: RC = ' || :nRtnCode);
		END LOOP;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Fatal error during cleanup of SQL Plan Baselines!');
    	ROLLBACK;
END;
/

-----
-- Run DDL commands to create the Sales Force Administration (SFA) schema
-- and all related objects
-----
@SFA_Setup.sql;

/* 
|| Listing 2.6:
|| Generate a SQL workload against the new application objects using six
|| queries tagged with a comment of SPM_2_2, and then capture the SQL Plan
|| Baselines into the SMB using DBMS_SPM.LOAD_PLANS_FROM CURSOR_CACHE
*/

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

@SPM_2_2.sql;

SET SERVEROUTPUT ON
VARIABLE plans_cached NUMBER;
BEGIN
    :plans_cached := 
        DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
             attribute_name => 'SQL_TEXT'
            ,attribute_value => '%SPM_2_2%'
            ,fixed => 'NO'
            ,enabled => 'YES'
        );
    DBMS_OUTPUT.PUT_LINE('>>> ' || :plans_cached || ' SQL statement(s) loaded from the cursor cache.');
END;
/

/* 
|| Listing 2.7:
|| Staging, Packing, and Exporting SQL Plan Baselines
*/

-----
-- Create a SQL Plan Baseline staging table and then "pack" those SQL 
-- Plan Baselines into a staging table
-----
BEGIN
    DBMS_SPM.CREATE_STGTAB_BASELINE (
         table_name => 'SPM_STAGING'
        ,table_owner => 'SFA'
        ,tablespace_name => 'EXAMPLE'
    );
END;
/

SET SERVEROUTPUT ON
VARIABLE plans_staged NUMBER;
BEGIN
    :plans_staged :=
        DBMS_SPM.PACK_STGTAB_BASELINE (
            table_name => 'SPM_STAGING'
           ,table_owner => 'SFA'
           ,creator => 'SYS'
        );
    DBMS_OUTPUT.PUT_LINE('Total SQL Plan Baselines Staged: ' || :plans_staged);
END;
/

-----
-- Export SPM staging table via DataPump Export
-----
rm -f /u01/app/oracle/admin/orcl/dpdump/*.log
rm -f /u01/app/oracle/admin/orcl/dpdump/*.dmp
expdp system/oracle PARFILE=DumpStagedSPMs.dpectl

#####
# Contents of DumpStagedSPMs.dpectl parameter file:
#####
JOB_NAME=DumpStagedSPMs
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=SFA_SPM_Staging.dmp
TABLES=SFA.SPM_STAGING

>>> Results:

Export: Release 11.1.0.6.0 - Production on Tuesday, 19 February, 2008 9:28:34
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."DUMPSTAGEDSPMS":  system/******** PARFILE=DumpStagedSPMs.dpectl 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SFA"."SPM_STAGING"                         46.49 KB       6 rows
Master table "SYSTEM"."DUMPSTAGEDSPMS" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.DUMPSTAGEDSPMS is:
  /u01/app/oracle/admin/orcl/dpdump/SFA_SPM_Staging.dmp
Job "SYSTEM"."DUMPSTAGEDSPMS" successfully completed at 09:29:50

/* 
|| Listing 2.8:
|| Importing and "unpacking" the staged Oracle 11g SQL Plan Baselines into
|| the target Oracle 11g database
*/

-----
-- Invoke DataPump Import to import the table that contains the staged 
-- SQL Tuning Set statements. Note that the default action of SKIPping
-- a table if it already exists has been overridden by supplying a value
-- of REPLACE for parameter TABLE_EXISTS_ACTION.
-----
impdp system/oracle PARFILE=LoadStagedSPMs.dpictl

#####
# Contents of LoadStagedSPMs.dpictl parameter file:
#####
JOB_NAME=LoadStagedSPMs
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=SFA_SPM_Staging.dmp
TABLE_EXISTS_ACTION=REPLACE

>>> Results of DataPump Import operation:

Import: Release 11.1.0.6.0 - Production on Tuesday, 19 February, 2008 9:31:41
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."LOADSTAGEDSPMS" successfully loaded/unloaded
Starting "SYSTEM"."LOADSTAGEDSPMS":  system/******** PARFILE=LoadStagedSPMs.dpictl 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SFA"."SPM_STAGING"                         46.49 KB       6 rows
Job "SYSTEM"."LOADSTAGEDSPMS" successfully completed at 09:31:52

-----
-- Clear all SQL Plan Baselines whose SQL text contains the tag "SPM_2"
-----
SET SERVEROUTPUT ON
VARIABLE nRtnCode NUMBER;
BEGIN
    :nRtnCode := 0;
    FOR r_SPMB IN (
	SELECT sql_handle, plan_name
          FROM dba_sql_plan_baselines
         WHERE sql_text LIKE '%SPM_2%'
	)
        LOOP
            :nRtnCode :=
    		DBMS_SPM.DROP_SQL_PLAN_BASELINE(r_SPMB.sql_handle, r_SPMB.plan_name);
		DBMS_OUTPUT.PUT_LINE('Drop of SPBs for Handle ' || r_SPMB.sql_handle
                || ' and Plan ' || r_SPMB.plan_name
                || ' completed: RC = ' || :nRtnCode);
		END LOOP;
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Fatal error during cleanup of SQL Plan Baselines!');
    	ROLLBACK;
END;
/

-----
-- Now, "unpack" the SQL Plan Baselines staging table directly into the SMB
-----
SET SERVEROUTPUT ON
VARIABLE plans_loaded NUMBER;
BEGIN
    :plans_loaded :=
        DBMS_SPM.UNPACK_STGTAB_BASELINE (
            table_name => 'SPM_STAGING'
           ,table_owner => 'SFA'
           ,creator => 'SYS'
        );
    DBMS_OUTPUT.PUT_LINE('Total SQL Plan Baselines Loaded: ' || :plans_loaded);
END;
/

/* 
|| Listing 2.9:
|| Show the current contents of the SQL Management Base
*/

Tue Feb 19                                                                                                   page    1
                                                     Current SQL Plan Baselines
                                                   (From DBA_SQL_PLAN_BASELINES)

         SQL      Plan                                                 CBO Ena-           Auto Created     Last
Creator  Handle   Name     SQL Text                  Origin           Cost bled Acpt Fixd Purg On          Executed
-------- -------- -------- ------------------------- ------------ -------- ---- ---- ---- ---- ----------- -----------
LDGN     68516a84 07e0351f SELECT /*SPM_1.1*/        AUTO-CAPTURE      757 YES  YES  NO   YES  2008-01-20  2008-01-20
                                S.cust_id                                                      10:47:14    10:47:31
                                ,C.cust_last_name
                               ,S.prod_id
                                ,P.pro

LDGN     68516a84 ddc1fcd0 SELECT /*SPM_1.1*/        AUTO-CAPTURE     2388 YES  NO   NO   YES  2008-01-20
                                S.cust_id                                                      11:04:03
                                ,C.cust_last_name
                               ,S.prod_id
                                ,P.pro

SYS      0047dfb5 e86f00e7 SELECT /*SPM_2_2.5*/      MANUAL-LOAD        13 YES  YES  NO   YES  2008-02-19
                               SR.abbr                                                         09:32:42
                              ,SD.abbr
                              ,SUM(SH.quantity_sold)

SYS      1e72d0bd dd777d18 SELECT /*SPM_2_2.4*/      MANUAL-LOAD        13 YES  YES  NO   YES  2008-02-19
                               rgn_abbr                                                        09:32:42
                              ,dst_abbr
                              ,ter_abbr
                              ,cust_id


SYS      7f161ead bb24e20c SELECT /*SPM_2.2.2*/      MANUAL-LOAD       415 YES  YES  NO   YES  2008-02-19
                                 SR.abbr,                                                      09:32:42
                                 SD.abbr,
                                 SZ.geo_id,

SYS      831c508c 3519879f SELECT /*SPM_2_2.3*/      MANUAL-LOAD        71 YES  YES  NO   YES  2008-02-19
                               SR.abbr                                                         09:32:42
                              ,SD.abbr
                              ,SZ.geo_id
                              ,C.cust_id


SYS      9c7bbbfb 9d1c7b8e SELECT /*SPM_2.2.1*/      MANUAL-LOAD       921 YES  YES  NO   YES  2008-02-19
                                C.cust_state_provinc                                           09:32:42
                           e
                               ,SUM(sh.quantity_sold
                           )

SYS      f6743c1d b197d40d SELECT /*SPM_2_2.6*/      MANUAL-LOAD        60 YES  YES  NO   YES  2008-02-19
                               SR.abbr                                                         09:32:42
                              ,SUM(SH.quantity_sold)
                              ,AVG(SH.quant

8 rows selected.