Problematic Statistics of the Application Objects in your Oracle Database

One of the often asked questions is to find out the status of the statistics of the application owned schemas. In this article, I will list the SQL code that I used for this purpose. As well, I will publish a SQL Developer report that can be used for the same purpose.

          , table_name
          , PARTITION_NAME
          , subpartition_name
          , LAST_ANALYZED
          , stale_stats
       from dba_tab_statistics
          owner not in                -- non-application schemas or other excluded shemas 
                      , 'SYSTEM'
                      , 'AUDSYS'
                      , 'APPQOSSYS'
                      , 'CTXSYS'
                      , 'DBSNMP'
                      , 'XDB'
                      , 'GSMADMIN_INTERNAL'
                      , 'OUTLN'
                      , 'WMSYS'
                      , 'ORDDATA'
                      , 'ORDSYS'
                      , 'MDSYS'
                      , 'LBACSYS'
                      , 'DVSYS'
                      , 'OJVMSYS'
                      , 'DBSFWUSER'
                      , 'OLAPSYS'
                      , 'SQLTXPLAIN'  
          and table_name not in                -- tables to be excluded explicitly 
                      ( 'PARTITION_LIST'
                      , 'SUBPAR_LIST'
                      , 'TAB_STAT_LIST'
          and         (
                         stale_stats = 'YES'
                         stale_stats is null
          and         (owner , table_name)
                         not in (
                                   select owner, table_name
                                   from dba_tables 
                                   where temporary = 'Y'
          order by 1, 2, 3, 4;

For ease of use, I have embedded the same into a SQL Developer report that can be downloaded here.


  1. How to Share Reports by Jeff Smith

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: