Blog - comments

Can someone please forward me all the netbackup commands with detail information I want CLI.

Shekhar D

thanks like it

internet shop
Thanks,I have installed AV server successfully but agent deployment failed on windows2008 OS. After ...
Silvere
Hi Eyal, thanks for your comment. I'm not sure to understand what you mean by predicate here but let...
Hi Greg, thanks for your comment. No, it doesn't matter because dbcc checkdb will issue an internal ...
Blog Yann Neuhaus How to avoid strange figures in the Oracle optimizer system statistics

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC 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 our blog 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 have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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

Rate this blog entry:
1

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 Oracle Grid Control as well as several open source solutions (MySQL, Unix/Linux, etc.). Yann Neuhaus is Oracle Certified Expert (OCE) Cluster 11g, Oracle RAC Certified Expert, MySQL 5.0 DBA certified, and Red Hat Certified Engineer. Prior to joining dbi services, Yann Neuhaus was Principal Consultant at Trivadis in Basel, where he was also responsible for the delivery and quality management of large infrastructure projects. He also worked as IT Database / Network Administrator for IFS France. Yann Neuhaus holds an Engineer's Degree in Computer Science from the University of Technology of Belfort-Montbéliard (F) as well as a Master in Business Engineering of the EM Strasbourg Business School (F). His branch-related experience covers Financial Services / Banking, Chemicals & Pharmaceuticals, Transport & Logistics, Retail, Food, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Thursday, 21 August 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter