Oracle determines the expired statistics

After a period of time, as the database object is modified, statistics must be collected regularly. To determine the database objects that require new database statistics, the oracle database provides

After a period of time, as the database object is modified, statistics must be collected regularly. To determine the database objects that require new database statistics, the oracle database provides

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 monitoring isenabled by default when STATISTICS_LEVEL is set toTYPICAL orALL.

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. When STATISTICS_LEVEL is set to TYPICAL or ALL, the table monitoring feature is enabled by default.

Monitoring tracks the approximate number of INSERTs, UPDATEs, andDELETEs for that table andwhether the table has been truncated since the last time statisticswere gathered. you can access information about changes of tablesin theUSER_TAB_MODIFICATIONS view. following adata-modification, there may be a few minutes delay while OracleDatabase propagates the information to this view. use theDBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure 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 query the USER_TAB_MODIFICATIONS view to obtain 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.

When processing procedures gather new Login tables with stale statistics when statistics is set to gather stale orGATHERAUTO. If a monitored table has been modified more than10 %, then these statistics are considered stale and login.

When the OPTIONS attribute is set to GATHER STALEor GATHERAUTO, GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS collects new statistics for tables with expired statistics. If a monitored table has been modified more than 10%, 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