System Statistics - Troubleshooting Oracle Performance - page 192

To avoid manually taking the ending snapshot, it's also possible to set the gathering_mode parameter of
the gather_system_stats procedure to interval . With this parameter, the starting snapshot is immediately taken,
and the ending snapshot is scheduled to be executed after the number of minutes specified by a second parameter
named interval . The following command specifies that the gathering of statistics should last 10 minutes:

dbms_stats.gather_system_stats(gathering_mode => 'interval',
interval => 10)

Note that the execution of the preceding command doesn't take 10 minutes. It just takes the starting snapshot and
schedules a job to take the ending snapshot. You can see the job by querying, for example, the user_scheduler_jobs view.

■ Because of bug 9842771, workload system statistics, specifically the values of sreadtim and mreadtim , are
broken in version 11.2.0.1 and version 11.2.0.2. to fix the problem, you can install patch 9842771. if you can't install that
patch, then as a workaround you can set the value of sreadtim and mreadtim manually (the code example later in this
section shows how to set those values).

Caution

The main problem in gathering workload statistics is choosing the gathering period. In fact, most systems experience
a load that is anything but constant, and therefore, the evolution of workload statistics, except for cpuspeed , is equally
inconstant. Figure 7-3 shows the evolution of workload statistics that I measured on a production system. To produce the
charts, I gathered workload statistics for about four days at intervals of one hour. Consult the system_stats_history.sql
and system_stats_history_job.sql scripts for examples of the SQL statements I used for that purpose.