April 6, 2015

BIND_EQUIV_FAILURE – Or when you will regret using Adaptive Cursor Sharing

Previously, when I was asked to define the Adaptive Cursor Sharing(ACS) feature I’ve often used the following definition: “it represents an answer to the always threating and challenging Oracle task of sharing cursors and optimizing SQL”.

Time passes and I have altered a little bit this definition to become: “it represents a short answer to the always threating and challenging Oracle task of sharing cursors and optimizing SQL’’.

Time passes again and I ended up by drastically altering my initial ACS definition to become:“In certain very plausible situations, It might represent a serious threat for your application where you will be happy to disable it provided you have enough experience to identify the link between ACS and your threat”.

If you want to know what has changed my mind about ACS, then follow this situation taken from a real life running system I am going to summarize:

Hmmm…. A huge count of non shared child cursors due to BIND_EQUIV_FAILURE.

The official Oracle documentation about BIND_EQUIV_FAILURE says : the bind value’s selectivity does not match that used to optimize the existing child cursor.This definition together with the selectivity xml tag mentioned above gave me a first clue: Adaptive Cursor Sharing (in this case Extended Cursor Sharing).

That is an impressive number of records in this dynamic view. For a single sql_id we have about 17 million of rows in this ACS monitoring view!!! This is dramatically altering the execution time of the underlying sql_id query.

If you don’t know what v$sql_cs_selectivity view stands for then look:

Once a cursor becomes bind aware, each time this cursor is executed, the Extended Cursor Sharing layer code peeks at the bind variable values (and in this particular case there are 9 bind variables), and execute, behind the scene, a select against v$sql_cs_selectivity view in order to check if any existing child cursor already covers the selectivity of the peeked bind variables. If a child cursor is found it will be shared. If not then a new child cursor is optimized and inserted into v$sql_cs_selectivity with a new range of bind variable value selectivity.

In this particular case each time the Extended Cursor Sharing layer code fails to find a child cursor in v$sql_cs_selectivity with an adequate range of selectivity(BIND_EQUIV_FAILURE) and compile a new execution plan ending up by filling dramatically v$sql view with multiple “optimal” plans.

We have been asked to use ACS to answer the need of sharing cursor and optimizing SQL. We end up by having neither the first nor the second desire in this particular and very plausible case.

Few extra words about this runing system case:

Query is using 9 bind variables in 9 different predicates

Columns on which bind variables are used have histograms(Frequency and Height Balanced) collected on them

I have read that DOC ID before. The bug description fits perfectly this current situation. The work around suggested is:

set _optimizer_extended_cursor_sharing_rel = none

Setting this parameter to none will stop the extended cursor sharing layer code from peeking at the bind variable and eventually optimizing a new execution plan. In this case even if the cursor is bind sensitive and bind aware, there will be no new optimization until a new hard parse.

I have intentionally omitted to mention this bug because I want to model this case and check it in my personal 12c database before definitely changing my mind about Adaptive Cursor Sharing

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.