RE: dba_tab_modifications

Hi Sami

>I did gather statistcs for dictinary segments and fixed objects however still I
>see values in dba_tab_modificaions as below. As per definition, only stale
>objects should be displayed in dba_tab_modifications. Please correct me if I am
>wrong?

Stale objects are objects where at least 10% of the rows has changed or objects that have been truncated. Therefore the view dba_tab_modifications doesn't contain only stale objects, but all objects that have been modified/truncated since the last gathering.

The objects you see, probably, don't exceed the 10% limit. Another possibility is that the statistics for those objects are locked.

>SQL> exec dbms_stats.gather_dictionary_stats;
>PL/SQL procedure successfully completed.
>
>SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
>PL/SQL procedure successfully completed.

WRH-tables are not considered fixed objects, but part of the dictionary. I.e. you have to use gather_dictionary_stats to gather their statistics.

>SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifica
>tions order by inserts desc;
>
>TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
>------------- ---------------- --------- ---------- ----------
>SYS WRH$_LATCH 8352 0 0
>SYS WRH$_SYSSTAT 7584 0 0
>SYS WRH$_PARAMETER 6192 0 0
>SYS WRH$_SQLBIND 5426 0 0
>
>[truncated the output for display purpose]

If you want to force the gathering you have to specify "options=>'GATHER'".

HTH,
Chris

--
http://www.freelists.org/webpage/oracle-l