All Forums Database
ricejfx 8 posts Joined 07/13
27 Apr 2016
Addition of Statistics Significantly Degrading Query Run Time?

Hello colleagues,
I have a query that I have been building, which has a DBS time that I'm satisfied with, but I was trying to improve.  I decided to turn on the DIAGNOSTICS HELPSTATS ON FOR SESSION to see if there were any stats that were obvious and missed.  The EXPLAIN contained a few recommendations, some of which I didn't see helping, but one which I felt I should have recognized and caught.  So, I applied palm to forehead, asked myself how I missed that, and dutifully collected the statistic, eager to see how much improvement I would glean.  I finally stopped the execution after exceeding 2x the original DBS time that I had previously observed (previous DBS times generally take about 2 minutes, with the max time observed being about 2.5 minutes ... I stopped the query at around 6 minutes).  I dropped the stat, reran the query, and I achieved the original, expected DBS time again.
Based on the reading that I've done (which I consider respectable, but certainly not cover-to-cover of every manual), I was under the impression that stats may not necessarily help, but they shouldn't hurt and the reason you would drop a statistic in that case is simply to avoid wasting time with its collection, since it wasn't providing benefit.
So, is my impression wrong?  If so, can you point me to where in the manuals I can read more about this?  I would like to understand the phenomenon more so if I have other stats that exist that may also be hindering the query performance, I can remove those as well. 

You must sign in to leave a comment.