System Statistics - Troubleshooting Oracle Performance - page 193

To avoid gathering workload statistics during a period that provides values that are unrepresentative of the load,
I see only two approaches. Either gather workload statistics over a period of several days or produce charts based on
much shorter periods (for example, 10 minutes), as in Figure 7-3 , to get values that make sense. I usually advise the
latter because averages computed over several days may be very misleading when the workload changes considerably
over that same period of time. In addition, with shorter periods, you also get a useful view of the system performance
at the same time.
Another advantage of using the approach based on short intervals gathered for several days or weeks is that
it forces you to not immediately change the system statistics in the data dictionary. In fact, when gathering system
statistics, it's much better to gather them in a backup table and check them for consistency. Then, if they're okay,
import them into the data dictionary.
For example, based on the charts shown in Figure 7-3 , I suggest using the average values for mbrc , mreadtim ,
and sreadtim and using the maximum values for maxthr and slavethr . Then a PL/SQL block like the following one
might be used to manually set the workload statistics. Note that before setting the workload statistics with the
set_system_stats procedure, the old set of system statistics is deleted with the delete_system_stats procedure:

BEGIN
dbms_stats.delete_system_stats();
dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 772);
dbms_stats.set_system_stats(pname => 'SREADTIM', pvalue => 5.5);
dbms_stats.set_system_stats(pname => 'MREADTIM', pvalue => 19.4);
dbms_stats.set_system_stats(pname => 'MBRC', pvalue => 53);
dbms_stats.set_system_stats(pname => 'MAXTHR', pvalue => 1136136192);
dbms_stats.set_system_stats(pname => 'SLAVETHR', pvalue => 16870400);
END;

This method of manually setting system statistics could also be used if different sets of workload statistics are
needed for different periods of the day or week. It must be said, however, that I have never come across a case that
required more than one set of workload statistics.

Choosing Between Noworkload Statistics and Workload Statistics
Choosing between the two types of available system statistics is about choosing between simplicity and control.
If simplicity is key, you may want to choose noworkload statistics. This is because, as described in the previous
sections, noworkload statistics are much easier to gather.

■ the absolute simplest approach is to choose the default statistics, which you can do with a call to
delete_system_stats . For some databases, these default statistics may be all you need.

Note

However, by choosing the simple approach of using noworkload statistics, you lose control of
two specific features:

db_file_multiblock_read_count
initialization parameter may impact some of the estimations performed by the query
optimizer. As described in Chapter 9, this is suboptimal. With workload statistics, the role
played by that parameter is replaced by the mbrc statistic.

• When using noworkload statistics, the value of the

maxthr and slavethr statistics, can you control the • Only with workload statistics, through the