/*
|| 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