MC/WLA tuning_recommendations vs. query_events vs. dc optimizer/ee events

WLA is an odd beast. It's a sub-system within Vertica that is normally thought of as being part of MC, but probably predates it. You can run WLA manually by running analyze_workload(). Essentially, it runs about 20 different SQL queries all designed to find various issues or alerts within the system. Some of these queries are very edge case and would likely never produce results in most environments. Others are very broad and produce the majority of recommendations that one will see when they view the little Workload Analyzer window in MC. Most of these tend to be of two varieties - missing or stale statistics on a projection, and a recommendation to run DBD for a given query. The latter is produced whenever a query generates a join or group spill. All of the output from WLA gets put into the tuning_recommendations table when it runs.

Query_events is an entirely different thing. It's a system table that captures both optimization and execution events that happen throughout the system, and is updated as these events occur in real time. WLA only runs once every morning (somewhere around 2 am I think, but it's also configurable). So, while WLA will use query_events in order to make some of its recommendations, there could very easily be more stuff in query_events if WLA hasn't noticed them there yet.

FWIW, I have a bunch of fixes and additions to the workload analyzer, and have become something of the "keeper of the workload analyzer", since no one who wrote it originally is still with Vertica, and it hasn't been maintained or updated in probably 5 years at this point. Some of the internal queries are actually broken. I'm happy to share my fixes/additions with clients but only with a gentleman's agreement that, in taking the files, they agree to actually look at the output, since it will actually start producing some useful results that will need to be looked at from time to time. Also, if you're curious, I have the source code to the WLA which includes all the original SQL files.

It would be helpful to see the SQL queries, but not share them directly. Maybe I can explain it better, or suggest ways the customer can run their own reports to see what they're interested in. Thanks!

I'll send you the rules.
If anyone else wants to see them, let me know. Happy to send them over.