Mohamed Houri’s Oracle Notes

April 6, 2015

BIND_EQUIV_FAILURE – Or when you will regret using Adaptive Cursor Sharing

Filed under: Tuning — hourim @ 4:42 pm

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:

When you see something like this in the library cache (11.2.0.3.0)

SQL> select
        sql_id
       ,count(1)
     from
        v$sql
     where executions < 2
     group by sql_id
     having count(1) > 10
     order by 2 desc;

SQL_ID          COUNT(1)
------------- ----------
7zwq7z1nj7vga      44217

You start wondering what makes this sql_id having such a big count of different versions in memory.

After few minutes of investigation you end up by ruling out the bind variable hypothesis. And then you finish by asking yourself what the heck is this sql_id?

Hopefully Tanel Poder nonshared script shed a small light on that:

SQL> @nonshared 7zwq7z1nj7vga
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000042CE36F7E8
CHILD_NUMBER         : 0
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>0</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                       <selectivity>1097868685</selectivity>
                      </ChildNode>

-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000045B5C5E478
CHILD_NUMBER         : 1
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>1</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>915662630</selectivity>
                      </ChildNode>
-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000038841E2868
CHILD_NUMBER         : 2
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>2</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>163647208</selectivity>
                      </ChildNode>
-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000038841E2708
CHILD_NUMBER         : 3
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>3</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>4075662961</selectivity>
                      </ChildNode>

…/…

-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000045B5C5E5D8
CHILD_NUMBER         : 99
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>99</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>3246589452</selectivity>
                      </ChildNode>

Moreover a direct select on the v$sql_shared_cursor shows this:

SQL> select
       count(1)
     from
         v$sql_shared_cursor
     where
        sql_id = '7zwq7z1nj7vga';

  COUNT(1)
----------
     45125

SQL> select
        count(1)
     from
       v$sql_shared_cursor
     where
        sql_id = '7zwq7z1nj7vga'
     and BIND_EQUIV_FAILURE = 'Y';

  COUNT(1)
----------
     45121

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).

SQL> select
       count(1)
    from
        v$sql_cs_selectivity
    where
      sql_id = '7zwq7z1nj7vga';

  COUNT(1)
----------
  16,847,320

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
  • Query is a simple select on a single heap table
  • Table has 4 indexes (which produces 4 distinct execution plans among those 99 ones)

 

3 Comments »

  1. Hi Mohamed,

    Thanks for sharing the behaviour of BIND_EQUIV_FAILURE.

    This behaviour identified as BUG 14176247(Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE)).

    Comment by vinodraj.Oracle — April 7, 2015 @ 6:51 am | Reply

  2. Hi Vinodraj,

    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

    Best Regards

    Comment by hourim — April 7, 2015 @ 7:08 am | Reply

  3. […] bind aware and subject to an eventual plan optimization at each execution keep a careful eye on the number of cursors the Extended Cursor Sharing Layer are going to […]

    Pingback by Adaptive Cursor Sharing triggering mechanism | Mohamed Houri’s Oracle Notes — August 12, 2015 @ 10:08 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“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.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: