Gathering System Statistics

The System Statistics enable optimizer to generate better execution plan. The Automatic statistic gathering feature of 10g does not gather the system statistics. You have to manually collect System Statistics. There are two options for collecting System Statistics, they are

1. Workload Statistics
2. No Workload Statistics

Workload Statistics: This type of statistics are taken during normal working period. It will not generate any workload. The execution plans are generated based on the workload at the time of submitting the query. For eg. if the system is I/O intensive then the CBO choses an execution plan that is less intensive on I/O.

To gather Workload Statistics

SQL> Exec dbms_stats.gather_system_stats('start')
SQL> Exec dbms_stats.gather_system_stats('stop')
Or
SQL> Exec dbms_stats.gather_system_stats('interval', interval=>N)

where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics and reset to the default noworkload statistics

SQL> Exec dbms_stats.delete_system_stats().

NoWorkload Statistics: Noworkload statistics gather data by submitting random reads against all data files. It gathers I/O transfer speed, I/O seek time and CPU speed. There will be some overhead while gathering statistics. By default oracle uses noworkload statistics. If workload statistics are available oracle ignores noworkload statistics.

To gather no workload statistics

SQL> Exec dbms_stats.gather_system_stats();

Thanks