Home » RDBMS Server » Performance Tuning » refresh table mon_mods$
refresh table mon_mods$ [message #233727] Fri, 27 April 2007 04:32 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Error while trying to compute Schema Statistics
Next Topic: PARALLEL - Hint not working!!!
Goto Forum:
  


Current Time: Mon Nov 04 15:14:53 CST 2024