I have a long query generated from Microstrategy with lots of joins which was running our of spool space. I checked the query and confirmed that we had stats collected on all the join columns and stats were current. Then there was one column on which I dropped stats, it had both single and multi column stats after which the query started to run fine. Based off this I have the below questions :-
a. Why does the optimizer generate a better plan without stats than with stats?
b. Can stats like above be an issue or I am missing a warning for some other issue?
c. Are there scenarios where random amp sampling would give a better explain than explain with Stats.
This is a difficult question to answer, but here goes:
a. 1 - The optimizer in effect is biased to assume that if you bothered to collect the stats, they must be relevant.
a. 2 - If you have denormalized data or other "highly correlated" groups of columns, but collect (only) single-column stats, the optimizer may mistakenly assume the columns are independent / non-correlated and estimates may vary wildly from actual performance.
b. Not sure what "like above" refers to. If you have a join qualified on multiple columns, you may need multi-column stats instead of / in addition to single-column stats.
c. In principle, stats should be at least as good as random sampling. But stats on (a) and (b) individually might not be as good as random sampling for (a,b), or vice versa.
This is NOT to say that you should collect stats on every possible combination. Collecting stats is relatively expensive. Some would say only collect stats if it changes the query plan - but even if the query plan does not change, a more accurate cost estimate may be valuable for workload management purposes.
If none of the above comments seem to apply, please open an incident. There could be a bug, or an opportunity to improve the optimizer.
I would recommend PRISE Tuning Assistant to investigate performance problems, you can get a full functional free trial here: https://www.prisetools.com/productrequest