How to collect statistics in an INTERVAL mode

First Check the old values

SQL> select * from sys.aux_stats$;

Create a new and old Statistics Table

SQL> exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'backup_stats');

SQL> exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'current_stats');

Save the old Statistics

SQL> exec dbms_stats.export_system_stats(stattab=>'backup_stats');

Gather the INTERVAL statistics for 30 minutes (or whatever period suites the system)

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode=>'INTERVAL', interval=>30, stattab=>'current_stats');

Wait for the status of COMPLETED which can be check from

SQL> select c1 from sys.newstats;

Then import the statistics

SQL> exec dbms_stats.import_system_stats(stattab=>'current_stats');

Check the new values

SQL> select * from sys.aux_stats$;

We can revert the statistics, if required

SQL> exec dbms_stats.import_system_stats(stattab=>'backup_stats');

If system Statistics are substantially wrong we will get wrong costs for plans and potentially wrong plans as well. It is best to have the right system statistics for our system. If we migrate the database from one system to another, system statistics may no longer be applicable.