Bind Variables and Cursor Sharing

=================================
Bind Variables Peeking and Adaptive Cursors
=================================
The Problem
  When bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. 

   This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

   Depending on the distribution of values ( data skewing aka cardinality) one execution plan might not be suitable for all values.

Adaptive Cursor Approach
   Provided statistics were gathered, and histograms were collected, Oracle would look (peek)  at the value of the bind variables, before generating execution plan.
   If a cursor has a bind variable in it, the Optimizer observes it for a while to see what type of values are passed to the variable and if the plan needs recalculation. 
If the plan does need to be recalculated, the cursor is marked as "Bind-Sensitive".

   The dictionary view V$SQL has been modified to add two more columns: IS_BIND_SENSITIVE and IS_BIND_AWARE. 

   Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change.

   If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y". 

   After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values. 
If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y".


Example from "Adaptive Cursor Sharing in Oracle Database 11g Release 1"

Run this SQL first time:
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 1;

SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)
----------
         1

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type

Plan hash value: 3987223107

--------------------------------------------------------------------------------------------------
| Id | Operation                    | Name                   | Rows  | Bytes |Cost(%CPU)|Time    |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                        |       |       |   2 (100)|        |
|  1 |  SORT AGGREGATE              |                        |     1 |     9 |          |        |
|  2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB               |     1 |     9 |   2   (0)|00:00:01|
|  3 |    INDEX RANGE SCAN          | TEST_TAB_RECORD_TYPE_I |     1 |       |   1   (0)|00:00:01|
--------------------------------------------------------------------------------------------------

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   V$SQL
WHERE  sql_text = 'SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type';

SQL_ID        CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE
------------- ------------ ----------------- -------------
9bmm6cmwa8saf            0 Y                 N

1 row selected.

Now run this SQL second time, with a different parameter, so a Full Table Scan is used:


VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;

SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

   MAX(ID)
----------
    100000

1 row selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type

Plan hash value: 509473618

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |   138 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_TAB | 48031 |   422K|   138   (2)| 00:00:02 |
-------------------------------------------------------------------------------

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   V$SQL
WHERE  sql_text = 'SELECT MAX(id) FROM test_tab WHERE record_type = :l_record_type';

SQL_ID        CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE
------------- ------------ ----------------- -------------
9bmm6cmwa8saf            0 Y                 N
9bmm6cmwa8saf            1 Y                 Y

2 rows selected.

So first time Oracle was "sensitive" of the possibility to use alternate execution plan. (IS_BIND_SENSITIVE = 'Y')

The second time Oracle actually used an alternate execution plan.
(IS_BIND_AWARE = 'Y')


=================================
New views in Oracle 11
=================================
V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS

V$SQL_CS_HISTOGRAM
V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into buckets for each child cursor as shown below:

SELECT * 

FROM V$SQL_CS_HISTOGRAM
WHERE sql_id = '7cv5271zx2ttg';

 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BUCKET_ID  COUNT
-------- ---------- ------------- ------------ ---------- ----------
45C8218C 2144429871 7cv5271zx2ttg            5          0          0
45C8218C 2144429871 7cv5271zx2ttg            5          1          2
45C8218C 2144429871 7cv5271zx2ttg            5          2          0
45C8218C 2144429871 7cv5271zx2ttg            4          0          8

V$SQL_CS_SELECTIVITY
V$SQL_CS_SELECTIVITY shows the selectivity of the different values passed to the bind variable.

SELECT * 

FROM V$SQL_CS_SELECTIVITY
WHERE sql_id = '7cv5271zx2ttg';

 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE   R LOW      HIGH
-------- ---------- ------------- -----------  ----------- - -------- ----------
45C8218C 2144429871 7cv5271zx2ttg           5  =STATE_CODE 0 0.895410   1.094391
45C8218C 2144429871 7cv5271zx2ttg           4  =STATE_CODE 0 0.004589   0.005609

45C8218C 2144429871 7cv5271zx2ttg           4  =STATE_CODE 1 0.002295   0.002804

45C8218C 2144429871 7cv5271zx2ttg           3  =STATE_CODE 0 0.002295   0.002804

45C8218C 2144429871 7cv5271zx2ttg           0  =STATE_CODE 0 0.004589   0.005609

The column PREDICATE shows the various predicates (the WHERE condition) users have used. 
The LOW and HIGH values show the range of values passed.

V$SQL_CS_STATISTICS
V$SQL_CS_STATISTICS shows the activities by the cursors marked either Bind-Aware or Bind-Sensitive.

SELECT  child_number, 
        bind_set_hash_value, 
        peeked as P, 
        executions, 
        rows_processed, 
        buffer_gets, 
        cpu_time
   FROM V$SQL_CS_STATISTICS
  WHERE sql_id = '7cv5271zx2ttg';
 
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
------------ ------------------- - ---------- -------------- ----------- ----------
           1            22981142 Y          1           9592        3219          0
           0            22981142 Y          1           9592        3281          0

The column EXECUTIONS shows how many times the query was executed.

The column PEEKED shows if the optimizer peeked into the bind variable.

=================================
BIND_AWARE and NO_BIND_AWARE Hints
=================================
To force Oracle use/not use the Adaptive Cursor Sharing functionality, there is an option to use hints:

SELECT /*+ BIND_AWARE */ MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;

=================================
SQL Plan Management and SQL Plan Baselining
=================================

The idea is that Oracle would reevaluate current execution plans in an ongoing manner, and when underlying factors (stats collection, database parameters change, etc) would change, it would evaluate a new plan, and use it only if the new plan is better.



This requires a new mechanism of SQL Plans Management.



This is the "SQL Plan Baselining"

It is new from Oracle 11.



optimizer_capture_sql_plan_baselines


A new parameter to control SQL Plan Baselining.

By default it is turned off. 

To turn it on:

ALTER SYSTEM optimizer_capture_sql_plan_baselines = true;

One this parameter is activated, the following takes place:

Once a repeatable statement is identified, its plan is captured and stored as a SQL Plan Baseline, in the database, in a new logical construct known as SQL Management Base (SMB). 

DBA_SQL_PLAN_BASELINES

The SQL Plan Baselines are stored in a view called DBA_SQL_PLAN_BASELINES.

plan_retention_weeks

Whenever a new plan is generated for the query, the old plan is retained in the history in the SMB for X weeks.

If a SQL Plan Baseline has not been used for X weeks it will be purged automatically.

The parameter that set this value is: plan_retention_weeks

BEGIN

  DBMS_SPM.configure('plan_retention_weeks',16);

END;

OEM has a nice option to select a plan from SPM, and compare it cost to current plan.

=================================

Cursor Sharing
=================================

For example, there is this SQL:
SELECT count(*)
FROM customers 
WHERE state_code = 'CT' 
  AND times_purchased > 3;


For some values for state_code, a better execution plan would be an index scan, 
and for some others a full table scan.





CURSOR_SHARING = EXACT
This is default behavior.
There is no bind peeking and cursor sharing.

CURSOR_SHARING = FORCE


If your code does not have bind variables and instead have literal values such as where state_code = 'CT', you can force all literals to be converted to bind variables by specifying an initialization parameter CURSOR_SHARING = FORCE.
This parameter will cause the statement where state_code = 'CT' to be rewritten as where state_code = ":SYS_0001" where SYS_0001 is a system generated variable name. 

This approach will make these statements identical.

When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. 

The database uses the same plan for each execution of the same statement. 

CURSOR_SHARING = SIMILAR

This option is deprecated from Oracle 11gR2. (link)

It was deprecated because the use of this parameter could potentially have a lot of performance implications related to the number of child cursors created for the single parent.  

The idea behind SIMILAR is that cursors are shared only if there is an improvement in execution plan.

=================================

Appendix

=================================

Adaptive Cursors and SQL Plan Management
Adaptive Cursor Sharing in Oracle Database 11g Release 1