• 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 – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
  2. How to configure Partitioned Block Devices (Non-ASMLIB) And Assign Them To ASM
  3. How to Change the Default Home Page of Oracle HTTP Server
  4. List of OCFS2 threads
  5. Oracle RMAN Pluggable Database Point in Time Recovery
  6. PL/SQL: Palindrome Program
  7. How to load SELinux Module For Oracleasm
  8. Find Oracle RAC OCR & voting disk location
  9. ORA-00214: control file inconsistent with file
  10. DDL Statement Examples in SQL

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright