Table monitoring is by default activated if STATISTICS_LEVEL system parameter is set to TYPICAL (or ALL).
Oracle keeps table monitoring data in memory, then flush it into the dictionary when DBMS_STATS . FLUSH_DATABASE_MONITORING_INFO is called. These datas are mainly used to determine if objects need new statistics.
Quote from Oracle
Table monitoring
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 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
The GATHER_DATABASE_STATS or 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.
FLUSH_DATABASE_MONITORING_INFO procedure
This 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 in those views. Because the GATHER_*_STATS procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.
Usage note
The 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 :
-- Create new row in table
INSERT INTO test_part_tab VALUES (3,'C1', SYSDATE) ;
COMMIT ;
/*
1 row created.
Commit completed.
*/
-- Check modification table info (1)
SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;
/*
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
--------------- --------------- ------- ------- ------- --------------- ----------
*/
-- Flush table monitoring info
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
/*
PL/SQL procedure successfully completed.
*/
-- Check modification table info (2)
SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;
/*
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
--------------- --------------- ------- ------- ------- --------------- ----------
TEST_PART_TAB 1 0 0 16-MAR-12 NO
TEST_PART_TAB P_LESS04 1 0 0 16-MAR-12 NO
*/
-- Check table stats (2)
SELECT table_name object_name, partition_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB';
/*
OBJECT_NAME PARTITION_ NUM_ROWS LAST_ANALYZED STALE_STATS
------------------------------ ---------- -------- -------------------- -----------
TEST_PART_TAB 15 2012/03/13 14:30:26 YES
TEST_PART_TAB P_LESS00 0 2012/03/13 14:30:26 NO
TEST_PART_TAB P_LESS02 2 2012/03/13 14:30:26 NO
TEST_PART_TAB P_LESS04 1 2012/03/13 14:30:26 YES
TEST_PART_TAB P_LESS06 12 2012/03/13 14:30:26 NO
TEST_PART_TAB P_LESS08 0 2012/03/13 14:30:26 NO
*/
-- Execute stats gathering
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'MICHEL', TabName=>'TEST_PART_TAB', Degree=>DBMS_STATS.AUTO_DEGREE, Granularity=>'AUTO') ;
/*
PL/SQL procedure successfully completed.
*/
-- Check modification table info (3)
SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;
/*
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
--------------- --------------- ------- ------- ------- --------------- ----------
*/
-- Check table stats (3)
SELECT table_name object_name, partition_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics WHERE table_name = 'TEST_PART_TAB';
/*
OBJECT_NAME PARTITION_ NUM_ROWS LAST_ANALYZED STALE_STATS
--------------------------- ---------- -------- -------------------- -----------
TEST_PART_TAB 16 2012/03/16 16:41:52 NO
TEST_PART_TAB P_LESS00 0 2012/03/13 14:30:26 NO
TEST_PART_TAB P_LESS02 2 2012/03/13 14:30:26 NO
TEST_PART_TAB P_LESS04 2 2012/03/16 16:41:52 NO
TEST_PART_TAB P_LESS06 12 2012/03/13 14:30:26 NO
TEST_PART_TAB P_LESS08 0 2012/03/13 14:30:26 NO
*/
FLUSH_DATABASE_MONITORING_INFO alternative
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
INCREMENTAL preference parameter to TRUEPUBLISH preference parameter to TRUE- Gather statistique with
GRANULARITY=>'AUTO' and ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE
Therefore we can create a dummy table in order to implicitly call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO as follow.
-- Create EXEC_FLUSH_DATABASE_MON_INFO table
CREATE TABLE EXEC_FLUSH_DATABASE_MON_INFO (DUMMY NUMBER(1) NOT NULL)
PARTITION BY RANGE (DUMMY) (PARTITION P0 VALUES LESS THAN (0)) ;
/*
Table created.
*/
-- Activate INCREMENTAL parameter
EXEC DBMS_STATS.SET_TABLE_PREFS (OwnName=>'HR', TabName=>'EXEC_FLUSH_DATABASE_MON_INFO', pname=>'INCREMENTAL', pvalue=>'TRUE') ;
/*
PL/SQL procedure successfully completed.
*/
-- Execute stats gathering
EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'HR', TabName=>'EXEC_FLUSH_DATABASE_MON_INFO', Degree=>DBMS_STATS.AUTO_DEGREE, Granularity=>'AUTO') ;
/*
PL/SQL procedure successfully completed.
*/
-- Check table stats
SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;
/*
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
--------------- --------------- ------- ------- ------- --------------- ----------
TEST_PART_TAB 1 0 0 16-MAR-12 NO
TEST_PART_TAB P_LESS04 1 0 0 16-MAR-12 NO
*/
Here we go, we can now call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO through GATHER_TABLE_STATS without needing ANALYSE_ANY system privilege.
Further reading
Enjoyed this article? Please like it or share it.
Please connect with one of social login below (or fill up name and email)