Gather_system_stats

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. This is the typical mode we have and it becomes imperative to have these stats good.

We can gather these stats in three different ways:

1. During heavy workload.

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

2. During no workload.

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD')

This actually gathers the IOTFRSPEED and IOSEEKTIM values in addition to CPUSPEEDNW rather than using the default values of 4096 and 10.

3. Setting your own stats.

This is important when testing which stats will be good and then setting them manually.

begin
    dbms_stats.delete_system_stats;
    dbms_stats.set_system_stats('MBRC',        64);
    dbms_stats.set_system_stats('MREADTIM',    10);
    dbms_stats.set_system_stats('SREADTIM',     5);
    dbms_stats.set_system_stats('CPUSPEED',  2000);
    dbms_stats.set_system_stats('MAXTHR',  262144);
    dbms_stats.set_system_stats('SLAVETHR', 65536);
    dbms_stats.set_system_stats('SLAVETHR', 47000);
    dbms_stats.set_system_stats('SLAVETHR', 16384);
end;
/

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:

No Workload (NW) stats:

• CPUSPEEDNW - CPU speed
• IOSEEKTIM - The I/O seek time in milliseconds
• IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related stats:

• SREADTIM  - Single block read time in milliseconds
• MREADTIM - Multiblock read time in ms
• CPUSPEED - CPU speed
• MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
• MAXTHR - Maximum I/O throughput (for OPQ only)
• SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)