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

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

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