Stale index statistics (Oracle 9i and 10g)
Hello,
for a table, Oracle has the info "inserts, updates, deletes" from dba_tab_modifications, so, associated with "num_rows", it is easy to decide if tables statistics are still fresh enough or should be considered stale.
But what about the indexes? Could anyone tell me how Oracle determines if index statistics are stale?
Oracle documentation specifies that if we use for example "DBMS_STATS.GATHER_SCHEMA_STATS( ..., options => 'LIST STALE', ... ), it uses the info from the *_TAB_MODIFICATIONS views, but this procedure can in fact retrieve also indexes.
I am wondering if we can find somewhere information for indexes similar to the dba_tab_modifications info for tables.
for a table, Oracle has the info "inserts, updates, deletes" from dba_tab_modifications, so, associated with "num_rows", it is easy to decide if tables statistics are still fresh enough or should be considered stale.
But what about the indexes? Could anyone tell me how Oracle determines if index statistics are stale?
Oracle documentation specifies that if we use for example "DBMS_STATS.GATHER_SCHEMA_STATS( ..., options => 'LIST STALE', ... ), it uses the info from the *_TAB_MODIFICATIONS views, but this procedure can in fact retrieve also indexes.
I am wondering if we can find somewhere information for indexes similar to the dba_tab_modifications info for tables.
0