Oracle determines the expired statistics

Youmust regularly gather statistics on database objects as thesedatabase objects are modified over time. To determine whether agiven database object needs new database statistics, OracleDatabase provides a table monitoring facility. This limit isenabled by whenSTATISTICS_LEVELIs setTYPICALOrALL.

After a period of time, as the database object is modified, statistics must be collected regularly. To determine database objects and require new database statistics, oracle database provides a table monitoring feature. WhenSTATISTICS_LEVEL is set to TYPICALOrWhen ALL is enabled, the table monitoring feature is enabled by default.

 

Monitoring tracks the approximate numberINSERTS,UPDATES, andDELETES for that table andwhether the table has been truncated since the last time statisticswere gathered. You can access information about changes of tablesinUSER_TAB_MODIFICATIONSView. Following adata-modification, there may be a few minutes delay while OracleDatabase propagates the information to this view. UseDBMS_STATS.FLUSH_DATABASE_MONITORING_INFOProcedure toimmediately reflect the outstanding monitored information kept inthe memory.

 

The table monitoring feature tracks the approximate number of insert, update, and delete operations of the table after the last statistical collection, and whether the table is truncate. You can queryThe USER_TAB_MODIFICATIONS view obtains information about table changes. After the data is modified, there may be some delay in obtaining the modification information through USER_TAB_MODIFICATIONS. Use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to immediately Save the delay information to the memory.

 

TheGATHER_DATABASE_STATSOrGATHER_SCHEMA_STATSProcedures gather new statisticsfor tables with stale statistics whenOPTIONSParameter is setGATHER STALEOrGATHERAUTO. If a monitored table has been modified more than10 %, then these statistics are considered stale and gatheredagain.

 

When the attribute OPTIONS is set to GATHER STALEOrGATHERAUTO,GATHER_DATABASE_STATSOrGATHER_SCHEMA_STATSCollect new statistics for tables with expired statistics. If a monitored table exceeds 10%, the statistics are deemed to have expired and need to be collected again.


 

Some experiments are as follows:

SQL> select * from test01;

A B
--------------------
21 10
9 10
22 10
23 10
24 10
25 10
1 1
2 2
44 44
55

10 rowsselected. ------ 10 rows of table data

SQL> select * fromuser_tab_modifications;

No rowsselected ------ collect statistics on the test database before the experiment. The result is blank.

SQL> insert into test01 values (66,66 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from user_tab_modifications;

No rowsselected ------ table modifications are not displayed in time

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; ------ keep the modified information to the memory

PL/SQL procedure successfully completed.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 10 0 NO ------ 1 represents the previous insert

 

To better display the monitoring information, perform the update, delete, and truncate operations:

SQL> update test01 set a = 77 where B = 66;

1 row updated.

SQL> commit;

Commit complete.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 1 0 0 NO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 1 0 NO

SQL> delete from test01 where a = 77;

1 row deleted.

SQL> commit;

Commit complete.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 1 0 NO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 1 1 1 NO

SQL> truncate table test01;

Table truncated.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 1 1 1 NO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
---------------------------------------------------------------
TEST01 1 11YES

 

Collect statistics on the table test01. The monitoring table information is cleared:

SQL> execdbms_stats.gather_table_stats (null, 'test01 ');

PL/SQL procedure successfully completed.

SQL> selecttable_name, INSERTS, UPDATES, DELETES, truncated fromuser_tab_modifications;

No rows selected