9 Replies Latest reply: Nov 12, 2012 12:48 PM by Shanaka Chandrasekera RSS

Oracle table statistics

Shanaka Chandrasekera
Currently Being Moderated

Dear All Members,

 

Is there any way where we can check whether current statistics in table are outdated in oracle? In OTN i found using view "DBA_TAB_MODIFICATIONS" we can gather timestamps of statistics

 

But can we use this to compare the quality of statistics in DB tables?

 

Regards,

Shanaka.

  • Re: Oracle table statistics
    Orkun Gedik
    Currently Being Moderated

    Hi Shanaka,

     

    As far as I know that the DBA_TAB_MODIFICATIONS table used to collect quality of statistics value by SAP, also. There's only one exception that at the first step, without paying an attention to the DBA_TAB_MODIFICATIONS records, the system creates new statistics by using "-f stats -t all" parameters. At the second and further steps, new stats will be created for the tables that are need to be collected.

     

    In short, because of the DB20 transaction reflects DBA_TAB_MODIFICATIONS table to store quality of the table stats, both values should be same.

     

    In addition to the information above, you can read the notes, below;

     

    Note 588668 - FAQ: Database statistics

    Note 408527 - Checking the statistics using DBA_TAB_MODIFICATIONS,

     

    Best regards,

     

    Orkun Gedik

  • Re: Oracle table statistics
    Michael Hofmänner
    Currently Being Moderated

    Statistics do not get outdated, you only need to collect new statistics, when the data changes significantly. Thus the trigger is not the last_analyzed field.

     

    588668 - FAQ: Database statistics

     

    Cheers Michael

  • Re: Oracle table statistics
    Fidel Vales
    Currently Being Moderated

    Shanaka Chandrasekera wrote:

     

    Dear All Members,

     

    Is there any way where we can check whether current statistics in table are outdated in oracle?

    First you have to define "OUTDATED" and this is the main problem a lot of people has.

    do you mean that the statistics were calculated long time ago? (to be define what is "long time ago)

    of do you mean that the table content has changed a lot? (also to be define what "a lot" means)

     

    The first point should NOT be used (see note 825653 - Oracle: Common misconceptions point 7)

    The second one is already used by BRCONNECT as already mentioned and, by defaut, it check that the change is bigger than 50%. for details check the documentation Internal Rules for Update Statistics

  • Re: Oracle table statistics
    Shanaka Chandrasekera
    Currently Being Moderated

    Thanks both Michal and Fiel,

     

    Michal, yest i meant those statistics which are calculated long time ago.....

     

    To my knowledge what oracle do is it keeps on analyzing statistics and once it realize current statistics were collected long time ago it automatically update current statistics ... If this is the normal method then there can be situation if tables are updating rapidly statistics can become old very soon ..

     

    Regards,

    Shanaka.

    • Re: Oracle table statistics
      Fidel Vales
      Currently Being Moderated

      Shanaka Chandrasekera wrote:


      To my knowledge what oracle do is it keeps on analyzing statistics and once it realize current statistics were collected long time ago it automatically update current statistics ... If this is the normal method then there can be situation if tables are updating rapidly statistics can become old very soon ..

       

      Hi,

      Oracle has a job (10g) or "autotask" (11g) that keeps looking the tables for "stalle" statistics. It is basically the same as BRCONNECT does. That is the reason why SAP deactivate that job (and that the job calculate histograms some times where it is not needed and ....)

      This means that you should not have such job working.

       

      Again, the date when statistics were calculated is not "important" and should not be a focus of any analysis (if the table do not changes why calculate statistics?)

Actions