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 ;
