I am trying to tune some sql queries for whcih i used the sql tuning advisor from SQl Developer for oracle database 10g. After running the tuning taks i get the recommendations as follows :
1- Statistics Finding
Optimizer statistics for index "ABCD"."XYZ" are stale.
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'ABCD', indname =>
'XYZ', estimate_percent =>
The optimizer requires up-to-date statistics for the index in order to
select a good execution plan.
The same recommendations have been given to for three other indexes
Now when i check in the database for the last analyzed status of the stats they seems to be of the latest date and also the state_status is no for these four indexes.
(i have checked the above in dba_indexes and dba_ind_statistics dictionary tables)
So my question is how come these stats are shown stale by advisor when they are upto date in the database.
Also want to mention one more thing these all are non partitioned bitmap indexes on a table.
No that is not the case as initially i also though it to be the same but then i checked the stats first which were latest updated and then run the sql tunining advisor again the same result stale stats.
Can any body show some pointers over this issue as its making me to doubt on the sql tuning advisor...