Oct 22, 2018

FLUSH_DATABASE_MONITORING_INFO Procedure

A FLUSH_DATABASE_MONITORING_INFO is Procedure in DMBS_STATS package. This procedure flush monitoring information for all tables. Corresponding entries in the

*_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views are updated immediately.

These views are populated only for tables with the MONITORING attribute. They are intended for statistics collection over a long period of time.
For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur.
Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate these views with the latest information.
The ANALYZE_ANY system privilege is required to run this procedure.

exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

after executing above package, then below query will give you updated data.

select table_owner,table_name,inserts,updates,deletes from DBA_TAB_MODIFICATIONS;

How to run this package?
e.g.,

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

Note that when you are running gather stats procedure, all stats will be collected to *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views. The above call is only in such exception cases.

GATHER_TABLE_STATS difference between 11g and 12c 

No comments:

Post a Comment

Translate >>