Key Concepts - Troubleshooting Oracle Performance - page 35

The v$sql_cs_selectivity view shows the selectivity range related to each predicate of each child cursor.
In fact, the database engine doesn't create a new child cursor for each bind variable value. Instead, it groups values
together that have about the same selectivity and, consequently, should lead to the same execution plan:

SQL> SELECT child_number, trim(predicate) AS predicate, low, high
2 FROM v$sql_cs_selectivity
3 WHERE sql_id = 'asth1mx10aygn'
4 ORDER BY child_number;

CHILD_NUMBER PREDICATE LOW HIGH
------------ --------- ---------- ----------
1 <ID 0.890991 1.088989
2 <ID 0.008108 0.009910

The information in the v$sql_cs_selectivity view isn't only used to show you the selectivity range of each child
cursor; it's also used by the database engine to select the child cursor to be used. In fact, when a cursor is bind aware,
bind variable peeking takes place every time a parse is executed, and the selectivity of the predicates the cursor is
based on is estimated. Based on that estimation, the right child cursor is used. Or, if no cursor for that selectivity range
exists, a new child cursor is created.

■ Bind aware cursors necessitate, for every parse, that the query optimizer performs an estimation of the
selectivity of their predicates. Because of that, adaptive cursor sharing is sometimes not enabled by the database engine.
there are two common cases to consider. the first is for SQL statements containing more than 14 bind variables.
the second is when the query optimizer is unable to correctly estimate the selectivity. For example, selectivity can't be
estimated for variables requiring an implicit datatype conversion (this is another good reason for using the right data
types), or when the referenced objects don't have object statistics.

Caution

The content of the v$sql_cs_histogram view is used by the SQL engine to decide when a cursor is made bind
aware, and therefore, when it should use adaptive cursor sharing. For each child cursor, the view shows three buckets.
The first one ( bucket_id equal 0) is associated with the executions that are considered efficient, the second one
( bucket_id equal 1) with the executions that are considered inefficient, and the third one ( bucket_id equal 2)
with the very inefficient executions. The idea is that after an execution, the SQL engine compares the estimated
cardinalities with the actual cardinalities. Then, depending on how close the two cardinalities are, the execution is
associated (that is, the count column is incremented) to one of the three buckets. Later on, while executing the next
parse operation involving the same cursor, and depending on how the executions are distributed among the three
buckets, a cursor might become bind aware or not. For example, when the number of inefficient executions is as high
as the number of efficient ones, the cursor is made bind aware. The following example illustrates this (notice that, for
the child number 0, the number of efficient executions is equal to the number of inefficient executions):

SQL> SELECT child_number, bucket_id, count
2 FROM v$sql_cs_histogram
3 WHERE sql_id = 'asth1mx10aygn'
4 ORDER BY child_number, bucket_id;