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:
 
select
   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)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm