How CBO infers stale statistics

Oracle CBO infers STALE statistics primarily from view *_TAB_MODIFICATIONS. This particular view shows the changes in your table but not immediately. Lets check

SQL> create table OBJECTS as select * from dba_objects;
Table created.

SQL> insert into objects select * from dba_objects;
72565 rows created.

SQL> commit;
Commit complete.

SQL> select * from user_tab_modifications where table_name='OBJECTS';
no rows selected

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS

This is because, the monitoring information is in the shared_pool and is flushed by Oracle only at a periodical intervals. When i manually flush it,

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_name,inserts, timestamp from user_tab_modifications where table_name='OBJECTS';
TABLE_NAME                        INSERTS TIMESTAMP
------------------------------ ---------- ---------
OBJECTS                             72565 21-DEC-14

Now i see 72565 records inserted. To further test I run inserts, followed by few deletions.

SQL> insert into objects select * from dba_objects;
72565 rows created.

SQL> delete from objects where rownum<1000;
999 rows deleted.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_name,inserts, deletes from user_tab_modifications where table_name='OBJECTS';
TABLE_NAME                        INSERTS  DELETES
------------------------------ ---------- ----------
OBJECTS                            145130        999

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS

So if a table is under monitoring(YES for MONITORING in DBA_TABLES) the DML's are monitored  which is flushed to the MODIFICATIONS table when we call the procedure DBMS_STAT. FLUSH_DATABASE_MONITORING_INFO manually.

The same flush also happens when GATHER_*_STATS procedure is called, Let's test that. Please note the STALE_STATS is NULL before collecting statistics as shown before.

SQL> exec dbms_stats.gather_table_stats('STATS','OBJECTS');
PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where table_name='OBJECTS';
no rows selected

As the document says, the *_TAB_MODIFICATIONS view is populated with the modifications for tables set MONITORING to TRUE and is purged immediately after the statistics is collected.

Actually the statistics staleness recorded in DBA_TAB_STATISTICS uses this data from *_TAB_MODIFICATIONS to identify if the statistics is stale or not. For example,

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS                        NO

SQL> update objects set owner='SRI';
216696 rows updated.

SQL> commit;
Commit complete.

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS                        NO

SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.

SQL> select table_name, stale_stats from user_tab_statistics where table_name='OBJECTS';
TABLE_NAME                     STA
------------------------------ ---
OBJECTS                        YES

In the above case, initially the STALE_STATS is NO. Then i Updated the whole table. But again when i check the stats is not updated as stale as the monitoring data is not flushed. When i flush it manually, it is set to YES. We can also control for what % of change this STALE_STAT should be changed to YES with the help of STALE_PCT from DBA_TAB_PREFS.