Table Monitoring and statistics_level in 10 GB

For versions earlier than 10 Gb, you must use the alter_database/schema_tab_monitoring process of dbms_stats or create/alter table... monitoring enables table monitoring. In 10 Gb, the keywords monitoring and nomonitoring are out of date. If you specify or cancel monitoring for a table in create/alter table, this keyword is ignored, table monitoring features are controlled by statistics_level. When statistics_level is set to basic, table monitoring is disabled. If it is set to typical or all, table monitoring is activated.

 

Statistics_level is typical by default, and table monitoring is activated in 10g. It is strongly recommended that the value of this parameter be typical in 10g. if statistics_level is set to basic, not only tables cannot be monitored, but also the following 10g new functions are disabled:
Ash (Active session History)
Assm (automatic shared memory management)
AWR (automatic workload repository)
ADDM (automatic database diagnostic monitor)

The monitoring result is the approximate number of insert/update/delete records since the previous statistical collection. Of course, this is also related to the number of records maintained in SGA, every 15 minutes, SMON refreshes the statistical results to the data dictionary. You can view the monitoring results using the following DBA/All/user_tab_modifications data dictionary. To view the monitoring results immediately, you can use exec dbms_stats.flush_database_monitoring_info () to refresh the data to the data dictionary. Oracle uses the data of these tables to determine whether the statistical data of the table has expired. If the data of the table changes more than 10%, oracle considers that the statistical data of the table has expired.

Example:
--------
Let us take an example:

Step 1:
-------

Create Table EMP. Its description is as follows

SQL> DESC EMP
SQL> select count (*) from EMP;

Count (*)
----------
14

SQL> select * From user_tab_modifications;

No rows selected

Initially there are 14 rows in EMP.

Step 2:
-------

Set parameter statistics_level = 'typical'

SQL> alter system set statistics_level = 'typical ';

System altered.

Step 3:
-------

Insert additional 14 rows. This will increase the data in EMP by 50% and
Therefore the statistics in EMP will be regarded as stale by Oracle.

SQL> insert into EMP select * from EMP;

14 rows created.
SQL> commit;

Step 4:
-------

SQL> exec dbms_stats.flush_database_monitoring_info ();

PL/SQL procedure successfully completed.

SQL> select * From user_tab_modifications;

Table_name partition_name
------------------------------------------------------------
Subpartition_name inserts updates deletes timestamp tru
------------------------------------------------------------------------
EMP
14 0 0 16--0-03 No

Step 5:
-------

If a monitored table has been modified more than 10%, then these
Statistics are considered stale

Analyze the tables whose statistics have become stale using the following command:

Execute dbms_stats.gather_schema_stats ('rajiv ',
Null,
False,
'For all columns size 1 ',
Null,
'Default ',
True,
Null,
Null,
'Gather stale ',
'LIST ');

Step 6:
-------

Query dba_tab_modifications to check whether the table has been analyzed
Or not?

SQL> select * From user_tab_modifications;

No rows selected

No rows in dba_tab_modifications indicates that the table is analyzed.