This discussion is archived
5 Replies Latest reply: Oct 24, 2013 10:17 PM by dba-developer RSS

Analyzing SQL tuning Advisor Recomendations

dba-developer Newbie
Currently Being Moderated

    Hi Experts,

                      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.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => 'ABCD', indname =>
            'XYZ', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE);

  Rationale
  ---------
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points