dbi services Blog

Welcome to the dbi services Blog! This blog focuses on database infrastructure and middleware topics. It covers technologies such as Oracle, Microsoft SQL Server, MySQL, Sybase, Linux, or Documentum (etc.). The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

How to avoid strange figures in the Oracle optimizer system statistics

Monday, 03 January 2011 Posted by Yann Neuhaus Category Development & Performance 0 comment

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

Tags: Oracle 11g, Oracle optimizer, System statistics, Single block read, Multi block read, Migration, I/O

How to handle mutex issues after migrating to Oracle 11.1 You have recently migrated to Oracle Database 11g Release 1 (11.1) and have had some issues with so called "mutexes"? Don't worry, these nasty sound...
Getting rid of network acces issues after migrating to Oracle 11.2 Are you experiencing network access issues after a migration to Oracle Database 11g Release 2 (11.2)? If yes, you have to make sure the concerned PL/S...
Bug while installing Oracle 11.2.0.1 on AIX 6.1 SP2 Last week I encountered a problem while installing Oracle 11g R2 on AIX 6.1 TL06 SP2. I met all the PreReqs of the installation document, but at the e...
Simulating database-like I/O activity with Flexible I/O You do not want to install or configure swingbench, load runner etc. - just to test the performance of your I/O system based on filesystems? Then Flex...
A SQL statement is slow and suddenly fast? Have a look at "Cardinality Feedback"! While discussing with some tuning gurus, I was made aware about this feature. It is not so far from the adaptive cursor sharing perfectly described in...

About the author

Yann Neuhaus

Yann Neuhaus is Chief Executive Officer (CEO) and Region Manager at dbi services.

He has more than ten years of experience in database and infrastructure management, engineering, and optimization.

He is specialized in Oracle technologies, cluster computing and high availability solutions (Oracle Real Application Clusters RAC, Oracle Data Guard and Oracle Clusterware). His expertise also includes open source solutions (MySQL, Unix/Linux, etc.).

Yann Neuhaus is „Oracle RAC Certified Expert“, „ MySQL 5.0 DBA certified “, and „Red Hat Certified Engineer“.

http://www.dbi-services.com