How to avoid strange figures in the Oracle optimizer system statistics

Have you ever noticed strange figures while collecting the Oracle optimizer system statistics ? If so, you need to provide the optimizer with the correct number, as explained in this posting.
Mess in the System statistics with Oracle 11.2: while collecting the system statistics during 30 minutes on an Oracle 11.2.0.1 database on Linux…

exec dbms_stats.gather_system_stats (gathering_mode => ‘interval’,interval => 30);

…we get really strange results in the system stats table:

SQL> select * from sys.aux_stats$;

SNAME               PNAME                               PVAL1 PVAL2
------------------- ------------------------------ ---------- -------------------
SYSSTATS_INFO       STATUS                                    COMPLETED
SYSSTATS_INFO       DSTART                                    07-07-2010 16:35
SYSSTATS_INFO       DSTOP                                     07-07-2010 17:05
SYSSTATS_MAIN       SREADTIM                        17204.089 <==== in ms !!
SYSSTATS_MAIN       MREADTIM                        20661.538 <==== in ms !!
SREADTIM - single block read time : 17204 miliseconds
MREADTIM - single block read time : 20661 miliseconds

An acceptable value for a single block read (SREADTIM) is about 2 to 6 ms. A little bit higher for Multi block reads.

If your applications suffers from this bug (9842771 and 9701256), a workaround will be to set the values to acceptable ranges, for instance 4 ms for a single access and 10 ms for a multi block access :

exec dbms_stats.set_system_stats(pname =>’sreadtim’, pvalue =>4);
exec dbms_stats.set_system_stats(pname =>’mreadtim’, pvalue =>10);

Now, as a last step, you just need to verify:

SQL> select * from sys.aux_stats$;
SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ---------
SYSSTATS_MAIN                  SREADTIM                                4
SYSSTATS_MAIN                  MREADTIM                               10

Good luck with collecting your Oracle system statistics!

Best regards,
Yann