5 Replies Latest reply: May 7, 2014 11:29 PM by ⌒o⌒Sam RSS

    Show objects that need to be analyzed

    1849079

      How to check objects that need to be analyzed?

        • 1. Re: Show objects that need to be analyzed
          Rafu

          select owner,table_name from all_tab_statistics where stale_stats = 'YES';

           

           

           

          select owner,index_name from all_ind_statistics where stale_stats = 'YES';

          • 2. Re: Show objects that need to be analyzed
            K.S.I.

            Hi.

             

            In the simplest case....

             

            select * from user_tables t where t.LAST_ANALYZED > trunc(sysdate,'mm');

             

            select * from user_indexes t where t.LAST_ANALYZED > trunc(sysdate,'mm');

             

             

            ALL_TABLES

             

            ALL_TAB_STATISTICS

            • 3. Re: Show objects that need to be analyzed
              SunnyDays

              SELECT 'TABLE' object_type,owner, table_name object_name, last_analyzed, stattype_locked, stale_stats

              FROM all_tab_statistics

              WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL

              and owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )

              AND owner NOT LIKE 'FLOW%'

              UNION ALL

              SELECT 'INDEX' object_type,owner, index_name object_name,  last_analyzed, stattype_locked, stale_stats

              FROM all_ind_statistics

              WHERE (last_analyzed IS NULL OR stale_stats = 'YES') and stattype_locked IS NULL

              AND owner NOT IN ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )

              AND owner NOT LIKE 'FLOW%'

              ORDER BY object_type desc, owner, object_name

               

               

              Hope this help

              • 4. Re: Show objects that need to be analyzed
                Martin Preiss

                a similar discussion was Re: oracle 11g stale statistics. The staleness of statistics is a relevant factor. The age is not necessaryly important - since there may be objects without big DML operations taking place. Another view that may include relevant information is dba|all|user_tab_modification (also mentioned in the linked thread).

                • 5. Re: Show objects that need to be analyzed
                  ⌒o⌒Sam

                  This doc  has fully answer :

                  Script to Show Objects That are Missing Statistics(Doc ID 957993.1).