Skip to content

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

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Application Server

The Application Server posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *