/*
|| Oracle 11g Adaptive Cursor Sharing
||
|| Demonstrates Oracle 11g Adaptive Cursor Sharing features, including:
|| - Creating queries with bind variables that take advantage of Adaptive Cursor Sharing
|| - Monitoring the status of Adaptive Cursor Sharing metadata
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Adaptive Cursor Sharing features, and they should be carefully proofread before 
|| being executed against any existing Oracle database to avoid potential damage!
*/

/* 
|| Listing 1:
|| Execute a simple DSS query containing bind variables to initiate 
|| Adaptive Cursor Sharing
*/

-- Clear buffer cache and shared pool
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

-- Declare bind variables
VARIABLE cust_start NUMBER;
VARIABLE cust_end   NUMBER;
VARIABLE time_start DATE;
VARIABLE time_end   DATE;
VARIABLE total_sold NUMBER;
VARIABLE total_qty  NUMBER;

-- Test #1
BEGIN
    :cust_start := 2;
    :cust_end   := 38;
    :time_start := '01 JAN 1998';
    :time_end   := '31 MAR 1998';
SELECT 
     SUM(amount_sold)
    ,SUM(quantity_sold)
  INTO 
     :total_sold
    ,:total_qty
  FROM sh.sales
 WHERE cust_id BETWEEN :cust_start AND :cust_end
   AND time_id BETWEEN :time_start AND :time_end;
END;
/

/* 
|| Listing 2:
|| Queries against Adaptive Cursor Sharing metadata views:
*/

-----
-- VIEW:    V$SQL
-- Purpose: Shows which SQL statements the optimizer is considering
--          as candidates for Adaptive Cursor Sharing
-----
TTITLE 'SQL Statements With Bind Sensitivity Enabled|(from V$SQL)'
COL sql_id          FORMAT A16          HEADING 'SQL ID'
COL hash_value      FORMAT 99999999999  HEADING 'Hash|Value'
COL plan_hash_value FORMAT 99999999999  HEADING 'Plan|Hash|Value'
COL iba_flag        FORMAT A06          HEADING 'Bind|Aware?'
COL sql_text        FORMAT A80          HEADING 'SQL Text'
SELECT 
     sql_id
    ,hash_value
    ,plan_hash_value
    ,is_bind_sensitive ibs_flag
    ,is_bind_aware iba_flag
    ,sql_text
  FROM v$sql
 WHERE ((is_bind_sensitive <> 'N') OR (is_bind_aware <> 'N'))
 ORDER BY hash_value
;
TTITLE OFF

-----
-- VIEW:    V$SQL_CS_HISTOGRAM
-- Purpose: Shows distribution of current Adaptive Cursor Sharing metadata, spread 
--          across three (3) groupings
-----
TTITLE 'Histograms for Adaptive Cursor Sharing|(from V$SQL_CS_HISTOGRAM)'
COL hash_value      FORMAT 99999999999  HEADING 'Hash|Value'
COL sql_id          FORMAT A16          HEADING 'SQL ID'
COL child_number    FORMAT 9999         HEADING 'Chld|#'
COL bucket_id       FORMAT 9999         HEADING 'Bckt|ID#'
COL count           FORMAT 999999       HEADING 'Exec-|ution|Count'     
SELECT
     hash_value
    ,sql_id
    ,child_number
    ,bucket_id
    ,count
  FROM v$sql_cs_histogram
;
TTITLE OFF

-----
-- VIEW:    V$SQL_CS_STATISTICS
-- Purpose: Displays statistics for specific executions of a cursor when it uses
--          Adaptive Cursor Sharing
-----
TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_STATISTICS)'
COL hash_value              FORMAT 99999999999  HEADING 'Hash|Value'
COL sql_id                  FORMAT A16          HEADING 'SQL ID'
COL child_number            FORMAT 9999         HEADING 'Chld|#'
COL bind_set_hash_value     FORMAT 99999999999  HEADING 'Hash|Value'
COL peeked                  FORMAT A05          HEADING 'Peek?'
COL executions              FORMAT 999999       HEADING '# of|Exec-|utions'
COL rows_processed          FORMAT 999999       HEADING '# of|Rows'
COL buffer_gets             FORMAT 999999       HEADING 'Buffer|Gets'
COL cpu_time                FORMAT 999999       HEADING 'CPU|Time'
SELECT
     hash_value
    ,sql_id
    ,child_number
    ,bind_set_hash_value
    ,peeked
    ,executions
    ,rows_processed
    ,buffer_gets
    ,cpu_time
  FROM v$sql_cs_statistics
;
TTITLE OFF

-----
-- VIEW:    V$SQL_CS_SELECTIVITY
-- Purpose: Shows the selectivity metrics that Adaptive Cursor Sharing uses to
--          decide when two cursors with different bind variables should spawn
--          a new execution plan
-----
TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_SELECTIVITY)'
COL hash_value      FORMAT 99999999999  HEADING 'Hash|Value'
COL sql_id          FORMAT A16          HEADING 'SQL ID'
COL child_number    FORMAT 9999         HEADING 'Chld|#'
COL range_id        FORMAT 9999         HEADING 'Rng|ID#'
COL low             FORMAT A12          HEADING 'Low Value'
COL high            FORMAT A12          HEADING 'High Value'
COL predicate       FORMAT A80          HEADING 'Predicates'
SELECT
     hash_value
    ,sql_id
    ,child_number
    ,range_id
    ,low
    ,high
    ,predicate
  FROM v$sql_cs_selectivity
;
TTITLE OFF

/* 
|| Listing 3:
|| Adaptive Cursor Sharing metadata (after first execution of query)
*/


                                                     SQL Statements With Bind Sensitivity Enabled
                                                                     (from V$SQL)

                                      Plan Bind
                         Hash         Hash Sensi- Bind
SQL ID                  Value        Value tive?  Aware? SQL Text
---------------- ------------ ------------ ------ ------ --------------------------------------------------------------------------------
87qtpurhk664g      3777173647    787661731 Y      N      SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN
                                                         :B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1


page    1
                                                        Histograms for Adaptive Cursor Sharing
                                                              (from V$SQL_CS_HISTOGRAM)

                                            Exec-
        Hash                   Chld  Bckt   ution
       Value SQL ID               #   ID#   Count
------------ ---------------- ----- ----- -------
  3777173647 87qtpurhk664g        0     0       0
  3777173647 87qtpurhk664g        0     1       1
  3777173647 87qtpurhk664g        0     2       0

                                                   Selectivity Metrics for Adaptive Cursor Sharing
                                                              (from V$SQL_CS_STATISTICS)

                                                          # of
        Hash                   Chld         Hash         Exec-    # of  Buffer     CPU
       Value SQL ID               #        Value Peek?  utions    Rows    Gets    Time
------------ ---------------- ----- ------------ ----- ------- ------- ------- -------
  3777173647 87qtpurhk664g        0      4302390 Y           1    1098    3178       0

/* 
|| Listing 4:
|| Execute the query twice more, driving the creation of a second execution
|| plan, and view the results of queries against Adaptive Cursor Sharing 
|| metadata views
*/

-----
-- Execution #2
-----
BEGIN
    :cust_start := 42999;
    :cust_end   := 50000;
    :time_start := '01 JAN 1997';
    :time_end   := '31 MAR 1998';

SELECT 
     SUM(amount_sold)
    ,SUM(quantity_sold)
  INTO 
     :total_sold
    ,:total_qty
  FROM sh.sales
 WHERE cust_id BETWEEN :cust_start AND :cust_end
   AND time_id BETWEEN :time_start AND :time_end;
END;
/

-----
-- Execution #3
-----
BEGIN
    :cust_start := 1000;
    :cust_end   := 1400;
    :time_start := '01 JAN 1996';
    :time_end   := '31 MAR 1997';

SELECT 
     SUM(amount_sold)
    ,SUM(quantity_sold)
  INTO 
     :total_sold
    ,:total_qty
  FROM sh.sales
 WHERE cust_id BETWEEN :cust_start AND :cust_end
   AND time_id BETWEEN :time_start AND :time_end;
END;
/

/* 
|| Listing 5:
|| Adaptive Cursor Sharing metadata (after second and third query executions)
*/

                                                     SQL Statements With Bind Sensitivity Enabled
                                                                     (from V$SQL)

                                      Plan Bind
                         Hash         Hash Sensi- Bind
SQL ID                  Value        Value tive?  Aware? SQL Text
---------------- ------------ ------------ ------ ------ --------------------------------------------------------------------------------
87qtpurhk664g      3777173647  2855975716 Y      Y      SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN
                                                         :B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

87qtpurhk664g      3777173647   787661731 Y      N      SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN
                                                         :B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1


                                                        Histograms for Adaptive Cursor Sharing
                                                              (from V$SQL_CS_HISTOGRAM)

                                            Exec-
        Hash                   Chld  Bckt   ution
       Value SQL ID               #   ID#   Count
------------ ---------------- ----- ----- -------
  3777173647 87qtpurhk664g        1     0       1
  3777173647 87qtpurhk664g        1     1       0
  3777173647 87qtpurhk664g        1     2       0
  3777173647 87qtpurhk664g        0     0       1
  3777173647 87qtpurhk664g        0     1       1
  3777173647 87qtpurhk664g        0     2       0

                                                   Selectivity Metrics for Adaptive Cursor Sharing
                                                              (from V$SQL_CS_STATISTICS)

                                                          # of
        Hash                   Chld         Hash         Exec-    # of  Buffer     CPU
       Value SQL ID               #        Value Peek?  utions    Rows    Gets    Time
------------ ---------------- ----- ------------ ----- ------- ------- ------- -------
  3777173647 87qtpurhk664g        1 1601990286  Y         1      1      2      0
  3777173647 87qtpurhk664g        0 `  4302390   Y          1   1098   3178      0

                                                   Selectivity Metrics for Adaptive Cursor Sharing
                                                             (from V$SQL_CS_SELECTIVITY)

        Hash                   Chld   Rng
       Value SQL ID               #   ID# Low Value    High Value   Predicates
------------ ---------------- ----- ----- ------------ ------------ --------------
  3777173647 87qtpurhk664g        1     0 0.000616    0.000753    <=B1
  3777173647 87qtpurhk664g        1     0 0.900000    1.100000    >=B2
  3777173647 87qtpurhk664g        1     0 0.109520    0.133858    <=B3
  3777173647 87qtpurhk664g        1     0 0.821710    1.004312    >=B4