/* 
|| Oracle 11g SQL Plan Management Listing 1
||
|| Demonstrates Oracle 11g SQL Plan Management(SPM) basic techniques,
|| including:
|| - Enabling automatic capture of SQL Plan Baselines
|| - Displaying captured SQL Plan Baselines
|| - Capturing new (but "unaccepted") SQL Plan Baselines
|| - Displaying SQL Plan Management summary and detail metadata
||
|| 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 1.1: A Candidate for SQL Plan Management
*/

-----
-- SPM Scenario 1.1:
-- Create a query that returns a better execution plan when the setting
-- for OPTIMIZER_MODE is ALL_ROWS vs. FIRST_ROWS
-----
SELECT /*SPM_1.1*/
     S.cust_id
	,C.cust_last_name
    ,S.prod_id
	,P.prod_name
    ,S.amount_sold
    ,S.quantity_sold
  FROM 
     sh.sales S
    ,sh.customers C
    ,sh.products P
 WHERE S.cust_id = C.cust_id
   AND S.prod_id = P.prod_id
   AND S.prod_id BETWEEN 40 AND 60
   AND S.cust_id BETWEEN 13000 AND 15000
;

/*  
|| Listing 1.2: Invoking Automatic SQL Plan Management
*/

-----
-- Step 1: Initialize Automatic SQL Plan Management
-- 1.) Connect as the Load Generator (LDGN) user
-- 2.) Activate automatic capture of SQL Plan Baselines via the
--	OPTIMIZER_CAPTURE_SQL_PLAN_MANAGEMENT parameter
-- 3.) Insure automatic SQL Plan Baselines are used via the
--	OPTIMIZER_USE_SQL_PLAN_MANAGEMENT parameter
-- 4.) Insure the default optimizer mode is set to maximize throughput
-- 	via the OPTIMIZER_MODE parameter
-----

CONNECT ldgn/ldgn;
ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;
ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';

-----
-- Step 2: Execute the sample code (SPM_1_1.sql) twice. This activates automatic
-- capture of a SQL Plan Baseline when the statement is run for the second time
-----
@SPM_1_1.sql;
@SPM_1_1.sql;

/*  
|| Listing 1.3: Showing Results of Automatic SQL Plan Management
*/

-----
-- View:    DBA_SQL_PLAN_BASELINES
-- Purpose: Shows capture and status parameters for all captured SQL Plan
--          Baselines for a specific set of SQL statements
-----
TTITLE 'Current SQL Plan Baselines|(From DBA_SQL_PLAN_BASELINES)'
COL creator         FORMAT A08      HEADING 'Creator'
COL hndle           FORMAT A08      HEADING 'SQL|Handle'
COL plnme           FORMAT A08      HEADING 'Plan|Name'
COL sql_hdr         FORMAT A25      HEADING 'SQL Text' WRAP
COL origin          FORMAT A12      HEADING 'Origin'
COL optimizer_cost  FORMAT 9999999  HEADING 'CBO|Cost'
COL enabled         FORMAT A04      HEADING 'Ena-|bled'
COL accepted        FORMAT A04      HEADING 'Acpt'
COL fixed           FORMAT A04      HEADING 'Fixd'
COL autopurge       FORMAT A04      HEADING 'Auto|Purg'
COL create_dt       FORMAT A11      HEADING 'Created|On' WRAP
COL lst_exc_dt      FORMAT A11      HEADING 'Last|Executed' WRAP
SELECT 
     creator
    ,SUBSTR(sql_handle, -8, 8) hndle
    ,SUBSTR(plan_name, -8, 8)  plnme
    ,SUBSTR(sql_text, 1, 75) sql_hdr
    ,origin
    ,optimizer_cost
    ,enabled
    ,accepted
    ,fixed
    ,autopurge
    ,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt
    ,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt
  FROM dba_sql_plan_baselines
  WHERE (sql_text LIKE '%SPM%')
 ORDER BY 1,2,3
;
TTITLE OFF

                                                              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

-----  
-- Listing 1.4: Switch the optimizer mode so that the first few rows are retrieved
-- (the opposite of maximized throughput) by setting the OPTIMIZER_MODE parameter
-- to FIRST_ROWS, and then re-execute the sample code once again
-----
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';

@SPM_1_1.sql;

-----
-- Finally, deactivate automatic SQL Plan Baseline Capture:
-----
ALTER SESSION SET optimizer_capture_sql_plan_baselines=FALSE;

                                               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


/*  
|| Listing 1.5: Viewing SQL Plan Baseline information using DBMS_XPLAN
*/

-----
-- Show the resulting SQL Plan Baselines that have been retained
-- for SQL statements containing the text "SPM" via a call to procedure
-- DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
-----
SET LINESIZE 150
SET PAGESIZE 2000
SELECT PT.*
   FROM (SELECT 
             DISTINCT sql_handle 
           FROM dba_sql_plan_baselines
          WHERE sql_text like '%SPM%') SPB,
        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_54df96a768516a84
SQL text: SELECT /*SPM_1.1*/      S.cust_id     ,C.cust_last_name     ,S.prod_id
                ,P.prod_name     ,S.amount_sold     ,S.quantity_sold   FROM
          sh.sales S     ,sh.customers C     ,sh.products P  WHERE S.cust_id =
          C.cust_id    AND S.prod_id = P.prod_id    AND S.prod_id BETWEEN 40 AND
          60    AND S.cust_id BETWEEN 13000 AND 15000
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_68516a8407e0351f
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2383008347

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |   223 | 13380 |   758   (1)| 00:00:10 |       |       |
|*  1 |  HASH JOIN                           |                |   223 | 13380 |   758   (1)| 00:00:10 |       |       |
|*  2 |   HASH JOIN                          |                |   451 | 21197 |   352   (1)| 00:00:05 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |    13 |   390 |     2   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                 | PRODUCTS_PK    |    13 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ALL               |                |  2563 | 43571 |   349   (0)| 00:00:05 |     1 |    28 |
|   6 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |  2563 | 43571 |   349   (0)| 00:00:05 |     1 |    28 |
|   7 |      BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|   8 |       BITMAP AND                     |                |       |       |            |          |       |       |
|   9 |        BITMAP MERGE                  |                |       |       |            |          |       |       |
|* 10 |         BITMAP INDEX RANGE SCAN      | SALES_PROD_BIX |       |       |            |          |     1 |    28 |
|  11 |        BITMAP MERGE                  |                |       |       |            |          |       |       |
|* 12 |         BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|* 13 |   TABLE ACCESS FULL                  | CUSTOMERS      |  1064 | 13832 |   405   (1)| 00:00:05 |       |       |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."CUST_ID"="C"."CUST_ID")
   2 - access("S"."PROD_ID"="P"."PROD_ID")
   4 - access("P"."PROD_ID">=40 AND "P"."PROD_ID"<=60)
  10 - access("S"."PROD_ID">=40 AND "S"."PROD_ID"<=60)
  12 - access("S"."CUST_ID">=13000 AND "S"."CUST_ID"<=15000)
  13 - filter("C"."CUST_ID"<=15000 AND "C"."CUST_ID">=13000)

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_68516a84ddc1fcd0
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3352396349

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |   223 | 13380 |  2388   (1)| 00:00:29 |       |       |
|   1 |  NESTED LOOPS                         |                |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                        |                |   223 | 13380 |  2388   (1)| 00:00:29 |       |       |
|   3 |    NESTED LOOPS                       |                |   451 | 21197 |  1937   (1)| 00:00:24 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |    13 |   390 |     2   (0)| 00:00:01 |       |       |
|*  5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |    13 |       |     1   (0)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ALL               |                |    36 |   612 |  1937   (1)| 00:00:24 |     1 |    28 |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    36 |   612 |  1937   (1)| 00:00:24 |     1 |    28 |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|   9 |        BITMAP AND                     |                |       |       |            |          |       |       |
|* 10 |         BITMAP INDEX SINGLE VALUE     | SALES_PROD_BIX |       |       |            |          |     1 |    28 |
|  11 |         BITMAP MERGE                  |                |       |       |            |          |       |       |
|* 12 |          BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|* 13 |    INDEX UNIQUE SCAN                  | CUSTOMERS_PK   |     1 |       |     0   (0)| 00:00:01 |       |       |
|  14 |   TABLE ACCESS BY INDEX ROWID         | CUSTOMERS      |     1 |    13 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("P"."PROD_ID">=40 AND "P"."PROD_ID"<=60)
  10 - access("S"."PROD_ID"="P"."PROD_ID")
       filter("S"."PROD_ID"<=60 AND "S"."PROD_ID">=40)
  12 - access("S"."CUST_ID">=13000 AND "S"."CUST_ID"<=15000)
  13 - access("S"."CUST_ID"="C"."CUST_ID")
       filter("C"."CUST_ID"<=15000 AND "C"."CUST_ID">=13000)