I am having two questions related to collect stats.
1) I using Diagnostic HelpStats On For Session to know the recommended stats. Even after collecting the stats on that column, it's still showing to collect the stats on the same column of that table. Is optimizer not able to use the stats collected on that column? Column is compressed and datatype is char(1). In the query, the column is being compared with a string value and it is not casted ( table.column = 'A'). Can you please help me in knowing the reason?
2) When I am collecting stats on some of the recommended columns, the explain is changing and the performance is getting degraded. Before collecting the stats, optimizer is using hash join. When I am collecting the stats, plan was changed, and it is using merge join and doing sorting, the order of joining the tables also changed, which is making it more expensive before collecting the stats. Even though I am collecting the stats on the additional columns, optimizer should be choosing the least cost one. This is really confusing me, am I missing anything? Please advise
In any scenario does collecting stats on the columns that are not required will degrade the performance of query. I understand that collecting stats itself is expensive as it will do FTS, but in any scenario does it degrade the performance of query also?
Collecting stats is a time and resource consuming process so if the columns are not used then you should avoid it. But if the columns are not used in the query then there is no impact on the query performance. The stats only come into play if the columns/index on which the stats are collected are used in the joining condition!
Yes, over-collecting statistics can degrade performances, and surely will as they get outdated.
The diagnostic helps you to see all column / column combinations that could be candidate for statistics collection; this doesn’t mean that they will necessarily help.
Avoid collecting all possible stats based on the helpstats, this rarely (if ever) helps. Rather drop it all, try them individually and see if they actually change your plan in the way you want.