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

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