refresh table mon_mods$ [message #233727] |
Fri, 27 April 2007 04:32 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
Hi all,
OS : AIX 5.3.0.0
Oracle : 9.2.0.7
On of our customers create tables with the MONITORING option.
For gathering statistics a script checks how much rows are inserted/deleted/updated in table mon_mods$. This is the table where oracle stores is table-monitoring data (since MONITORING option is set)
The gathering of statistics is done as follows:
Quote: |
dbms_stats.gather_table_stats(<owner>, <table_name>, cascade=>true, method_opt=>'for all indexed columns size auto')
|
And now.... the question(s):
1) When statistics are gathered for a table, are the values for that table in mon_mods$ set to zero?
2) After stopping/starting the database, are the values for that table in mon_mods$ set to zero?
I've tried to look it up in the manuals/google/this forum but had no luck so far.
Can anyone point me to a site or documents?
Best Regards,
Martijn
|
|
|
Re: refresh table mon_mods$ [message #233773 is a reply to message #233727] |
Fri, 27 April 2007 07:31 |
dbaxchangedba
Messages: 26 Registered: November 2005
|
Junior Member |
|
|
1) When statistics are gathered for a table, are the values for that table in mon_mods$ set to zero?
Rows for the table for which the stats were collected are completely removed from the mon_mods$ table. You can see it happen through a simple test:
SQL> create table abc as select object_id,object_name from user_objects where 0=1;
Table Created
SQL> alter table abc monitoring;
Table altered.
-- Oracle's view built on mon_mods$
SQL> select count(*) from dba_tab_modifications where table_name = 'ABC';
no rows selected
-- Insert Rows
SQL> insert into abc select object_id,object_name from dba_objects where rownum <= 5000;
5000 rows created.
SQL> commit;
commit complete.
-- I don't want to wait and so flush the stats from memory to disk
SQL> exec dbms_stats.flush_database_monitoring_info
SQL> select table_name,inserts from dba_tab_modifications where table_name = 'ABC';
TABLE_NAME INSERTS
---------- ---------
ABC 5000
-- Collect stats
SQL> dbms_stats.gather_table_stats'ODS','ABC', cascade=>true, method_opt=>'for all indexed columns size auto')
SQL> select table_name,inserts from dba_tab_modifications where table_name = 'ABC';
no rows selected
2) After stopping/starting the database, are the values for that table in mon_mods$ set to zero?
If stats have not been collected on the table then entries are not removed from the mon_mods$ table.
In general, it is a good idea to use the options=> 'GATHER STALE' available with dbms_stats.gather_schema_stats since oracle gathers stats only on tables that has 10% of data changes. Alternatively you could build you own piece of code that queries dba_tab_modification's inserts, updates and deletes columns to see if that amounts to 10% of data changes to the entire table's data and if it does then run dbms_stats.gather_Table_stats to collect statistics.
Good luck....
http://www.dbaxchange.com
|
|
|
Re: refresh table mon_mods$ [message #233781 is a reply to message #233773] |
Fri, 27 April 2007 08:02 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
Thank you very much.
I wasn't aware of the dba_tab_modifications table.
(and I was (obviously) not able to make the link between mon_mods$ and dba_tab_modifications)
Our customer is indeed doing what you describe in the last part of your post. (using there own algorithm (based on mon_mod$) to see which tables needs gathering statistics.)
btw.: the term "stale" is a bit strange to me. Formerly I was a unix-admin. Then a "stale"-partition was always a reason for some serious work.
Anyway thanks for your information. It shines a light on my problem.
Regards,
Martijn
|
|
|