SQL with BIND_EQUIV_FAILURE repeatedly creates same range over and over again
From: Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>
Date: Fri, 28 Feb 2020 08:39:45 +0100 (CET)
Message-ID: <1262810579.2067.1582875585022.JavaMail.zimbra_at_performing-db.com>
Re-Post - seems not ot work with attachments. SQL with BIND_EQUIV_FAILURE repeatedly creates same range over and over again
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
Date: Fri, 28 Feb 2020 08:39:45 +0100 (CET)
Message-ID: <1262810579.2067.1582875585022.JavaMail.zimbra_at_performing-db.com>
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.)
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
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.00044215 =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
-- 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-lReceived on Fri Feb 28 2020 - 08:39:45 CET