Page 1 of 1
Refresh Statistics
Posted: Mon Nov 04, 2013 3:40 pm
by bud
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!
Re: Refresh Statistics
Posted: Tue Nov 05, 2013 8:23 am
by nnani
#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
Code: Select all
select anchor_table_name,has_statistics from projections;
And for checking when was the last time the statistics were updated
Code: Select all
SELECT projection_column_name, statistics_type,
statistics_updated_timestamp
FROM PROJECTION_COLUMNS;
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..
Re: Refresh Statistics
Posted: Tue Nov 05, 2013 11:16 pm
by scutter
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.
Re: Refresh Statistics
Posted: Wed Nov 06, 2013 12:44 pm
by bud
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.