Hi,
Is there a best practice for how often table statistics need to be refreshed? How can I tell which tables have out of date statistics?
Thanks!
Refresh Statistics
Moderator: NorbertKrupa
Re: Refresh Statistics
#bud,
For finding if the table has stale statistics, you can check the explain plan of the query referring the table.
The Explain Plan mentions, if there are NO STATISTICS or STALE STATISTICS.
Besides, you can check if the Projections have stastistics or no using this query
And for checking when was the last time the statistics were updated
In General the table Stats need to updated after every bulk loading of table and before a complex query is executed on the table.
Hope this helps..
For finding if the table has stale statistics, you can check the explain plan of the query referring the table.
The Explain Plan mentions, if there are NO STATISTICS or STALE STATISTICS.
Besides, you can check if the Projections have stastistics or no using this query
Code: Select all
select anchor_table_name,has_statistics from projections;
Code: Select all
SELECT projection_column_name, statistics_type,
statistics_updated_timestamp
FROM PROJECTION_COLUMNS;
Hope this helps..
Re: Refresh Statistics
The QUERY_EVENTS table also includes so events related to stale/missing statistics that help you to see that a query was planned without up to date statistics. Workload Analyzer also helps you to identify statistics that need updating. But best is to incorporate the collection of stats into your ETL processes.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Refresh Statistics
nnani/scutter,
Thanks for the help! I'll incorporate a daily check that looks for stale and no statistics and run the analyze_statistics funcntion on tables I load during the ETL.
Thanks for the help! I'll incorporate a daily check that looks for stale and no statistics and run the analyze_statistics funcntion on tables I load during the ETL.
Bud Anderson