RE: dba_tab_modifications
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <dba.orcl@xxxxxxxxx>
- Date: Thu, 11 Aug 2005 19:11:14 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
