The dbms_stats.gather_system_stats procedure measures important timings within the database and adjusts the optimizers propensity to choose indexes vs. full-scans.

Telling Oracle about your system timing provides the SQL optimizer with important information thet will tune your SQL by allowing the CBO to choose the optimal execution plan.

The dbms_stats.gather_system_stats procedure is especially useful for multi-mode Oracle shops that run OLTP during the day and DSS at night.  You invoke the dbms_stats.gather_system_stats procedure as an elapsed time capture, making sure to collect the statistics during a representative heavy workload:

execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

The data collection mechanism of the dbms_stats.gather_system_stats procedure works in a similar fashion to my script that measures I/O times to optimizer the optimizer_index_cost_adj parameter.  The dbms_stats.gather_system_stats also related to the under-documented  _optimizer_cost_model parameter and your db_file_multiblock_read_count setting.

The output from dbms_stats.gather_system_stats is stored in the aux_stats$ table and you can query it as follows:

select pname, pval1 from sys.aux_stats$;
Here are the data items collected by dbms_stats.gather_system_stats:

The dbms_stats.gather_system_stats procedure is very similar to my script for setting optimizer_index_cost_adj, where I compare the relative costs of sequential and scattered read times:
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
   ) * 100 c5
   dba_hist_system_event a,
   dba_hist_system_event b
   a.snap_id = b.snap_id
   a.event_name = 'db file scattered read'
   b.event_name = 'db file sequential read';

Here is sample output from a real system showing an empirical test of disk I/O speed.  We always expert scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential prefetch according to out setting for db_file_multiblock_read_count:  Below we see a starting value for optimizer_index_cost_adj based on the relative I/O costs, very similar to dbms_stats.gather_system_stats:
- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)
- starting setting for optimizer_index_cost_adj at 36:

C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
13,824     5,072      13         86         36

Need more Oracle Tuning Details?

Tuning Oracle requires many years of experience and I have codified all of my Oracle tuning tips in my 950 page book "Oracle Tuning: The Definitive Reference".  You can get it for 30%-off and get instant access to the Oracle tuning scripts download here: