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

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

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