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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com
------------------------------
*Von: *"Andy Sayer" <andysayer@xxxxxxxxx>
*An: *"Martin Klier" <martin.klier@xxxxxxxxxxxxxxxxx>
*CC: *"Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com
