Table monitoring is by default activated if
STATISTICS_LEVEL system parameter is set to
Oracle keeps table monitoring data in memory, then flush it into the dictionary when
FLUSH_DATABASE_MONITORING_INFO is called. These datas are mainly used to determine if objects need new statistics.
Quote from Oracle
Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table and whether the table has been truncated since the last time statistics were gathered. You can access information about changes of tables in the
USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle Database propagates the information to this view. Use the
FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to
GATHER STALE or
GATHER AUTO. If a monitored table has been modified more than
STALE_PERCENT (default 10%), then these statistics are considered stale and gathered again.
This procedure flushes in-memory monitoring information for all tables in the dictionary. Corresponding entries in the
*_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 in those views. Because the
GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.
ANALYZE_ANY system privilege is required to run this procedure. Without this privilege, this procedure can still be executed with an alternative explain below in this article.
Check table monitoring info
Let's see how it works :
If you don't have the privilege to execute
FLUSH_DATABASE_MONITORING_INFO, you can implicitly excute this procedure with
GATHER_*_STATS if you run it on a least one table which the folowing contition:
- Partitioned table
INCREMENTALpreference parameter to
PUBLISHpreference parameter to
- Gather statistique with
Therefore we can create a dummy table in order to implicitly call
FLUSH_DATABASE_MONITORING_INFO as follow.
Here we go, we can now call
GATHER_TABLE_STATS without needing
ANALYSE_ANY system privilege.
Enjoyed this article? Please like it or share it.