Adaptive Cursor Sharing and Adaptive Execution Plans

That’s not the end of the story though. As of 11g, Oracle has introduced the concept of adaptive cursor sharing. Based on the performance of a SQL statement, the execution plan may be marked for revision the next time the statement is executed, even when the underlying statistics have not changed at all.

In v$sql this is indicated by the columns is_bind_sensitive and is_bind_aware. The former indicates that a particular sql_id is a candidate for adaptive cursor sharing, whereas the latter means that Oracle acts on the information it has gathered about the cursor and alters the execution plan.

Problematic is that adaptive cursor sharing can only lead to an improved plan after the SQL statement has tanked at least once. You can bypass the initial monitoring by supplying the BIND_AWARE hint: it instructs the database that the query is bind sensitive and adaptive cursor sharing should be used from the very first execution onwards. A prerequisite for the hint to be used is that the bind variables only appear in the WHERE clause and an applicable histogram is available. The hint may improve the performance but you should be aware that it’s rarely the answer in the case of generic static statements, which we describe below. The NO_BIND_AWARE hint does exactly the opposite: it disables bind-aware cursor sharing.

Frequency histograms are important to adaptive cursor sharing. The problem is that they are expensive to compute, unstable when sampled, and the statistics have to be collected at the right time. In Oracle Database 12c, the speed with which histograms are collected has been greatly improved.

Adaptive cursor sharing has a slight overhead though, as explained by the Oracle Optimizer team: additional cursor memory, more soft and hard parses, and more child cursors. The last one may cause cursor cache contention.

The default setting for the CURSOR_SHARING parameter is 'EXACT'. You can also set it to 'FORCE' (11g and 12c) or 'SIMILAR' (11g). These settings are, however, generally recommended only as a temporary measure. Oracle’s own recommendation boils down to the following: 'FORCE' is only used by lazy developers who cannot be bothered to use bind variables.

Note

Oracle never replaces literals in the ORDER BY clause because the ordinal notation affects the execution plan: cursors with different column numbers in the ORDER BY cannot be shared.

Adaptive execution plans were introduced in 12c. When we talked about execution plans we already mentioned the mechanics: they allow execution plans to be modified on the fly. On a development or test system adaptive cursor sharing and adaptive execution plans may mask underlying problems that need to be investigated and solved before the code hits production, which is why they should be switched off. There are even some people who believe that these features have no place in a production system either because once you have determined the optimal execution plan, it should not be changed, lest you run into unexpected surprises. As such, untested execution plans should never be released into the wild, according to Connor McDonald and Tony Hasler.