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