Table level monitoring is enabled by setting parameter “statistics_level=typical”. The information about DML operations are maintained in data dictionary cache from where SMON will flush the data into the DBA_TAB_MODIFICATIONS & USER_TAB_MODIFICATIONS ( Which we call as data dictionary )
CB0 uses this statistics and generate better execution plans. By default when ever there is 10% changes automatically oracle gather stats on tables
DBA_TAB_MODIFICATIONS is used by oracle to track how many inserts, updates, deletes on the table. It also checks segment has be truncated since the last gather statistics. The information in DBA_TAB_MODIFICATIONS is used by oracle process if the stats is stale or stats are changed by 10%. When ever you gather stats on database, stats will be flushed from this table
when ever you run following package : Count of DML happen to table after gathering stats will be loaded into the table dba_tab_modifications;
exec dbms_stats.flush_database_monitoring_info;
As a user:
select timestamp, inserts, updates, deletes from user_tab_modifications where table_name=‘EMP’;
As a DBA:
select table_name,inserts, updates, deletes, truncated from dba_tab_modifications where table_owner=‘NSPDBA’ and table_name=‘EMP’;
Note:
Above query’s will display you the number of DML happened on table after the stats was gathered on table using dbms_stats
How do we improve gather stats jobs on database ?
when you set CONCURRENT = TRUE; when you set this parameter is true, it will perform concurrent statistics gathering and reduce the overall time taken by gather statistics. This concurrent statistics gathering is controlled by JOB_QUEUE_PROCESSES parameter
generally when you gather statistics more CPU is utilized. make sure concurrent gather statistics is required on database :)
How do you gather stats manually ?
---- Working on it
---Note
In 12c, when you perform direct load or truncate table, stats are not updated, you need to gather stats after :)
---Nikhil Tatineni--
---Oracle in memory---
