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: @OracleSK
Martin Klier - Performing Databases GmbH <martin.klier@xxxxxxxxxxxxxxxxx> hatADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE
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@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com