Re: SQL with BIND_EQUIV_FAILURE repeatedly creates same range over and over again

Hello Martin

When a cursor is bind aware, for any of its subsequent executions, Oracle will peek at the bind variable values, check their selectivity and see if there exists a child cursor in the gv$cs_selectivity having a low-high range interval that contains the selectivity of those bind variable values.

· If such a child cursor exists then it will be shared

· If not then a new hard parse will be done

· If the newly hard parsed execution plan is the same as one of the existing child cursors, then, normally one of those child cursors will see its low-high range interval updated while the other will not be shareable anymore

If you want to know whether your next execution will share an existing child cursor or parse a new one then you can follow the example I gave in this articles:

Cursor Selectivity Cube for Frequency Histogram <https://hourim.wordpress.com/2017/10/25/cursor-selectivity-cube-part-i/>

Cursor Selectivity Cube for Hybrid Histogram <https://hourim.wordpress.com/2017/10/28/cursor-selectivity-cube-part-ii/>

But since you’ve already reached 500 child cursors then I think it is better to cancel ECS for this query using /*+ no_bind_aware */ and stick to one of the best existing execution plans that is still okay for a vast majority of your query executions.

Best regards

Mohamed Houri

Le ven. 28 févr. 2020 à 13:42, Ghassan Salem <salem.ghassan_at_gmail.com> a écrit :

> Try disabling fix for *17443547* (_fix_control.....)
>
> On Fri, Feb 28, 2020 at 11:47 AM Patrick Jolliffe <jolliffe_at_gmail.com>
> wrote:
>
>> Martin, We're seeing same issue,
>> Sometimes even exact same values in v$sql_cs_selectivity for multiple
>> child cursors (all remain is_shareable='Y')
>> We're thinking it's probably Bug 28794230 : 12.2 CURSOR MUTEX X DUE TO
>> SQL NOT SHARED BECAUSE OF BIND_EQUIV_FAILURE
>> But I'm having a hard time getting this deployed unless we can reproduce
>> the issue, which is proving challenging.
>> Regards
>> Patrick
>>
>> On Fri, 28 Feb 2020 at 16:43, Andy Sayer <andysayer_at_gmail.com> wrote:
>>
>>> Hi Martin,
>>>
>>> I don’t have a script at the moment but you can probably do something
>>> with v$sql_bind_data for the actual bind values of a child, and the
>>> v$sql_cs% views to see where the child cursor landed in the selectivity
>>> buckets.
>>>
>>> You can check Dba_tab_histograms to see which value ranges of the column
>>> would return which selectivity - and you would look there to see how many
>>> potential combinations could exist.
>>>
>>> Maybe someone else will share a script they’ve already put together for
>>> this.
>>>
>>> Thanks,
>>> Andy
>>>
>>> On Fri, 28 Feb 2020 at 09:03, Martin Klier - Performing Databases GmbH <
>>> martin.klier_at_performing-db.com> wrote:
>>>
>>>> Hi Andy,
>>>>
>>>> thank you interesting idea, not only for this case. How can I tie down
>>>> which cursor is related to which hist bucket combination?
>>>>
>>>> Thank you!
>>>> Martin
>>>>
>>>>
>>>> --
>>>> Martin Klier // Performing Databases GmbH
>>>> Managing Partner // Senior DB Consultant
>>>> Oracle ACE Director
>>>>
>>>> martin.klier_at_performing-db.com // https://www.performing-databases.com
>>>>
>>>>
>>>>
>>>> ------------------------------
>>>>
>>>> *Von: *"Andy Sayer" <andysayer_at_gmail.com>
>>>> *An: *"Martin Klier" <martin.klier_at_performing-db.com>
>>>> *CC: *"Oracle-L Freelists" <oracle-l_at_freelists.org>
>>>> *Gesendet: *Freitag, 28. Februar 2020 08:49:41
>>>> *Betreff: *Re: SQL with BIND_EQUIV_FAILURE repeatedly creates same
>>>> range over and over again
>>>>
>>>> Hi Martin,
>>>>
>>>> Have you checked what histograms are being used for this SQL? It sounds
>>>> like you have multiple columns that have large numbers of buckets so that
>>>> each of these child cursors use a different bucket combination. It might be
>>>> worth considering Top-N histograms instead now that you're on a version
>>>> that has them - otherwise depending on the distribution, height balanced
>>>> might be decent.
>>>>
>>>> Hope that helps,
>>>> Andrew
>>>>
>>>> On Fri, 28 Feb 2020 at 08:40, Martin Klier - Performing Databases GmbH <
>>>> martin.klier_at_performing-db.com> wrote:
>>>>
>>>>> Re-Post - seems not ot work with attachments.
>>>>> SQL with BIND_EQUIV_FAILURE repeatedly creates same range over and
>>>>> over again
>>>>>
>>>>>
>>>>> Dear listers,
>>>>>
>>>>> I stumbled over an effect today, which I can't explain. Maybe you can
>>>>> help out?
>>>>>
>>>>> For SQL ID d9k6c3p56r0zc I observe a growing number of child cursors
>>>>> (>500). Reason is BIND_EQUIV_FAILURE/Bind mismatch(33) (except for the
>>>>> first child, where I see LOAD_OPTIMIZER_STATS, which does not surprise me.)
>>>>>
>>>>> I expect this SQL being a candidate for Extended Cursor Sharing, no
>>>>> discussion, the application profits from this adaption in 99.999% of all
>>>>> cases. But increasing the cursors up to _cursor_obsolete_threshold
>>>>> increases my parsing overhead, which I cannot afford here.
>>>>>
>>>>> The selectivity in v$sql_shared_cursor is slightly different every
>>>>> time, but "not much".
>>>>> In v$sql_cs_selectivity I can see, that we get new child cursors with
>>>>> the same cardinality range over and over again.
>>>>>
>>>>> So the question is:
>>>>> Why does this happen?
>>>>>
>>>>> Thank you in advance!
>>>>> Martin Klier
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> More infos:
>>>>>
>>>>> The query is basically, an bit obfuscated:
>>>>> SELECT <comma seperated list of all columns of MYVIEW>
>>>>> FROM MYVIEW
>>>>> WHERE ID = :1 AND (qty - qtyReserved) > :2 AND OtherID = :3
>>>>>
>>>>> Needless to say, the view is a very complex join all over a normalized
>>>>> schema plus inline queries making up view columns.
>>>>>
>>>>>
>>>>>
>>>>> See attached a CSV file with the output of
>>>>> ---------
>>>>> select *
>>>>> from v$sql_cs_selectivity
>>>>> where sql_id='d9k6c3p56r0zc'
>>>>> order by predicate asc, range_id asc, "LOW" asc, "HIGH" asc;
>>>>> ---------
>>>>> Exctract:
>>>>> Child, Predicate, LOW, HIGH:
>>>>> 1 =1 0 0.000361 0.000442
>>>>> 8 =1 0 0.000361 0.000442
>>>>> 20 =1 0 0.000361 0.000442
>>>>> 19 =1 0 0.000361 0.000442
>>>>> 18 =1 0 0.000361 0.000442
>>>>> 17 =1 0 0.000361 0.000442
>>>>> 16 =1 0 0.000361 0.000442
>>>>> 15 =1 0 0.000361 0.000442
>>>>> 14 =1 0 0.000361 0.000442
>>>>> 13 =1 0 0.000361 0.000442
>>>>> 12 =1 0 0.000361 0.000442
>>>>>
>>>>>
>>>>> Example for the reason in v$sql_shared_cursor:
>>>>> <ChildNode><ChildNumber>18</ChildNumber><ID>39</ID><reason>Bind
>>>>> mismatch(33)</reason><size>1x4</size><selectivity>1061987324</selectivity></ChildNode>
>>>>> <ChildNode><ChildNumber>19</ChildNumber><ID>39</ID><reason>Bind
>>>>> mismatch(33)</reason><size>1x4</size><selectivity>1058633042</selectivity></ChildNode>
>>>>> <ChildNode><ChildNumber>20</ChildNumber><ID>39</ID><reason>Bind
>>>>> mismatch(33)</reason><size>1x4</size><selectivity>1059681618</selectivity></ChildNode>
>>>>> (All of them in the other attachment)
>>>>>
>>>>>
>>>>> DB Version: RU Database Release Update : 19.6.0.0.200114 (30557433),
>>>>> recently upgraded from 11.2.0.4
>>>>> DB Architecture: Non-Multitenant
>>>>> OS Platform: Oracle Linux.
>>>>>
>>>>>
>>>>> --
>>>>> Martin Klier // Performing Databases GmbH
>>>>> Managing Partner // Senior DB Consultant
>>>>> Oracle ACE Director
>>>>>
>>>>> martin.klier_at_performing-db.com // https://www.performing-databases.com
>>>>>
>>>>
>>>>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>


--
http://www.freelists.org/webpage/oracle-l