Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

Home
Home This is where you can find all the blog posts throughout the site.
Categories
Categories Displays a list of categories from this blog.
Tags
Tags Displays a list of tags that has been used in the blog.
Bloggers
Bloggers Search for your favorite blogger from this site.

Posted by Yann Neuhaus on Monday, 03 January 2011 in Development & Performance

Hits: 4984
0 Comments
Subscribe to this entry
Print
PDF

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

Tagged in: I/O Migration Multi block read Oracle 11g Oracle optimizer Single block read System statistics

Rate this blog entry:

Oracle Cluster 11g R2 listener down - que faire ?
NetBackup command lines - Tips

How to handle mutex issues after migrating to Oracle 11.1 - Database Administration & Monitoring Dec 10
Getting rid of network acces issues after migrating to Oracle 11.2 - Database Administration & Monitoring Jan 27
Bug while installing Oracle 11.2.0.1 on AIX 6.1 SP2 - Database Administration & Monitoring Feb 05
Take care while upgrading your Oracle DB with "DBUA" - Database Administration & Monitoring Aug 11
Oracle Application Express (APEX) 4.1: a migration guide - Middleware Feb 24

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