Could not clean SM/OPTSTAT by oracle itself?
as we know SM/OPTSTAT component is to store old statistics. with time goes, the component will occupy much space, and we also we know that oracle provide us the settings to let oracle clean old statistics. but from my current case, it seems it does not works, it needs additional setting?
here is my case:
SQL> select dbms_stats.get_stats_history_retention,dbms_stats.get_stats_history_availability,created,sysdate from v$database;
31 16-JUN-09 11.06.35.635254000 PM -04:00 2009-04-13 04:35:47 2010-05-19 01:17:30
we see the database is created on 2009-04-13, and the oldest statistics is 16-JUN-09, with the retention=31. the data before 2010 April should be removed, why the old statistics before 2010, April still remain? miss additional setting?