1 Reply Latest reply on May 26, 2015 2:48 PM by 2697990

    How to calculate statistics stale %

    979139

      Hi All,

      i have configured automatic statistics gathering job in my database(11.2.0.4) to calculate object statistics with stale % a>= 10. I am having a doubt how Oracle calculates stale percentage of an object. I did search in OTN discussion and found the below querry

      SELECT DT.OWNER,

             DT.TABLE_NAME,
             ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
      FROM   DBA_TABLES DT, DBA_TAB_MODIFICATIONS DTM
      WHERE      DT.OWNER = DTM.TABLE_OWNER
             AND DT.TABLE_NAME = DTM.TABLE_NAME
             AND NUM_ROWS > 0
             AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
             AND OWNER IN ('OWNER_NAME’')
      ORDER BY 3 desc;

      but the query is displaying me output with stale % more than 100 and also statistics is not gathered for few of the tables listed by this query.

       

       

      Thanks and Regards,

      Alex Augustine

        • 1. Re: How to calculate statistics stale %
          2697990

          You can use the following to list all Stale Objects in the DB.

          default is set to 10%

           

          SET SERVEROUTPUT ON

           

          DECLARE

          ObjList dbms_stats.ObjectTab;

          BEGIN

          dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');

          FOR i in ObjList.FIRST..ObjList.LAST

          LOOP

          dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);

          END LOOP;

          END;