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

Hello Martin,
well, looks good so far - it is as described in my previous mail :-)

I just picked out some random samples from your data but your selectivity ranges are different (and sometimes not overlapping for cursor merging) - in consequence you get new child cursors for new bind combinations with (several) ranges. You need to look at the full set of binds (in your case 1, >2, 3).

I attached some random samples for illustration. For example please have a look at predicate 3 for each child/range_id - it is different (0.000200/0.000245 vs. 0.000200/0.000245 & 0.001661/0.002030 vs. 0.000200/0.000320 & 0.000437/0.005875). Predicate >2 is stable so I would focus on predicate 1 and 3 and check why you get different cursor selectivity cubes (e.g. check with help of the blog posts by Mohamed Houri and its calculations I already sent to you).

Got histograms on these columns? Need them? Does the different selectivity have an impact on execution plan? If not - get rid of histograms for these predicates.  

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com> hat am 27. März 2020 um 09:53 geschrieben:
>
>
> 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
>
> Any ideas are welcome. Thank you!
>
>
> --
> 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-l




text/plain attachment: snap.txt