Re: SQL with BIND_EQUIV_FAILURE repeatedly creates same range over and over again

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_at_performing-db.com // https://www.performing-databases.com 

> Von: "Andy Sayer" <andysayer_at_gmail.com>
> An: "Martin Klier" <martin.klier_at_performing-db.com>
> CC: "Oracle-L Freelists" <oracle-l_at_freelists.org>
> 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_at_performing-db.com | martin.klier_at_performing-db.com ] >
> 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_at_performing-db.com | martin.klier_at_performing-db.com ] // [


>> https://www.performing-databases.com/ | https://www.performing-databases.com ]



--
http://www.freelists.org/webpage/oracle-l