RE: System Statistics...do I need them? - (DBMS_STATS.GATHER_SYST EM_STATS)
- From: "Murching, Bob" <bob_murching@xxxxxxxxx>
- To: "'cmarquez@xxxxxxxxxxxxxxxx'" <cmarquez@xxxxxxxxxxxxxxxx>, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Jun 2005 17:21:28 -0400
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@xxxxxxxxxxxxxxxx]
Sent: Wednesday, June 29, 2005 4:16 PM
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
- » RE: System Statistics...do I need them? - (DBMS_STATS.GATHER_SYST EM_STATS)
