optimizer system statistics

Optimizer system statistics

Optimizer System Statistics is a set of parameters defining the various system properties such as processor speed, the average number of simultaneous read blocks (multiblock read count), etc. As well as such factors as the time of reading single block from the disk and simultaneous readout of the set of blocks (multiblock read time).

why



Oracle query optimizer uses these statistics in his calculations of the optimal plan for executing queries, it uses the values of these parameters of the system for calculating the I/O Cost and CPU Cost of the plan, that affects the final query execution plan. Such indicators as a small bandwidth of the I/O subsystem, a great multiblock read time or the low value of the average multiblock read count increase the cost of plans using a full table scan or leads to choosing nested loops rather than hash joins in optimizer decisions. In contrast high values of these parameters will lead to lower prices of the full scan operations and using hash joins.

how



Oracle does not automatically collect system statistics, but he has a set of default values, which he uses in the absence of the collected statistics. But not all system parameters have default values, but the main ones. Collect, edit and view system statistics can be done using the system package for statistical data management DBMS_STATS under schema SYS. You must be assigned the DBA  role or GATHER_SYSTEM_STATISTICS role to work with system statistics.



There are two possible types of system statistics:

• Noworkload: All databases come bundled with a default set of noworkload statistics, but they can be replaced with more accurate information. When gathering noworkload stats, the database issues a series of random I/Os and tests the speed of the CPU. As you can imagine, this puts a load on your system during the gathering phase.
  

  EXEC DBMS_STATS.gather_system_stats;

• Workload: When initiated using the start/stop or interval parameters, the database uses counters to keep track of all system operations, giving it an accurate idea of the performance of the system. If workload statistics are present, they will be used in preference to noworkload statistics.
  

  -- Manually start and stop to sample a representative time (several hours) of system activity.
  EXEC DBMS_STATS.gather_system_stats('start');
  EXEC DBMS_STATS.gather_system_stats('stop');
  
  -- Sample from now until a specific number of minutes.
  DBMS_STATS.gather_system_stats('interval', interval => 180); 
  

Your current system statistics can be displayed by querying the AUX_STATS$ table.

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                           1074
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.

SQL>

If you are running 11.2.0.1 or 11.2.0.2 then check out MOS Note: 9842771.8.
The DELETE_SYSTEM_STATS procedure will delete all workload stats and replace previously gathered noworkload stats with the default values.

EXEC DBMS_STATS.delete_system_stats;

You only need to update your system statistics when something major has happened to your systems hardware or workload profile.

There are two schools of thought about system stats. One side avoid the use of system statistics altogether, favoring the default noworkload stats. The other side suggests providing accurate system statistics. The problem with the latter, is it is very difficult to decide what represents an accurate set of system statistics. Most people seem to favor investigation of systems using a variety of methods, including gathering system stats into a stats table, then manually setting the system statistics using the SET_SYSTEM_STATS procedure.

EXEC DBMS_STATS.set_system_stats('iotfrspeed', 4096);

The available parameter names can be found here.
I would say, if in doubt, use the defaults.