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