This discussion is archived
7 Replies Latest reply: Jan 1, 2013 8:32 AM by DBA112 RSS

oracle 11g stale statistics

oradba11 Newbie
Currently Being Moderated
Hi,
I am working on oracle 11.1.0.7 and aix.
I am trying to find a sql which can show what all tables in the schema have stale statistics and we need to collect statistics ?

In our production environment we are not having scheduled gather stats job ..we are runing job on sunday ,so for weekdays we need to find the
tables which have stale statistics...

Is there any way to find out stale statistics in database ?
  • 1. Re: oracle 11g stale statistics
    JohnWatson Guru
    Currently Being Moderated
    Just do this -

    exec dbms_stats.gather_database_stats(options=>'gather stale')

    and let Uncle Oracle work it out.
  • 2. Re: oracle 11g stale statistics
    ManguilibeKAO Pro
    Currently Being Moderated
    Hi,

    For each table in user_tables (or all_tables or dba_tables), there is a column name last_analyzed which tells you the last time statistics have been collected on the table.

    So, you can run this:
    select table_name, last_analyzed
    from user_tables
    order by last_analyzed desc nulls last;
  • 3. Re: oracle 11g stale statistics
    oradba11 Newbie
    Currently Being Moderated
    yes, i can understand this ...but last analyzed will show ..when table stats was collected ....
    but i want to find out tables ...having stale stats ( as per oracle if changes are more then 10%) oracle will collect statistics ...
    can we find what all tables having stale statistics and if those were collected ....it was properly...and completed successfully...
  • 4. Re: oracle 11g stale statistics
    JohnWatson Guru
    Currently Being Moderated
    if you want to work out in advance what tables will be analyzed by options=>'gather stale' you can join dba_tables to dba_tab_modifications and do the arithmetic comparing dba_tables.num_rows to the count of changes.
    I see little purpose to it, though. I've already told you the answer.

    Edited by: JohnWatson on Jan 1, 2013 12:31 PM
    By the way, there is something wrong with your keyboard: it inserts multiple ... characters.
  • 5. Re: oracle 11g stale statistics
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    oradba11 wrote:
    Hi,
    I am working on oracle 11.1.0.7 and aix.
    I am trying to find a sql which can show what all tables in the schema have stale statistics and we need to collect statistics ?

    In our production environment we are not having scheduled gather stats job ..we are runing job on sunday ,so for weekdays we need to find the
    tables which have stale statistics...

    Is there any way to find out stale statistics in database ?
    Read the manual (PL/SQL supplied packages) pages about dbms_stats.
    The procedurels gather_data_stats and gather_schema_stats have an option parameter which can be set to "list stale" to generate a list of the objects with stale statistics. You'll have to write a little pl/sql wrapper to collect and report the information

    Extracts from $ORACLE_HOME/rdbms/admin/dbmsstat.sql
    -- type for listing stale tables
    type ObjectElem is record (
      ownname     varchar2(32),     -- owner
      objtype     varchar2(6),      -- 'TABLE' or 'INDEX'
      objname     varchar2(32),     -- table/index
      partname    varchar2(32),     -- partition
      subpartname varchar2(32),     -- subpartition
      confidence  number);          -- not used
    
    type ObjectTab is table of ObjectElem;
    
    
      procedure gather_schema_stats
        (ownname varchar2,
         estimate_percent number default 
           to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
         block_sample boolean default FALSE,
         method_opt varchar2 default get_param('METHOD_OPT'),
         degree number default to_degree_type(get_param('DEGREE')),
         granularity varchar2 default get_param('GRANULARITY'),
         cascade boolean default to_cascade_type(get_param('CASCADE')),
         stattab varchar2 default null, statid varchar2 default null,
         options varchar2 default 'GATHER', objlist out ObjectTab,                  --  these are the ones you need to look at
         statown varchar2 default null,
         no_invalidate boolean default 
           to_no_invalidate_type(get_param('NO_INVALIDATE')),
         gather_temp boolean default FALSE,
         gather_fixed boolean default FALSE,
         stattype varchar2 default 'DATA',
         force boolean default FALSE);
    
    
    --   options - further specification of which objects to gather statistics for
    --      'GATHER' - gather statistics on all objects in the schema
    --      'GATHER AUTO' - gather all necessary statistics automatically.  Oracle
    --        implicitly determines which objects need new statistics, and
    --        determines how to gather those statistics.  When 'GATHER AUTO' is
    --        specified, the only additional valid parameters are comp_id, stattab,
    --        statid and statown; all other parameter settings will be
    --        ignored. Also, return a list of objects processed.
    --      'GATHER STALE' - gather statistics on stale objects as determined
    --        by looking at the *_tab_modifications views.  Also, return
    --        a list of objects found to be stale.
    --      'GATHER EMPTY' - gather statistics on objects which currently
    --        have no statistics.  also, return a list of objects found
    --        to have no statistics.
    --      'LIST AUTO' - return list of objects to be processed with 'GATHER AUTO'
    --      'LIST STALE' - return list of stale objects as determined
    --        by looking at the *_tab_modifications views
    --      'LIST EMPTY' - return list of objects which currently
    --        have no statistics
    
    --   objlist - list of objects found to be stale or empty
    Regards
  • 6. Re: oracle 11g stale statistics
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    oradba11 wrote:
    I am working on oracle 11.1.0.7 and aix.
    I am trying to find a sql which can show what all tables in the schema have stale statistics and we need to collect statistics ?

    Is there any way to find out stale statistics in database ?
    In addition to what others have already said, there is a column "STALE_STATS" in the "USER/ALL/DBA_TAB_STATISTICS" that shows a YES/NO flag according to the preferences set for staleness and the modifications recorded in USER/ALL/DBA_TAB_MODIFICATIONS.

    It is however probably better to follow the advice Jonathan has given and to use the official DBMS_STATS procedures provided, because the information in USER/ALL/DBA_TAB_MODIFICATIONS is only updated from time to time, so any query based on that data might miss some objects that recently went stale.

    In order to have the information up to date, the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure can be used. A simple call to DBMS_STATS.GATHER...STATS does update that info as well, by the way, as a side effect.

    Hope this helps,
    Randolf
  • 7. Re: oracle 11g stale statistics
    DBA112 Newbie
    Currently Being Moderated
    As we know, Oracle typically considers stats to be stale if % rows modified is >= 10% of total NUM_ROWS from the time stats were last gathered based on DML captured in DBA_TAB_MODIFICATIONS.

    Below query should give you what you are looking for
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points