Identify stale statistics in projection columns

Rajzz

How to check periodically whether any projection having stale statistics? 

Rahul_Choudhary

Hi

 

You can use projections table to check the same as given in below example.With has_statistics equal to false means that no. of projections has stale statistics. 

 

vsql=> select has_statistics,count(*) from projections group by has_statistics;
has_statistics | count
----------------+-------
f | 16
t | 8030
(2 rows)

 

vsql=> dbadmin=> select projection_name,has_statistics from projections where has_statistics='f';
projection_name | has_statistics
----------------------------------------------------------------+----------------
dc_requests_retried_raw_super | f
vs_tuning_rules_raw_super | f
client_auth_params_raw_super | f
vs_system_tables_raw_super | f
dc_catalog_operations_raw_super | f
vs_projection_column_expressions_raw_super | f
query_metrics_raw_super | f
vs_parameters_catalog_raw_super | f
dc_resource_acquisitions_raw_super | f
dc_lock_requests_raw_super | f
vs_catalog_event_add_derived_column_raw_super | f
vs_plan_steps_raw_super | f

 

 

You can further analyse_statistics() function to compute the statistics for found projections from above query.

 

Thanks

Rahul