This content has been marked as final. Show 4 replies
Hi Tom, try this and see if it reveals anything.
for r in( select table_name from user_tables ) loop
dbms_output.put_line( r.table_name );
dbms_stats.gather_table_stats( user, r.table_name, estimate_percent => 5,
degree => 16, granularity => 'ALL', cascade => true,
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
thanks for your hint. the problem is that it is on the production system and the schema contains a lot of tables.
when i execute the dbms_stats.gather_SCHEMA_stats procedure, i am sure that only tables that have changed since the last analyze will be checked.
how about the dbms_stats.gather_TABLE_stats? i think this will analyze every table, whether it has changed or not. right?
The easiest way to monitor stale tables is looking into
user/all_tab_modifications view which is stored by periodically
or calling dbms_stats.flush_database_monitoring_info.
Another way is to call dbms_stats.gather_schema_stats with "list stale" option.
And for dbms_stats with 1410 error refer to metalink doc# 4430245, 4343493.