RE: System Statistics...do I need them? - (DBMS_STATS.GATHER_SYST EM_STATS)

From: Murching, Bob <bob_murching_at_BUDCO.com>
Date: Wed, 29 Jun 2005 17:21:28 -0400
Message-ID: <FDE18F41BE19D611A5AE00306E110E0F0F08C905@budco_exch1.budco.com>

I've found that enabling system statistics (in 10g) can change execution plans dramatically by altering how Oracle costs CPU versus I/O. In particular, the optimizer seems very sensitive to mreadtim and sreadtim, and these values can vary dramatically depending on server or storage subsystem load at the time you collect system statistics. Yes, absolutely, things can run worse than before.  

My strong advice is to roll out system statistics carefully, preferably as part of a database migration or upgrade (where their impact can be evaluated on an application-by-application or query-by-query basis), and be prepared to return to defaults if things go sideways. Our experience has been that there is a lot of potential in the collection and use of system statistics, but the general concept would work much better if they continually were adjusted on the fly as storage throughput goes through peaks and valleys.

---

From: Marquez, Chris [mailto:cmarquez_at_collegeboard.org] Sent: Wednesday, June 29, 2005 4:16 PM
To: oracle-l_at_freelists.org
Subject: System Statistics...do I need them? -
(DBMS_STATS.GATHER_SYSTEM_STATS)
Any one have advise, references/links/docs, and procedures for using, colleting, and maintaining System Statistics
(DBMS_STATS.GATHER_SYSTEM_STATS).
I have never used them but am looking to se if I can *help* further improve the performance of database application code. I will not re-write (the application) bad sql but as a DBA I do feel obligated to maintain and improve the database or create the best playing filed possible (for the bad sql ;o) ).

By not having System Statistics am I not giving the database code a better chance?

Thanks

PS
I believe (know) nothing is free in life nor with Oracle...there are no "silver bullets".
Having any stats is an attempt and will change sql plans...for better *OR* for worse.
I assume this rule applies to having System Statistics, no? Simply...what is the chance things run worse with System Statistics?

PPS I found these;

Doc ID:         Note:149560.1
Subject:        Collect and Display System Statistics (CPU and IO) for CBO

usage

Doc ID:         Note:153761.1
Subject:        Scaling the System to Improve CBO optimizer

Chris Marquez
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 29 2005 - 17:26:55 CDT