Hungry DBA .com

 

One of the most common questions that a DBA or Developer may ask when perplexed by the results from the Oracle optimizer is "I've gathered statistics on my relevant tables and indexes so why does Oracle choose not use my index?"  The answer from 9i onwards may be down to missing or inadequate system statistics.

 

In fact I would go as far to say that missing system statistics was the number one reason behind why so many people experienced poor performance in 9i after upgrading from 8i.

 

Before the introduction of system statistics (CPU Costing) in 9i; you could nudge the optimizer to lean more favourably towards the use of indexes by altering the parameters OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING.

 

In addition explain plans would be affected by the parameter DB_FILE_MULTIBLOCK_READ_COUNT (setting this to a high value would encourage Oracle to perform more full table scans.)

 

System statistics attempt to take the guess work out of manual settings of the above parameters and collect real statistics regarding the hardware that your database sits on including CPU and disk speeds.  It will then use this information to set the cost of a single block read; a multi block read and eliminate the impact that the DB_FILE_MULTIBLOCK_READ_COUNT parameter settings have on explain plan costs and use the calculated MBRC instead.

 

The cost model becomes:

 

cost =  (#srds * sreadtm + #mrds * mreadtm + #cpucycles / cpuspeed) / sreadtm

 

where

   #srds                      number of single block reads

   sreadtm                  single block read time

   #mrds                     number of multi block reads

   mreadtm                 multi block read time

   #cpucycles             number of cpu cycles

   cpuspeed                cpu cycles per second

 

System statistics should be gathered during peak load time so Oracle can see how the hardware behaves under stress.  

 

However, The problem (as explained by Jonathan Lewis) is that for various reasons including SAN caches, read-ahead algorithms, etc. Oracle can gather misleading information regarding the performance of your hardware.  For example, some bad code could be protected by a file-system cache which makes multi-block reads appear to be very fast. Collecting system stats at the wrong time therefore encourages the optimizer to believe that multi-block reads really are fast always - so it does more of them.

 

There are also certain rules (at least in 9i) to follow to make effective use of system statistics, namely that mreadtim must be larger than sreadtim and as a rule of thumb must be at least 1.2 times larger. Otherwise Oracle will not make use of the CPU costing and revert to the default I/O costing model.

 

Some authors write that system statistics should be gathered at different times of the day in order to optimize code based on different levels of usage (e.g. OLTP type transactions during the day and batch loading type transactions in the evening).  However, due to the problems highlighted above I tend to stay away from this approach.

 

My preference is to gather the stats once during peak load times using the command:

 

begin dbms_stats.gather_system_stats('start'); end;

 

-- Then after 1 to 2 hours of peak time usage

 

begin dbms_stats.gather_system_stats('stop'); end;

 

If necessary I then manually adjust the figures to ensure mreadtim is larger than sreadtim.

 

For example, for an OLTP type system I would manually set SREADTIM to 2 and MREADTIM to 5 as follows:

 

begin

dbms_stats.set_system_stats('sreadtim',2);

dbms_stats.set_system_stats('mreadtim',5);

end;

 

The above figures should be a pretty good starting point for the majority of OLTP systems; however it is recommended to test and measure performance levels using different values to a point that you are happy.  Once at this stage I would not re-gather system stats again unless there are some major changes occur to your hardware / application (If the system stats are producing optimal explain plans why change them).

 

You can view the system stats settings via the table SYS.AUX_STATS$:

 

select * from sys.aux_stats$;

 

 

In 10g CPU Costing is enabled by default without having to manually gather system statistics; this therefore leads to the question "do we need to manually gather system statistics in 10g?"