Many of us use Oracle in there day to day software engineers life and many of us might have used Oracle 9i’s MONITORING ALTER TABLE ... MONITORING clause to monitor the table for DML operations on table. However with the death of CBO (Cost Based Optimizer) in Oracle 10g, MONITORING clause is also gone.
MONITORING is deprecated the alter statement will work but has no effect.
However the table monitoring is enabled by default and governed by STATISTICS_LEVEL level parameter (The default value is TYPICAL, so automatic statistics gathering is enabled.)
There are three possible values for STATISTICS_LEVEL
BASIC – Very basic table monitoring
TYPICAL – Default and sufficient for statistics gathering
ALL – very advanced statistics collections, Might affect on DB performance.
To view the result of table monitoring you can query *_TAB_MODIFICATIONS (*-> DBA/ALL)
To view the current STATISTICS_LEVEL
SHOW PARAMETER statistics_level
To set the STATISTICS_LEVEL
ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;
To see what is all is collected based on STATISTICS_LEVEL values use following query.
SELECT statistics_name,
session_status
system_status, activation_level, session_settable
FROM v$statistics_level
ORDER BY statistics_name;
e.g.
SQL> ALTER SYSTEM SET statistics_level=basic;
System altered.
SQL> SELECT statistics_name, session_status, system_status,
activation_level, session_settable FROM v$statistics_level
ORDER BY statistics_name;
I hope this would help.
I will write about DBMS_STATS and it’s usage in next post.
Subscribe to Email to receive all the FREE updates and Business Objects Interview Questions and Answers FREE!!
Related posts:
