1 2 Previous Next 15 Replies Latest reply on Oct 13, 2009 12:09 PM by Aman....

    Statistics are not stale ?

      Hallo list,

      I have a table analyzed before about one year the number of rows were 352000 rows , and i have the automated gathering statistics job enabled, today i have discovered that the table has more than 800000 rows and the stale statistcs is still 'NO' ,and hence it is not captured by the automatic job !.

      Can some one explain this ? how it is still not stale and not analyzed ? knowing that i did not lock the statistics.

      Oracle 10g,SE,10.2.3

      Best wishes.

      Edited by: welo on Oct 13, 2009 4:09 AM
        • 1. Re: Statistics are not stale ?
          select table_name,last_analyzed
          form dba_tables
          where table_name = '' and owner = ''

          Check the last_analyzed time.

          Asif Kabir
          1 person found this helpful
          • 2. Re: Statistics are not stale ?
            select num_rows ,last_analyzed,stale_stats from dba_statistics where table_name ='Search_words';
            num_rows :352946 , last_analyzed :11.12.08 ,stale_stats :NO.
            select count*) from search_words ;
            and i have this job sys.gather_stats_job activated ! , i have seeen this problem untill now with this table only ? any clues ???

            Best wishes
            • 3. Re: Statistics are not stale ?
              column operation format a30
              column target format a10 trunc
              column start_time format a18 trunc
              column end_time format a18 trunc
              select operation,target,to_char(start_time,'DD/MM/YYYY HH24:Mi') start_time,
              to_char(end_time,'DD/MM/YYYY HH24:Mi') end_time
              from dba_optstat_operations order by start_time asc;

              Will show if the jobs are running, please review. Hope this is of help.
              1 person found this helpful
              • 4. Re: Statistics are not stale ?
                Satish Kandi
                welo wrote:
                select num_rows ,last_analyzed,stale_stats from *dba_statistics* where table_name ='Search_words';
                typo? Never heard of this view. Did you mean dba_tab_statistics?

                On a side note, for your original query, check the value of stattype_locked in dba_tab_statistics.

                What happens if you manually run gather stats against this table?
                1 person found this helpful
                • 5. Re: Statistics are not stale ?
                  1 person found this helpful
                  • 6. Re: Statistics are not stale ?
                    hallo list,

                    The job is running ,because it is gathering statiscs on other tables in the same schema,also i mean Dba_tab_statistcis and i have checked the statype_locked it is null.

                    i have checked this view :dba_tab_modifications and found for this table :

                    inserts :10740
                    updates :zero
                    deletes :zero

                    timstamp :12.12.08
                    truncated :no

                    also i have checked dba_tables i found monitoring = YES

                    What does this means ???

                    Best wishes

                    Edited by: welo on Oct 13, 2009 4:47 AM
                    • 7. Re: Statistics are not stale ?
                      Perhaps as this is 10G you may wish to try setting monitoring off.
                      I presume it was enabled in 9i and left enabled when you upgraded?

                      From the SQL manual for 10G:-
                      MONITORING | NOMONITORING
                      In earlier releases, you could use these clauses to start or stop the collection of modification statistics on this table. These clauses have been deprecated.

                      If you formerly collected modification statistics on tables by using the DBMS_STATS package in GATHER AUTO or GATHER STALE mode, then you no longer have to do anything. Oracle Database now collects these statistics automatically, and the MONITORING and NOMONITORING keywords in existing code will not cause an error.

                      If, for performance reasons, you do not want to collect modification statistics on any tables, then you should set the STATISTICS_LEVEL initialization parameter to BASIC. Be aware, however, that doing so disables a number of manageability features
                      1 person found this helpful
                      • 8. Re: Statistics are not stale ?

                        This is an upgraded db froma 9i , i have checked another table it is analyzed only with table iam facing the problem,i do not know what special it can be ! , what if the same problem exists with other tables ? i have more than 1000 table with more than 30 customers !.

                        Best wishes
                        • 9. Re: Statistics are not stale ?

                          If the old statistics indicate 352.000 rows and the dba_tab_modifications view shows 10.740 changes, then the changes ratio is:

                          +% changes ratio = (352.000 / 10.740)*100 = *3.05%*+

                          So, the Gather Auto option only chooses thoses tables that acumulate 10% of changes. Your tables isn`t candidate to gather auto.

                          Your solution can be to delete the statistics of that table and to pass the statistis with gather auto again.

                          • 10. Re: Statistics are not stale ?
                            Perhaps your best option is to log a call with Oracle support it may be a simple upgrade problem that they know about. metalink.oracle.com I hope this helps.
                            • 11. Re: Statistics are not stale ?
                              hi ,

                              I know why it is not collecting statistics ,but iam concernced why it is still stal while the size of the table has been doubled ? in particular this is an active table so how can the deletes and updates equal to zero ? i can gather statistcs for this but i what if the problem is by man customers or table how i would Know ? i discovereed this by chance when i was tuning on query.

                              iam checking also meta link perhaps....

                              Best wishes
                              • 12. Re: Statistics are not stale ?
                                One option that comes into mind is that it could be possible that the stats gathering for the table is locked and thus the automatic job would not gather the stats for the table. Can you check back any such setting for the table. AFAIK if the job is running and is gathering stats on all other tables, there isno such thing that it would behave in a different way for another single table.

                                • 13. Re: Statistics are not stale ?
                                  hallo Aman,

                                  i have checked the statype_locked column in dba_tab_statistics it is null, the same as the other tables that are being analzed , i think the problem is why the operations in dba_tab_modifications are not updeted since lst year?! ,perhaps it is a bug... .

                                  Best wishes
                                  • 14. Re: Statistics are not stale ?
                                    Perhaps the dml activyty over this table is under the 10% of the total rows. Dba_tab_modifications only changes with transactions over 10% of rows.

                                    See Metalink 762738.1

                                    If this is the case dba_tab_modifications could not be assigning this table as candidate.
                                    1 2 Previous Next