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
