Adaptive Cursor Sharing: Overview

Adaptive Cursor Sharing:

Synopsis. At the heart of any SQL statement optimizing engine is the capability to determine the least expensive and most effective access path to the statement’s target data. Bind variables help limit the number of SQL statement cursors that need to be stored in the Library Cache to return similar result sets. This article discusses a new Oracle Database 11gR1 feature – Adaptive Cursor Sharing – and illustrates how to use it in practical situations to limit hard parsing of SQL statements with bind variables while increasing the relative execution efficiency of those statements.
As I’ve enjoyed telling several hundred students over the past four years as an instructor, I have a sure-fire way to prevent poor database performance: Simply be sure that an application developer never runs any SQL statements against their databases. While this usually elicits at least a few guffaws of laughter and some wistful glances, I usually request an immediate return to reality so we can discuss the best ways to ensure their developers build effective SQL statements. And I assure them that no matter what standards they may put in place, sooner or later a poorly constructed SQL statement will cause their OLTP, DSS, or hybrid database to perform poorly.

“Peeking” at Bind Variables: It’s Not Cheating!

Fortunately, the Oracle RDBMS has provided us with some excellent tools to identify SQL statements that are candidates for improved performance as well as the ability to suggest better methods for reducing the impact of poor execution. At the heart of this approach is the way in which we can influence the cost-based optimizer to parse a SQL statement only when a new execution plan is absolutely necessary. Of course, when a statement is executed for the first time, it must be hard-parsed so that the optimizer can determine the optimal access path to the required data. Because parsing is a relatively expensive operation, however, DBAs are also interested in limiting the number of unique cursors stored in the Library Cache, especially for online transaction processing (OLTP) environments where the same statement may be executed hundreds or thousands of times an hour to provide a similar result set to a calling user session.
The chief method to accomplish effective sharing of cursors is by setting the CURSOR_SHARING initialization parameter appropriately depending on the expected application workload. Setting this parameter to a value ofSIMILAR tells the optimizer to allow cursors to be shared when SQL statements are virtually identical except for literals in their predicates, and the execution plan provides equal or superior performance. Likewise, settingCURSOR_SHARING to a value of FORCE tells the optimizer to share the cursor regardless of the existence of a potentially better execution plan. When SQL statements contain bind variables, there are excellent opportunities to provide this possible performance benefit. However, how can the optimizer initially construct an effective execution plan for a SQL statement without knowing what typical values are going to be used to populate the resulting cursor?
Oracle Database 9i made the first attempt to overcome the potential issues inherent in erroneous plan selection with the introduction of bind variable peeking. As its name implies, whenever a SQL statement that contains bind variable is first executed, Oracle sneaks a quick look at the actual values of the bind variable arguments and uses those values to construct an optimal plan for just that execution. The advantage of this approach is obvious: Instead of a possibly incorrect guess that might result in a (horrendously!) non-optimal execution plan, the cost-based optimizer uses real values to construct the plan.
Unfortunately, however, the drawbacks of this approach for non-OLTP applications are also self-evident. For example, what if the next run of a DSS query specifies a set of bind variable values that need a completely different execution plan for more efficient execution? In fact, this is not uncommon in a Data Warehousing environment, as it’s actually quite desirable to have several potential execution plans for a DSS query because one set of bind variable values may return a result set that contains only a few hundred rows, while another set of values may return several million rows. In this case, Oracle recommends leaving the CURSOR_SHARING parameter at its default value of EXACT to force the generation of a new and (potentially) more efficient execution plan.

Adaptive Cursor Sharing: More Flexible Binding

Oracle Database 11g offers Adaptive Cursor Sharing (ACS) to overcome the possibility that a cursor may be shared when it really shouldn’t be shared. ACS uses two new metrics, bind sensitivity and bind awareness, to implement this feature set.
Bind Sensitivity. Whenever a SQL statement with bind variables is parsed for the first time, the optimizer will mark it as bind-sensitive after it has peeked at the values of the bind variables to determine the relative selectivity of the predicates for the statement. But that’s where the similarity to bind peeking ends, because it also retains these sensitivity measurements for comparison during later executions of the same statement with different values for the same bind variables to determine if an existing execution plan can provide service for that new combination of values.
To illustrate how these bind sensitivity features work, I’ve constructed a simple query shown in Listing 1 against theSH.SALES table in the Oracle 11g SH sample schema – a good choice, since it’s the largest table in the schema, and is deeply partitioned across time ranges. That query uses four bind variables to define a beginning and ending range for SH.SALES.TIME_ID and SH.SALES.CUST_ID.
Adaptive Cursor Sharing Metadata. Oracle 11g also provides three new views and two new columns in dynamic view V$SQL to allow an Oracle DBA to determine if the optimizer has decided that a SQL statement is a candidate for Adaptive Cursor Sharing, as well as peek into the business rules the optimizer has used to classify a SQL statement’s execution plan for sharing:
Table 1. Adaptive Cursor Sharing Views
ViewDescription
V$SQLTwo new columns are added:
  • IS_BIND_SENSITIVE indicates if a SQL statement is bind-sensitive. If this column contains a value of (Y)es, it means that the optimizer peeked at the values of the statement’s bind variables so that it can calculate each predicate’s selectivity.
  • Likewise, IS_BIND_AWARE indicates if the optimizer has also decided that the statement’s cursor is bind-aware after additional execution of the statement.
V$SQL_CS_HISTOGRAMDistributes the frequency (within a three-bucket histogram) at which Oracle 11g used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed.
V$SQL_CS_SELECTIVITYContains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges. These values are also termed the cursor’s selectivity cube.
V$SQL_CS_STATISTICSLists the statistics of whether and/or how often an Adaptive Cursor has been shared. The PEEKED column will display a value of Y(es) if the bind set had been used to build the Adaptive Cursor.
I’ve also constructed sample SQL*Plus queries with formatted output against these views as shown in Listing 2; I’ll be using them in the rest of this article to illustrate exactly how Adaptive Cursor Sharing works. In addition, in Listing 3, I’ve shown the impact of the first execution of the statement on this metadata. After this statement is hard-parsed for the first time, its cursor is automatically marked as bind-sensitive, but not yet bind-aware. Also, the query’s bind variable values during its first execution have placed it in the middle of the three histogram buckets for Adaptive Cursor Sharing.
Bind Awareness. Once a SQL statement’s cursor has been marked as bind-sensitive, the optimizer may also decide that the cursor is bind-aware. The optimizer accomplishes this by checking the values supplied for the bind variables against those already captured for all matching plans during subsequent executions of the same query. If the optimizer decides it can utilize an existing execution plan, then just the cursor execution histogram is updated to reflect the statement’s execution; on the other hand, if the bind variable values are sufficiently different, the optimizer may decide to create a completely new child cursor and execution plan. Once this happens, Oracle 11g also stores the relative selectivity of the child cursor in the Adaptive Cursor Sharing metadata.
I find it helps to think of these selectivity ratings as the central point of an “electron cloud” or sphere of influence. (The Oracle documentation uses the term “selectivity cube” but that’s a little hard-edged for my brain.) During subsequent execution(s) of a cursor, the optimizer compares the existing selectivity statistics against the statistics for the cursor’s most recent execution, and if it observes that most of the executions use approximately the same selectivity sphere, the cursor will be marked as bind-aware.
I’ve illustrated these concepts with two additional executions of the same query, but with totally different bind variables (see Listing 4). The resulting changes to the Adaptive Cursor Sharing metadata for the query’s cursors is immediately evident as shown in Listing 5. Note that Oracle 11g has created a new child cursor for the statement with a plan hash value of 2855975716 and marked it as both bind-sensitive and bind-aware; the selective metrics for these cursors is also updated in the metadata as well.
And what happens when the query is executed with a set of different bind variable values that are just outside the bounds of an existing bind-aware cursor’s sphere of influence? During the hard parse of the statement, the optimizer may simply decide to expand the selectivity sphere of that cursor to now include the new bind values. It does this by generating a new child cursor that incorporates both sets of bind variable values, and then deleting the older, less-inclusive child cursor. This obviously results in only limited additional child cursors being generated when they’re absolutely necessary.
So how do I activate this new feature? The good news is that it’s already activated in Oracle 11g, and even better, it’s completely independent of whatever the setting is for the CURSOR_SHARING initialization parameter. This dramatically increases the chances that a statement that uses bind variables in a hybrid OLTP / DSS environment – not at all uncommon today in Real Application Cluster databases! – will function best depending on how it’s intended to be used.
Impact On SQL Plan Management (SPM). Finally, if you’ve read my SQL Plan Management on Oracle 11g’s new SQL Plan Management (SPM) feature set, you’re probably wondering if Adaptive Cursor Sharing interferes with SPM’s ability to capture and retain SQL execution plans in the SQL Management Base (SMB). Here’s a brief summary of their interaction:
  • If initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES has been set to TRUE to activate automatic plan capture, then a SQL statement with bind variables will be marked as the corresponding enabled and accepted execution plan.
  • If a second execution plan for the same statement is built – not uncommon with Adaptive Cursor Sharing! – then the plan is simply added to the statement’s plan history, but it’s not immediately used because SPM first requires that the new plan is first verified as a better statement.
Unfortunately, this means that a “good” plan might be ignored even when its child cursor’s selectivity sphere would probably return better performance. An excellent workaround to this problem is to leave automatic plan capture at its default setting of FALSE and then simply capture all child cursors in the library cache’s cursor cache into the SMB. This will force all plans for the child cursors generated by Adaptive Cursor Sharing to be marked as SQL Plan Baselines.

Conclusion


Oracle Database 11g’s new Adaptive Cursor Sharing capabilities provide a much simpler method for SQL statements that contain bind variables to share execution plans effectively, but only when it makes sense based on the values supplied for the bind variables. And since Adaptive Cursor Sharing will also generate a new execution plan when the resulting selectivity of the bind variable values warrant it, the relative number of shared cursors is kept to a minimum. The bottom line: Oracle DBAs have to spend a lot less time demystifying unexpectedly poor performance of a (hitherto) well-tuned SQL statement.


=====================================================
Adaptive Cursor Sharing: Overview:


DETAILS

Introduction

With the introduction of the Cost Based Optimizer (CBO) a number of changes were made to calculate the selectivity of a predicate, which in turn affected how the query was optimized.  The selectivity was based on the number of distinct values for a given column or a predefined percentage of rows depending on the relational operator that was used. For more details on predicate selectivity see:
Document 68992.1  Predicate Selectivity
This worked well for data that was evenly distributed but had limitations in applications where data was skewed.

With 9i, a new feature "Bind Peeking" was introduced to try to get around the issues associated with the selectivity of a bind variable. During hard parsing of a query using bind variables, the binds are "peeked at" and selectivity is generated based on the bind and the underlying column statistics. This method is successful but if the bind selectivity of the initial execution of a statement varied from the selectivity of subsequent executions with different sets of binds this could sometimes lead to plans being generated that were not representative of the general query usage and which performed poorly.
In Oracle 10g, this was especially noticeable since the default statistic gathering methodology changed to gather histograms automatically. This meant that selectivity previously generated based on the number of distinct values was now generated based on histograms. This gave the optimizer better information about the distribution of skewed data, the impact of which being that the first execution of a query using binds that were representative of the general query usage was particularly important. For more information on this see:
Document 387394.1 Query using Bind Variables is suddenly slow
Document 430208.1 Bind Peeking By Example
In Oracle 11g, Adaptive Cursor Sharing was introduced to provide a solution to these issues. Adaptive Cursor Sharing monitors the execution statistics for candidates queries and makes it possible for the same query to generate and use different execution plans for different set of binds values. See:

Advantages and Disadvantages of Adaptive Cursor Sharing

Advantages of Adaptive Cursor Sharing

Adaptive cursor sharing is a solution to provide the shareability of binds, with the plan adaptability of literals.
With adaptive cursor sharing the database can adapt execution plans based on the selectivity of values used in bind variables. This resolves the situation where the plan used for one set of bind variables is inappropriate for another set.

Previously, workarounds employed would either use literal values instead of binds, which could lead to excessive hard parsing, or apply a fixed plan that could cause performance issues.

Disadvantages of Adaptive Cursor Sharing

There may be some extra overhead associated with Adaptive Cursor Sharing:
  • More Hard Parses (CPU) - Extra Hard Parses will be required when a cursor becomes "Bind Aware" as we attempt to generate the better matched execution plans for the bind selectivity.
  • More Child Cursors (SGA) - It is recommended that some consideration be taken to increase the size of the shared_pool on upgrade from 10g to 11g, given the extra cursors that may be required to accommodate this feature.
  • More Work to Match the Cursor (CPU) - More Child Cursors and the requirement to match a query to the best execution plan for its predicate selectivity.
  • If you encounter an issue with query performance, in some cases ACS may make the issue more difficult to identify and repeat if a new plan is generated and accepted that then makes the problem "disappear". The other side to this is that the query now performs better without user intervention.
A SQL statement is executed with a less-than-optimal access method, thus taking what may be significantly higher elapsed time, before it is determined to be a candidate for Adaptive Cursor Sharing.   For example, it may perform a full table scan when an index access would have improved performance, or vice-versa.  In other words it is the additional workload observed within this less-than-optimal execution plan, which then causes ACS to take effect.

Adaptive Cursor Sharing (Bind Sensitivity)

The first step towards ACS is recognising a query as "Bind Sensitive". This means that the best plan for a query has potential to be different according to the bind variables supplied to it. In effect this just means the cursor is being monitored to see if it should be made bind aware.
A cursor is marked as Bind Sensitive if:
  • query is executed with bind peeking
  • binds using any of the following relational operators = < > <= >= != or a user defined bind operator e.g. contains(e.job,:job,1)>0,
    From 11.2.0.2 the "LIKE" operator is also supported.
  • A histogram exists on the column containing the bind value.
In other words, a cursor is marked Bind Sensitive if a change in the bind variable value may lead to a different plan.

Apart from checking for a valid operator there are also a number of subsequent bind sensitivity checks that need to be performed before it can be marked as bind sensitive. If any of these checks fail, the cursor will not be marked as bind sensitive and adaptive cursor sharing will not occur and Adaptive Cursor Sharing will be disabled :-
  • Extended cursor sharing has been disabled
  • The query has no binds
  • Parallel query is used
  • Certain parameters like ("_optim_peek_user_binds"=false) are set
  • You are using a /*+ NO_BIND_AWARE */ hint
  • Outlines are being used
  • Query is recursive
  • The number of binds in a given sql statement is greater than 14.  ** Could be less depending on version and setting of fix_control for Bug 10182051.  SeeDocument : 1983132.1
SQL Plan Baselines are designed to work with Adaptive Cursor Sharing.  Any valid enabled SQL Plan Baselines Plans can be used by Adaptive Cursor Sharing.  This means if you have more than one enabled baseline for a given SQL statement ACS can use one or more of those baselines at the same time. 

When all the criteria are met the cursor is marked as bind sensitive and a "sharing context" containing information about execution statistics of the cursor is created and stored against the cursor.

Cursors that are marked as bind-sensitive can been identified by the column IS_BIND_SENSITIVE having a value "Y" in V$SQL or V$SQLAREA.

Extended Cursor Sharing (Bind Aware)

If there is significant variation in the row source cardinality for executions of the same sql statement in consecutive executions a cursor will be marked as bind aware.  This will allow multiple child cursors to be created for different bind selectivity values, while still attempting to share child cursors when possible.

For more information about this please see :-
Document 836256.1 Adaptive Cursor Sharing: Worked Example
Document 1115994.1 Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]

https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1

Monitoring

V$SQL can be used to see whether  a cursor is_bind_sensitive, is_bind_aware, or is_shareable.
Bind context information can be viewed via V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS and V$SQL_CS_HISTOGRAM

V$SQL_CS_SELECTIVITY displays  the valid selectivity ranges for a child cursor in extended cursor sharing mode.
A valid range consists of a low and high value for each predicate containing binds. Each predicate's selectivity (with the current bind value) must fall between the corresponding low and high values in order for the child cursor to be shared.

V$SQL_CS_STATISTICS contains the raw execution statistics used by the monitoring component of adaptive cursor sharing.
A sample of the executions is monitored. This view shows which executions were sampled, and what the statistics were for those executions. The statistics are cumulative for each distinct set of bind values.

V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor sharing.
This information is used to decide whether to enable extended cursor sharing for a query. It  is stored in a histogram, whose bucket's contents are exposed by this view.

Known Issues with Excessive Child Cursors

There is a possibility that Adaptive Cursor Sharing may compound problems in which excessive numbers of child cursors are generated.  This may lead to performance degradation as large numbers of child cursor can put space pressure on the shared pool and may also lead to an increase in mutex X waits for that cursor.

Checklist to avoid known issues
1. Ensure that cursor_sharing is not set to SIMILAR. In 11g  this setting is not recommended and is deprecated in future releases.
Document 1169017.1:ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting
2. If there are high version counts check v$sql_shared_cursor and search My Oracle Support for notes that may allude to the cause of the excessive cursors.
Document 438755.1 : Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value
If there are still excessive child cursors, then Oracle Support should be contacted to assist with diagnosing the issue.
3.  Bug 10182051 has a query with many predicates containing binds has many shareable child cursors (is_shareable = 'Y' in V$SQL) with the same plan hash value, but some with other plan hash values:
Document 10182051.8 EXTENDED CURSOR SHARING GENERATES 100S OF SHAREABLE CHILD CURSORS FOR A QUERY

Known Issues

1. Bug 8357294 is where adaptive cursor sharing will not work from PL/SQL.  This is currently being worked on:
Bug 8357294 ADAPTIVE CURSOR SHARING DOESN'T WORK FOR STATIC SQL CURSORS FROM PL/SQL

2. Extra memory consumption may be seen per cursor in the shared pool when adaptive cursor sharing is enabled due to following bug:
Document 14033503 : EXCESSIVE MEMORY CONSUMPTION FOR ADAPTIVE CURSOR SHARING
If you have more questions, please ask our community:

https://community.oracle.com/community/support/oracle_database/database_tuning

REFERENCES

NOTE:836256.1 - Adaptive Cursor Sharing: Worked Example
BUG:20438009 - COMPLEX QUERY IS NOT BIND SENSITIVE
NOTE:1518681.1 - FAQ: Adaptive Cursor Sharing (ACS) Frequently Asked Questions
NOTE:6644714.8 - Bug 6644714 - High number of child cursors with adaptive cursor sharing

NOTE:68992.1 - Predicate Selectivity
NOTE:1983132.1 - SQL Containing More Than 8 Bind Variables is not Marked as Bind Sensitive
NOTE:13456573.8 - Bug 13456573 - Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing
NOTE:1169017.1 - ANNOUNCEMENT: Deprecating the Cursor_Sharing = 'SIMILAR' Setting
NOTE:387394.1 - Query Using Bind Variables Suddenly Starts to Perform Slowly
NOTE:1115994.1 - Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]
NOTE:430208.1 - Bind Peeking By Example


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

Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)


DETAILS

Introduction

It is a common problem on previous releases of the database that , when bind variables are used, the initial plan can be suboptimal due to the fact that (a) future values used in future executions share the initial plan and (b) the first set of binds used may not be representative of the majority of executions and therefore that initial plan could be considered "bad" in the context of what is best for most executions.
11G introduces the concept of Adaptive Cursor Sharing. The idea now is to no longer blindly share plans but do so only if it is believed the bind values will not degrade a currently stored plan

Testcase

The following testcase is needed to explain the behaviour
create table acs_test (col1 varchar2(1),col2 varchar2(1),col3 varchar2(1));
insert into acs_test values ('A','A','X');
insert into acs_test values ('B','B','X');
begin
for i in 1..20 loop
insert into acs_test  values ('A','C','X');
end loop;
end;
/

begin
for i in 1..20 loop
insert into acs_test  values ('B','D','X');
end loop;
end;
/

begin
for i in 1..958 loop
insert into acs_test  values ('C','D','X');
end loop;
end;
/

create index acs_test_idx on acs_test(col1,col2);

execute dbms_stats.gather_table_stats(NULL,'acs_test',method_opt=>'FOR ALL COLUMNS SIZE 254');
There are now 1000 rows with the following counts:
select col1, count(*) from acs_test group by col1;

C   COUNT(*)
- ----------
A         21
B         21
C        958

SQL> select col2,count(*) from acs_test group by col2;

C   COUNT(*)
- ----------
D        978
A          1
B          1
C         20
The selectivity of  each column value is:
COL1
A -> 21/1000 = 0.021
B -> 21/1000 = 0.021
C -> 958/1000 = 0.958
COL2
A -> 1/1000 = 0.001
B -> 1/1000= 0.001
C -> 20/1000 = 0.020
D -> 978/1000 = 0.978

Discussion

The following testcase will be used for the purpose of this discussion:
var b1 varchar2(128);
var b2 varchar2(128);


begin
:B1 := ;
:B2 := ;
end;
/
select * from acs_test where col1=:B1 and col2=:B2;
The selectivities are central to adaptive cursor sharing. The basic concept is that , in the testcase example,  a cube is stored centered around an x/y axis where x=sel of col1 and y=sel of col2. If future bind values are such that the x/y for those fall outside that cube it  won't share it and will create a new cursor and possibly a new plan so there is now 2 such cubes. If the 3rd execution has bind values that fall inside either of the cubes then they share that child  (and therefore it's plan) , otherwise it too will generate a new child/new cube/new plan
Run the cursor for the first time and use :B1 = 'A' and :B2 = 'A'
The first execution will obviously cause a hard parse. If there is a histogram,  bind peeking is enabled and the cursor  uses binds (which it does) then the sql will be marked as bind sensitive. This can be seen  here:
select child_number, executions, buffer_gets,
       is_bind_sensitive BS, is_bind_aware BA,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          1          30 Y  N       2647430641


NOTE: The cursor is not currently bind aware. This is because, presently there has been 1 execution and therefore it is not yet known if the bind values are such that they will cause different selectivities for different values when executed. What has happened in this first execution is that information related to the bind values and the execution stats are stored in the cursor.
The current execution statistics for the cursor can be seen using:
SQL> select * from v$sql_cs_statistics where sql_id='19sxt3v07nzm4';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS
-------- ---------- ------------- ------------ ------------------- - ----------
ROWS_PROCESSED BUFFER_GETS CPU_TIME
-------------- ----------- ----------
324A9D84 3229253220 19sxt3v07nzm4            0          1498426793 Y          1
             2          30          0 
 
The first execution returned a single row  - the selectivity of 'A'/'A'  was 0.21/0.01
The plan it uses is
--------------------------------------------------------------------------------------------
| Id | Operation                    | Name         | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |              |      |       |     2 (100) |          |
|  1 |  TABLE ACCESS BY INDEX ROWID | ACS_TEST     |    1 |     6 |     2 (0)   | 00:00:01 | 
|  2 |   INDEX RANGE SCAN           | ACS_TEST_IDX |    1 |       |     1 (0)   | 00:00:01 |
-------------------------------------------------------------------------------------------- 
If it is run again using  :B1='C' and :B2='D' this will return 958 rows the selectivity is now 0.958/0.978
It uses the same plan. The buffer gets rises significantly (From 30 to 163)
select child_number, executions, buffer_gets,
       is_bind_sensitive BS, is_bind_aware BA,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641 
The cursor is still not bind aware because this high buffer get run may be a one-off. Run it once more to force a change in behaviour:
select child_number, executions, buffer_gets,
       is_bind_sensitive BS, is_bind_aware BA,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641
           1          1          68 Y  Y       3194050402 
Note there is now a new child (1) and this is now marked as bind_aware. The plan for the child has changed to hash_value 3194050402 which is :
------------------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |          |      |       |     3 (100) |          |
|  1 |  TABLE ACCESS FULL | ACS_TEST |  936 |  5616 |     3 (0)   | 00:00:01 |
------------------------------------------------------------------------------
The information about each bind value used for each child is stored in V$SQL_CS_SELECTIVITY. This view is only populated when the cursor becomes bind aware
SQL> select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH 
     from v$sql_cs_selectivity 
     where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           1 =B2                                               0 0.879750   1.075250
           1 =B1                                               0 0.861750   1.053250 
Rerun the first SQL again (binds 'A','A'):
SELECT child_number,  executions,  buffer_gets,
  is_bind_sensitive BS,  is_bind_aware BA,  plan_hash_value
FROM v$sql
WHERE sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641
           1          1          68 Y  Y       3194050402
           2          1           4 Y  Y       2647430641 
There is  now yet another child and another plan . This is because the cursor is now bind aware and would look to see if it could share child 1, see the ranges for the binds are outside of those for the stored ranges (B1 is 'A' which is a selectivity of  0.021 and  not between 0.861750 and 1.05320 ). The range of values required to meet the criteria for sharing is stored with each  new child:
SQL> SELECT child_number,  predicate,  range_id,  low,  high
FROM v$sql_cs_selectivity
WHERE sql_id = '19sxt3v07nzm4'; 

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           2 =B2                                               0 0.000900   0.001100
           2 =B1                                               0 0.018900   0.023100
           1 =B2                                               0 0.879750   1.075250
           1 =B1                                               0 0.861750   1.053250 
Child 2 B1 was 0.021 - the range is evenly spread around it (+/- 0.0021) ie 10%
Likewise Child 1 B1 was 0.958 and the range is +/- 10% too
So - any future bind pairs will need to be within BOTH ranges. If any are outside the range then a new child  will be created. Running a combination of Child1 and Child2. ie, set B1 to 'A' and B2 to 'D' should create a new child as it cannot share any current one. This returns no rows and creates a new child
SELECT child_number,  executions,  buffer_gets,
  is_bind_sensitive BS,  is_bind_aware BA,  plan_hash_value
FROM v$sql
WHERE sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641
           1          1          68 Y  Y       3194050402
           2          1           4 Y  Y       2647430641
           3          1           2 Y  Y       2647430641 
The plan for the Child is the same as that of Child2.
SQL> SELECT child_number,  predicate,  range_id,  low,  high
FROM v$sql_cs_selectivity order by child_number,range_id,predicate
WHERE sql_id = '19sxt3v07nzm4'; 

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           1 =B1                                               0 0.861750   1.053250
           1 =B2                                               0 0.879750   1.075250
           2 =B1                                               0 0.018900   0.023100
           2 =B2                                               0 0.000900   0.001100
           3 =B1                                               0 0.018900   0.023100
           3 =B2                                               0 0.879750   1.075250
           3 =B1                                               1 0.018900   0.023100
           3 =B2                                               1 0.000900   0.001100


Something, unexpected until now, happened.  There are 4 entries for Child 3. There is a  pair of RANGE_IDs for each bind. Note that the LOW/HIGH range for RANGE_ID 1on B1/B2 is the same as that of Child 2 (where we ran with 'A'/'A'). If the execution with 'A'/'A' is run again now it can be seen that it no longer uses Child 2 but , instead uses Child 3.
SELECT child_number,  executions,  buffer_gets,
  is_bind_sensitive BS,  is_bind_aware BA,  plan_hash_value
FROM v$sql
WHERE sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS B B PLAN_HASH_VALUE
------------ ---------- ----------- - - ---------------
           0          2         137 Y N      2647430641
           1          1          67 Y Y      3194050402
           2          1           4 Y Y      2647430641
           3          2           6 Y Y      2647430641
So - what has happened is that Child3 , because it has used the same plan as Child2 has made that child redundant and the range_ids for Child3 are :
RANGE_ID 0 -- Original Ranges as it would have been if the plan was new.
RANGE_ID 1 -- The incorporation of Child 2s ranges so anything found in that range can use this new Child.

REFERENCES

NOTE:1115994.1 - Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]
NOTE:740052.1 - Adaptive Cursor Sharing: Overview


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


QUESTIONS AND ANSWERS

General ACS Questions

Where are the main documents and Blogs relating to ACS?

The main documents about ACS can be found here:
Document 1115994.1 Introduction to Adaptive Cursor Sharing concepts in 11G and mutimedia demo [Video]
Document 836256.1 Adaptive Cursor Sharing in 11G
Document 740052.1 Adaptive Cursor Sharing: Overview

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
11.1.3.1 Adaptive Cursor Sharing
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#CIHIGAHB
You can find links to Technical White Papers and Blogs for the Oracle Optimizer, including ACS, here:
Document 1337116.1 White Papers and Blog Entries for Oracle Optimizer

Are the ACS buckets based on actual row processes OR estimated row process ?

ACS is based on actual rows not estimated rows.

Should STATISTIC_LEVEL be ALL with ACS?

Statistics level set to all is not required, it is only there so we can see the actual rows in the execution plan.

What is the impact of ACS on the Shared pool?

ACS will increase the shared pool requirements but it is difficult to say by how much since it is dependent on the amount of variability in the plans in the application

Is there any impact/or consideration when using ACS with RAC database ?

No

In RAC, when one instance gets bounced, will it get back the ACS cache from any other instance?

No. The ACS information is unique to each node and is not shared from instance to instance.

Does ACS work for the SQL from PL/SQL?

Yes. ACS will work on the SQL started within PL/SQL, however there are a number of cases where it does not. For example:
Bug 14276867 ACS IS NOT WORKING WITH PL/SQL CODE LEADING TO POOR PERFORMANCE OF THE QUERIES
Summary: For queries executed from pl/sql functions ACS monitoring is not on (v$sql.is_bind_sensitive is N) so the cursors can never become bind sensitive so ACS will never work.
Status: Not a Bug

Bug 8357294 adaptive cursor sharing doesn't work for static sql cursors from pl/sql
Summary: For SQL that is embedded in pl/sql and executed via "execute immediate", ACS does not work because PL/SQL caches it's cursors differently to standard cursors.
Status:   Unresolved

Bug 9197434 BIND PEEKING NOT HAPPENING WHEN USING DBMS_SQL
Summary Bind peeking does not work with dbms_sql. This is because the relevant driver does not handle the bind code in the right way for this to work and would need an enhancement to do so.
Closed as a duplicate of : Base bug:
  Bug 4179405 bind peeking does not work with dbms_sql
  Closed as a duplicate of : Base bug:
    Bug 13896999 ADAPTIVE CURSOR SHARING IS NOT WORKING AS EXPECTED WITH DYNAMIC PL/SQL
    Closed as a duplicate of : Base bug:
      Bug 13386678 BIND PEEKING DOES NOT WORK IN DBMS_SQL
      Closed, not feasible to fix


What is the suggestion to have the CURSOR_SHARING parameter for ACS to be effective?

SIMILAR is deprecated in 12c, so that is not recommended. See:
Document 1169017.1 Deprecating the cursor_sharing = 'SIMILAR' setting
If the application already uses bind variables throughout then EXACT is fine.
If any literals are being used (either all literals or a combination of both literals and binds) and you want to use ACS, then set the CURSOR_SHARING parameter to FORCE. This will convert literal values into binds and allow the cursors to be shared. ACS can then be used to make it possible for the same query to generate and use different execution plans for different set of binds values based upon the selectivity of those values.

Is there a plan to have persistent ACS cursors?

Development are considering this and it may happen in the future.

Licenses and Editions

Is ACS a separately licensed feature?

No.
No separate license is required See:
Document 1361401.1 Where to Find Information About Performance Related Features

Does ACS work with Standard Edition ?

Yes.
ACS is a standard feature of Personal, Standard and Enterprise Editions. See:
Document 1361401.1 Where to Find Information About Performance Related Features

ACS and Parameters

How does CURSOR_SHARING=FORCE/EXACT affect ACS?

CURSOR_SHARING = FORCE converts all SQL statements that do not use bind variables to contain bind variables so that they can be shared.
CURSOR_SHARING = EXACT performs no literal replacement so ACS will work only on SQL that was originally written to include bind variables.

ACS works on all SQL containing bind variables regardless of whether they were in the original SQL or generated by cursor_sharing = FORCE.

Adaptive cursor sharing is independent of the CURSOR_SHARING initialization parameter and is equally applicable to statements that contain user-defined and system-generated bind variables.
Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)
Part Number E16638-07
Section 7.3.10.2 When to Set CURSOR_SHARING to a Nondefault Value
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94345

and

Section 11.1.3.1 Adaptive Cursor Sharing
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#CIHIGAHB

When cursor_sharing=EXACT, will ACS still work?

Yes, as long as the queries contain bind variables. Queries with literals are not candidates for ACS.

Are there drawbacks or negative effects to using the cursor_sharing=FORCE initialisation parameter for Oracle 11g database? Does this have effects on ACS?

CURSOR_SHARING = FORCE  is a workaround to make an application scalable that otherwise would not be because of literals. It is not however the optimal solution (which is to write the application with bind variables in the first place), it is a workaround - we are trying to fix a problem with a parameter. CURSOR_SHARING = FORCE replaces literals with binds. Pre-11g (i.e. with no ACS) queries with bind variables were constrained to a single execution plan per SQL. With 11g and ACS we can have multiple execution plans. So there are no specific negative effects of CURSOR_SHARING = FORCE . With ACS you may get more cursors than without ACS but these should be for different plans based on the different values and their selectivities.

If an application uses literals and CURSOR_SHARING = EXACT, will adding a "bind aware" hint help?

No.
ACS will not be used because there are no binds in the query.

Are there any recommendations with regard to the SESSION_CACHED_CURSORS initialisation parameter relate to ACS?

The SESSION_CACHED_CURSORS parameter allows you to specify the number of cursors to cache within an individual session. This can help performance where queries are repeated within that session.  See:
Document 30804.1 Init.ora Parameter "SESSION_CACHED_CURSORS" Reference

Oracle® Database Reference
11g Release 2 (11.2)
Part Number E25513-03
SESSION_CACHED_CURSORS
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams229.htm#REFRN10195
Whether or not you need a larger session cache with ACS depends on your application and how many extra cursors are being stored with ACS as compared to without. It is difficult to generalise and while ACS is likely to increase the number of cursors for bind aware cursors, it does not necessarily affect all queries and it may not increase the number of cursors significantly overall. If it does, then increasing the cache may help but only if individual sessions re-use the same cursors.

If, for example, you have 100 different child cursors created by ACS and an individual session only use one of these within a particular session then additional session caching would be unlikely to help. If however the session used bind variable values such that all 100 were used then additional session caching may prove beneficial.

Generally, the default value should be sufficient, but if you want to check your usage then we would suggest using a script similar to the following to measure the usage for a representative set of session and use that as a basis for any decision:
Document 208857.1 SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters Based on Usage
If you find that you are using more cursors than you have cached and that, upon testing, a higher value is beneficial for your application, then a higher value can be set.

How does the OPEN_CURSORS initialisation parameter relate to ACS?

The OPEN_CURSORS parameter has nothing to do with ACS. It limits the number of cursors that can be kept open in a session at the same time. Its purpose is to prevent a session from opening an excessive number of cursors. Refer to Reference Manual for details:
Oracle® Database Reference 11g Release 2 (11.2)
Part Number E25513-03
OPEN_CURSORS
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams161.htm#REFRN10137

ACS and Bind Variables

Does bind peeking play a role in ACS?

Yes, it is one of the pre-requisites. ACS needs to know the bind variable value so that it can use column histograms to determine the selectivity of the bind variables that is uses to determine if extra plans are required.

What is the difference between bind aware and bind sensitive?

Bind sensitive means that a query contains bind variables that are sensitive to different plans when the bind values are changed. For example, for a particular bind variable, if data is skewed then the number of rows returned by ValueA might be 10 but for ValueB is 10,000. In this case a different plan may be appropriate for each value. This query would be bind sensitive in that the plan chosen is sensitive to the values of the bind variables supplied when the query is executed.

Bind aware means that the statement has moved into ACS and is a candidate for multiple plans according to different bind variables. This status is achieved by some checks to ensure that cursors are not moved into ACS unless they are deserving of it so as to avoid using extra space in the shared pool unnecessarily.

Why is a query containing more than 8 bind variables not bind sensitive?

For details, please refer to the following article:
Document 1983132.1 SQL Containing More Than 8 Bind Variables is not Marked as Bind Sensitive

Why are there hints to enable/disable bind aware features?

ACS is conservative in making statements bind aware. If ACS made all queries move from bind sensitive to bind aware in every case the shared pool would be flooded with ACS cursors and there would be no benefit over creating a cursor for every different bind value. There can be cases where the order that SQL is executed can mean that the statement sometimes becomes bind aware and other times does not. If we want it to be bind aware we can use the hints.

Can we enable BIND_AWARE hint in any way for all connections from a user or service?

No.
It would be unlikely that you would want to do that because it could impact too many sql statements. It is better to use on only a few sql statements for where it provides a benefit than globally where space and CPU would be wasted.

Should ACS be disabled if the Bind Variable Selectivity Changes too frequently?

No.
ACS is designed to handle changes in bind variable values.

Lets consider that we have SQL with a bind variable in a predicate against a column with a histogram and the bind value is changed many times. In this case the selectivity may be different for all or many (or none even) of thes values. When each value is evaluated, if the selectivity is not within any of the ranges of the existing selectivities already recorded against a bind aware query there will be some overhead because it will have to make the child cursor non-shareable and create a new child cursor copy the selectivity profile and increase the range of it. This overhead occurs primarily in the ramp up phase. Once the system is stable then the selectivities are less likely to be out of range it should just pick plans according to the selectivity of the predicate.

What does it mean for ACS when bind aware is true, but bind sensitive is false?

This is not a normal situation. For a SQL to be bind aware it must first have been bind sensitive. If you have this particular case then we suggest you open a SR with the details.

ACS and Histograms

Are histograms always needed for ACS?

Histograms are required for ACS to work upon SQL with equality predicates. Without histograms the selectivity for an equality predicate against a column is 1 / Number of distinct values in the column. This is the same for all values and thus ACS could not differentiate between plans based on bind value. SQL with range predicates (and LIKE) can work regardless of histogram because different selectivities can be estimated for different values provided compared to the position of the values relative to the high and low values in the column. Obviously with histograms this information will be more accurate.

If there is are histograms, how does Oracle determine that a SQL statement is bind sensitive?

With Equality predicates it cannot.
However a SQL statement with range predicates ( > < >= etc) has different selectivity regardless of the presence of histograms since the optimizer generates selectivity estimates from the low/high column values

ACS and Query Optimization

Can ACS choose a Bad Plan when there is a better one available?

ACS creates plans based upon selectivity ranges. It is still possible that a bad plan could be chosen for a particular selectivity range but it is likely to be better overall than relying on a single plan for all ranges.

Can ACS make the CBO choose a bad plan?

No.
ACS simply triggers a new hard parse based upon certain trigger conditions. It is still up to the optimizer to determine the plan based on the statistics it is provided.

Does ACS impact SQLT output?

SQLT will report information regardless of whether ACS used or not. SQL contains an Adaptive Cursor Sharing section:
SQLT Adaptive Cursor Sharing Section
which will be populated with various ACS related information if ACS is used.

ACS and Sharing of Cursors

Once ACS has made a SQL statement non-shareable, will that plan never be used?

The child cursor will not be used but the plan may be re-generated in other child cursor if it is re-parsed. A Child Cursor becomes non-shareable because now that ACS is enabled and active for this cursor, the new ACS Bind Aware plans need to be used.

Is there a danger of generating a high number of version counts with ACS enabled?

High Version Counts can be an issue regardless of ACS. That said however ACS does have potential to generate more cursors than simply sharing 1 cursor for all different bind values without it. It is considerably better than having a different cursor for every single values like you would with literals. The number of cursors with ACS should stay under a controlled number (you would not expect hundreds of cursors with ACS) _ and is going to be somewhere between 1 cursor (bind variables) and 1 cursor for every value (literals) but tending towards the lower end of that due to the selectivity range model and the likelihood that there are probably a small number of good plans for each SQL statement.

How do SQL profiles play a role in ACS ?

SQL Profiles adjust CBO calculations and impact the final plan. ACS doesn't impact the final plan generated by the optimizer. It manages whether different plans are required to handle different bind variable values. The goal of ACS is to make sure that a new hard parse is triggered when a new plan is required.

ACS and SPM

What is the relationship between ACS and Sql Plan Management (SPM) ?

ACS helps with in plan flexibility.
SPM helps with plan stability.

ACS allows there to be different plans for the same cursor with bind variables, but limits this based upon the selectivity of those bind variables rather than having a different plan for every bind (as you would with literals).

SPM allows you to fix and manage access paths for queries so that only a limited number of verified and accepted plans are available for queries so as to avoid 'nasty suprises' such as the generation of new bad plan.

If I have some plan baselines that I am happy with, then I enable ACS which creates some plans which look promising, how does SPM and ACS interract?

If you are happy with a new plan then you need to determine whether to use them or not and accept the new plans. the new plans will not be executed unless they are accepted.

What would be the impact of ACS we're NOT using SPM and have Statistics locked

ACS can work regardless of SPM or locked statistics. Different set of bind values could get still get different plans.

With SPM, plans that have not been accepted are not so ACS will simply generate plans that are waiting to be accepted or rejected. The standard SPM plan will be used until that happens.

With locked statistics you could still get different plans with different bind variable values until the stored ACS plans have reached a certain level of maturity depending on the relative selectivities of the bind variable values.

Using SPM we can pin a good plan, can this be done in case of a situation demanding better plan (among the child cursors) arises with ACS....


Yes. The cursors generated by ACS will not have been accepted by SPM so SPM still applies.
========================================================================

Optimizer White Papers


Article TitleURL
What to expect from the Optimizer in Oracle Database 12chttp://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
SQL Plan Management in Oracle Database 12chttp://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf
Understanding Optimizer Statistics with Oracle Database 12c - Part 1http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
Best Practices for Gathering Optimizer Statistics with Oracle Database 12c - Part 2http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
Optimizer Technical Papershttps://blogs.oracle.com/optimizer/entry/optimizer_technical_papers1
SQL Plan Management 11ghttp://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf
Understanding Optimizer Statistics with Oracle Database 11g - Part 1http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-stats-concepts-110711-1354477.pdf
Best Practices for Gathering Optimizer Statistics with Oracle Database 11g - Part 2http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf
Upgrading from 11g to 12c: What to expect from the Optimizerhttp://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
Upgrading from 10g to 11g: What to expect from the Optimizerhttp://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf
Upgrading from 9i to 10g: What to expect from the Optimizerhttp://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bidw-optimizer-10gr2-0208-130973.pdf
Explain the Explain Planhttp://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

Blogs

There are numerous Blogs written on various topics by members of the Optimizer development team under the heading Oracle Optimizer at : http://blogs.oracle.com/optimizer and https://blogs.oracle.com/In-Memory/ .

Please also refer to the Oracle DB/EM Troubleshooting Blog at: http://blogs.oracle.com/db/
Specific Optimizer related entries in these blogs are categorized and linked below for your convenience:

Blogs: Cursor Related Features


Article TitleURL
Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.http://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
Update on Adaptive Cursor Sharinghttp://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing
How do adaptive cursor sharing and SQL Plan Management interact?https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing
Why are there more cursors in 11g for my query containing bind variables?http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html
Why do I have hundreds of child cursors when cursor_sharing is set to similar in 10ghttp://blogs.oracle.com/optimizer/entry/whydo_i_have_hundreds_of_child_cursors_when_cursor_sharing_is_set_to_similar_in_10g
How do I force a query to use bind-aware cursor sharing?https://blogs.oracle.com/optimizer/entry/how_do_i_force_a
My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?https://blogs.oracle.com/optimizer/entry/my_cursor_wasn_t_shared

Blogs: Optimizer Transformations


Article TitleURL
Optimizer Transformations: Join Factorizationhttp://blogs.oracle.com/optimizer/entry/optimizer_transformations_join_factorization
Optimizer Transformation: Join Predicate Pushdownhttp://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
Optimizer Transformations: OR Expansionhttp://blogs.oracle.com/optimizer/entry/or_expansion_transformation
Optimizer Transformations: Table Expansionhttp://blogs.oracle.com/optimizer/entry/optimizer_transformations_table_expansion
Optimizer Transformations: View Merging part 1http://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1
Optimizer Transformations: View Merging part 2http://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_2
Optimizer Transformations: Star Transformationhttp://blogs.oracle.com/optimizer/entry/star_transformation
Optimizer Transformations: Subquery Unnesting part 1http://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_1
Optimizer Transformations: Subquery Unnesting part 2http://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_2

Blogs: SQL Plan Management


Article TitleURL
SQL Plan Management (Part 1 of 4) Creating SQL plan baselineshttp://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
SQL Plan Management (Part 2 of 4) SPM Aware Optimizerhttp://blogs.oracle.com/optimizer/entry/sql_plan_management_part_2_of_4_spm_aware_optimizer
SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselineshttp://blogs.oracle.com/optimizer/entry/sql_plan_management_part_3_of_4_evolving_sql_plan_baselines_1
SQL Plan Management (Part 4 of 4): User Interfaces and Other Featureshttp://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4_user_interfaces_and_other_features
Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?https://blogs.oracle.com/optimizer/entry/does_the_use_of_sql
What is the difference between SQL Profiles and SQL Plan Baselines?https://blogs.oracle.com/optimizer/entry/what_is_the_different_between
How do I migrate stored outlines to SQL Plan Management?https://blogs.oracle.com/optimizer/entry/how_do_i_migrate_stored
How does SQL Plan Management match SQL statements to SQL plan baselines?https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management
How do adaptive cursor sharing and SQL Plan Management interact?https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing
SQL Plan Management and Auto Capturehttps://blogs.oracle.com/optimizer/entry/what_you_need_to_know 
Tips on SQL Plan Management and Oracle Database In-Memory Part 1https://blogs.oracle.com/optimizer/entry/tips_on_sql_plan_management
Tips on SQL Plan Management and Oracle Database In-Memory Part 2https://blogs.oracle.com/optimizer/entry/tips_on_sql_plan_management1
Tips on SQL Plan Management and Oracle Database In-Memory Part 3https://blogs.oracle.com/optimizer/entry/tips_on_sql_plan_management2

Blogs: Statistics


Article TitleURL
Understanding Optimizer Statisticshttps://blogs.oracle.com/optimizer/entry/lies_damned_lies_and_statistics
Best Practices for Gathering Optimizer Statisticshttps://blogs.oracle.com/optimizer/entry/lies_damned_lies_and_statistics1
How do I know if the cardinality estimates in a plan are accurate?https://blogs.oracle.com/optimizer/entry/how_do_i_know_if
Concurrent Statistics Gatheringhttp://blogs.oracle.com/optimizer/entry/gathering_optimizer_statistics_is_one
Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table?https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics
How do I restrict concurrent statistics gathering to a small set of tables from a single schema?https://blogs.oracle.com/optimizer/entry/how_do_i_use_concurrent
How do I compare statistics?https://blogs.oracle.com/optimizer/entry/how_do_i_compare_statistics
How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?http://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating
How do I know what extended statistics are needed for a given workload?http://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload
Extended Statisticshttp://blogs.oracle.com/optimizer/entry/extended_statistics
Maintaining statistics on large partitioned tableshttp://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables
Understanding DBMS_STATS.SET_*_PREFS procedureshttp://blogs.oracle.com/optimizer/entry/understanding_dbms_statsset__prefs_procedures
I thought the new AUTO_SAMPLE_SIZE in Oracle Database 11g looked at all the rows in a table so why do I see a very small sample size on some tables?https://blogs.oracle.com/optimizer/entry/i_thought_the_new_auto
How does AUTO_SAMPLE_SIZE work in Oracle Database 11g?https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size
How does the METHOD_OPT parameter work?https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt
Fixed Objects Statistics and why they are importanthttps://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

Blogs: Testcases


Article TitleURL
Providing a Testcase - SQL Test Case Builderhttp://blogs.oracle.com/optimizer/entry/oracle_keeps_closing_my_tar_because_i_cannot_provide_a_testcase_can_you_help

Blogs: Upgrading


Article TitleURL
Upgrading from 9i to 11g and the implicit migration from RBOhttp://blogs.oracle.com/optimizer/entry/upgrading_from_9i_to_11g_and_the_implicit_migration_from_rbo
Why was the RULE hint ignored?https://blogs.oracle.com/optimizer/entry/why_was_the_rule_hint
How do I migrate stored outlines to SQL Plan Management?https://blogs.oracle.com/optimizer/entry/how_do_i_migrate_stored

Blogs: SQL Patches


Article TitleURL
Using SQL Patch to add hints to a packaged applicationhttps://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
Additional Information on SQL Patcheshttps://blogs.oracle.com/optimizer/entry/additional_information_on_sql_patches

Blogs: Queries and Plans



Article TitleURL
Cardinality Feedbackhttp://blogs.oracle.com/optimizer/entry/cardinality_feedback
Dynamic sampling and its impact on the Optimizerhttp://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
Explain the Explain Planhttp://blogs.oracle.com/optimizer/entry/explain_the_explain_plan_white
How do I get an application to use the same execution plan I get in SQL*Plus?http://blogs.oracle.com/optimizer/entry/how_do_i_get_sql_executed_from_an_application_to_uses_the_same_execution_plan_i_get_from_sqlplus
What's Changed between my New Query Plan and the Old One?http://blogs.oracle.com/optimizer/entry/whats_changed_between_my_new_query_plan_and_the_old_one
What should I do with old hints in my workload?http://blogs.oracle.com/optimizer/entry/what_should_i_do_with_old_hints_in_my_workload
How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?http://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex
Why are some of the tables in my query missing from the plan?http://blogs.oracle.com/optimizer/entry/why_are_some_of_the_tables_in_my_query_missing_from_the_plan
Adaptive joinshttps://blogs.oracle.com/optimizer/entry/what_s_new_in_12c
Sql Plan Management and Auto Capturehttps://blogs.oracle.com/optimizer/entry/what_you_need_to_know



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

Predicate Selectivity


Selectivity
~~~~~~~~~~~
Selectivity is a measure of the proportion of a row source retrieved by 
application of a particular predicate or combination of predicates.

Within the Oracle kernel it is expressed as a value between 0 and 1. 
The closer the value is to 0 the more selective the predicate is. 
Selectivity is only used by the CBO.

Basic Selectivity formula:
~~~~~~~~~~~~~~~~~~~~~~~~~~

         Number of records satisfying a condition
Selectivity = -----------------------------------------
               Total Number of records 

In the optimizer, selectivity is used to compare the usefulness of various 
predicates in combination with base object costs. 

Knowing the proportion of the total data set that a column predicate defines 
is very helpful in defining actual access costs. 

By default, column selectivity is based on the high and low values and the 
number of values in the column with an assumption of even distribution of 
data between these two points. 

Histogram data can give better selectivity estimates for unevenly distributed 
column data. There is more discussion regarding Histograms later.

Selectivity is also used to define the cardinality of a particular row source
once predicates have been applied. Cardinality is the expected number of rows 
that will be retrieved from a row source. Cardinality is useful in determining 
nested loop join and sort costs. Application of selectivity to the original 
cardinality of the row source will produce the expected (computed) cardinality 
for the row source.


Glossary of Terms:
~~~~~~~~~~~~~~~~~~

NDV  Number of Distinct Values
Cardinality Number of rows 
Selectivity Proportion of a dataset returned by a particular predicate(or
  group of predicates)

In the following illustrations there are 2 tables (T1 & T2) with columns (c1) and (c2) respectively.

Selectivities:
~~~~~~~~~~~~~~
Without histograms 
~~~~~~~~~~~~~~~~~~
c1 = '4076'              1/NDV 
c1 > '4076'             (High - Value / High - Low) 
c1 >= '4076'            (High - Value / High - Low) + 1/NDV
c1 < '4076'             (Value - Low / High - Low) 
c1 <= '4076'            (Value - Low / High - Low) + 1/NDV
c1 like '4076'           1/NDV

Join selectivity
~~~~~~~~~~~~~~~~

The selectivity of a join is defined as the selectivity of the most selective 
join column adjusted by the proportion of not null values in each join column.


 Sel = 1/max[NDV(t1.c1),NDV(t2.c2)] * 
      ( (Card t1 - # t1.c1 NULLs) / Card t1) * 
      ( (Card t2 - # t2.c2 NULLs) / Card t2)

Bind Variable selectivity
~~~~~~~~~~~~~~~~~~~~~~~~~

Bind variables present a special case because the optimizer has no idea what 
the bind variable value is prior to query optimization. This does not present
a problem with equality predicates since a uniform distribution of data is 
assumed and the selectivity is taken as 1/NDV for the column. However for
range predicates it presents a major issue because the optimizer does not
know where the range starts or stops. Because of this the optimizer has to 
make some assumptions as follows:

c1 =    :bind1           1/NDV 
c1 >    :bind1           Default of 5%
c1 >=   :bind1           Default of 5%
c1 like :bind1           Default of 25%

For more information on bind variables see Note:70075.1

Selectivity With Histograms
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Histograms provide additional information about column selectivity for 
columns whose distribution is non uniform. Histograms store information about
column data value ranges. Each range is stored in a single row and is often 
called a 'bucket'. There are 2 different methods for storing histograms in 
Oracle. If there are a small number of distinct
column values (i.e. less than the number of buckets), the column value 
and the count of that value is stored. If not then a series of endpoints
are stored to enable more accurate selectivity to be determined.

The first method allows the accurate figures to be used. However with 
inexact histograms the terms popular and non-popular value are introduced
and are used to help determine selectivity. A popular value is a value that
spans multiple endpoints whereas a non-popular value does not. 
See Note:72539.1 for more information on histograms.

Exact histograms
~~~~~~~~~~~~~~~~
c1 = '4706'         count of value '4076' / Total Number of Rows 
c1 > value          count of values > '4076' / Total Number of Rows 

InExact Histograms
~~~~~~~~~~~~~~~~~~
col = pop value         # popular buckets / # buckets 
col = non pop           (Density)
col > value             # buckets > value / # buckets


Rules for combining selectivity
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Let P1 and P2 be 2 distinct predicates of query Q

 P1 AND P2
       S(P1&P2) = S(P1) * S(P2)
 P1 OR P2
       S(P1|P2) = S(P1) + S(P2) -[S(P1) * S(P2)]

Index Selectivity for concatenated indexes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Starting with 10.2, when a concatenated index, with all its columns having
equality predicates, is used as an access path, the optimizer uses 1/NDK as
the selectivity (where NDK is the number of distinct keys in the index).

On 9.2.0.7 and 9.2.0.8 this may be enabled with event 38060 level 1.
On 10.1.0.4 and 10.1.0.5 this may be enabled with event 38059 level 1.
On 10.2 adjustments will be made to the selectivity to account for nulls
in the index keys. This also occurs on 10.1.0.5 (with event 38059.)


Join cardinality
~~~~~~~~~~~~~~~~

 Card(Pj) = Card(T1) * Card(T2) * Sel(Pj)

NOTE:70075.1 - Use of Bind Variables in Queries (Pre-9i)

NOTE:72539.1 - Interpreting Histogram Information

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

NOTE--> This information collected from Oracle. we will use for knowledge purpose and not for commercial usage. all rights reserved for Oracle only.

Comments