User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

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 TRUE
  • PUBLISH 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.

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh