Question: I need to monitor my
tables for stale optimizer statistics, and I understand that
the alter table monitoring clause will allow Oracle
to detect when a table need to be re-analyze with
dbms_stats, thereby keeping my statistics fresh. How
do I use alter table monitoring, and how do I tell that my
tables and indexes are being monitored?
Answer: Prior
to 11g, alter table xxx monitoring tracks the
approximate number of DML statements against the table since
the last time statistics were gathered. Information about
how many rows are affected is maintained in the SGA, until
periodically (about every three hours) SMON incorporates the
data into the data dictionary. Oracle has the
dba_tab_modifications view to see these DML updates..
An
example of the "alter table xxx monitoring" clause
is below, a script to tell when a table or index is not
being monitored:
select
table_name,
monitoring
from
user_tables
where
monitoring = 'NO'
order by
monitoring;
select
last_analyzed,
count(*)
from
user_indexes
group by
last_analyzed
order by
last_analyzed desc;
-- *****************************
prompt un-analyzed tables
-- ****************************
select
table_name,
last_analyzed
from
user_tables
where
last_analyzed is NULL;
Important Note!
Starting with 11g and beyond, the
alter table monitoring clause been deprecated and
statistics are collected automatically. Even if you do
specify alter table xxx monitoring, it is ignored.
