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

Hi Patrick (and all the other listers who kindly responded),

unfortunately I was out of business for a time for health reasons. Now I try to take up all the loose ends of this conversation again. :)

After reading a bit more about the ECS features of Oracle, I more and more come to the conclusion that it just does not work properly in this case.

Maybe I have to add some raw data to clarify my picture. This dropbox folder contains two CSV files with the ECS metadata of the SQL in question: [ https://www.dropbox.com/sh/9wqgkbxls77gbb7/AAA0jf3ND4X2KidPMd2kJaK-a?dl=0 | https://www.dropbox.com/sh/9wqgkbxls77gbb7/AAA0jf3ND4X2KidPMd2kJaK-a?dl=0 ] Any ideas are welcome. Thank you!

-- 
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: "Patrick Jolliffe" <jolliffe_at_gmail.com>
> An: "Andy Sayer" <andysayer_at_gmail.com>
> CC: "Martin Klier" <martin.klier_at_performing-db.com>, "Oracle-L Freelists"
> <oracle-l_at_freelists.org>
> Gesendet: Freitag, 28. Februar 2020 11:45:48
> Betreff: 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 < [ mailto:andysayer_at_gmail.com |
> 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 < [
>> mailto:martin.klier_at_performing-db.com | 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

>>> [ mailto:martin.klier_at_performing-db.com | martin.klier_at_performing-db.com ] // [
>>> https://www.performing-databases.com/ | https://www.performing-databases.com ]



>>>> Von: "Andy Sayer" < [ mailto:andysayer_at_gmail.com | andysayer_at_gmail.com ] >

>>>> An: "Martin Klier" < [ mailto:martin.klier_at_performing-db.com |

>>>> martin.klier_at_performing-db.com ] >

>>>> CC: "Oracle-L Freelists" < [ mailto:oracle-l_at_freelists.org |

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

>>>> mailto:martin.klier_at_performing-db.com | 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

>>>>> [ mailto:martin.klier_at_performing-db.com | martin.klier_at_performing-db.com ] // [
>>>>> https://www.performing-databases.com/ | https://www.performing-databases.com ]

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