• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

FLUSH_DATABASE_MONITORING_INFO Procedure

by admin

As per documentation, the FLUSH_DATABASE_MONITORING_INFO procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS, and *_IND_STATISTICS views are updated immediately, without waiting for the Oracle database to flush them periodically. This procedure is useful when you need up-to-date information on those views. Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.

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 the above package, the below query will give you updated data.

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

How to run this package?

Conside the example shown below:

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 exceptional cases.

Final Thoughts

It is possible to manually flush this data by calling the procedure DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO if you want to get up-to-date information at query time (internally the monitoring data is flushed before all statistics collection operations). You can then see which tables have stale statistics by querying the STALE_STATS column in the USER_TAB_STATISTICS view.

SQL> select table_name,stale_stats from user_tab_statistics;

Tables where STALE_STATS is set to NO, have up to date statistics. Tables where STALE_STATS is set to YES, have stale statistics. Tables where STALE_STATS is not set are missing statistics altogether.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle 12c: Unplug/Plug PDB to new CDB using RMAN Active Database Duplication
  2. Managing Oracle Database Backup with RMAN (Examples included)
  3. How to Backup and Restore Java Classes and Privileges only in Oracle Database
  4. Oracle Database 12c New Feature: Transaction Guard and Application Continuity
  5. Basics of PL/SQL LOOPs
  6. Oracle Database 12c New Feature: SYSRAC administrative privilege
  7. What are the implications of rebuilding indexes in Oracle Database
  8. How to Create a Physical Standby Database by Using SQL and RMAN Commands
  9. How to Install and configure OSWatcher Black Box (OSWbb)
  10. How to Startup/Shutdown PDB’s in Oracle Database 12c

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright