Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: segment monitoring, stats, histograms

RE: segment monitoring, stats, histograms

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 4 Feb 2005 07:55:28 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A972F1@exchsen0a1ma>


Jeremiah,

I have all tables in monitoring mode. And I gather stats daily using the USER_TAB_MODIFICATIONS table. Granted that I have mostly OLTP databases, but I have not yet run into a situation where the execution plan changed to something bad because of new stats being gathered. Monitoring has not impacted DML operations. And we perform a full stats gather - I never sample. I don't gather histogram stats as I have not had the need yet.

Runs fine for us.

Hope this helps.

Tom

-----Original Message-----

From: Jeremiah Wilton [mailto:jeremiah_at_ora-600.net] Sent: Thursday, February 03, 2005 7:09 PM To: oracle-l_at_freelists.org
Subject: segment monitoring, stats, histograms

What is the current state of the art WRT CBO best practices?

I'm working on 9.2.0.4 and considering the 'automated statistics gatherin=
g' approach.  This involves turning on monitoring for any and all tables =
that need to ever have stats updated, then periodically running dbms_stat=
s in gather_stale mode.

How is this working for people? Does monitoring impact DML operations, a= nd if so, how much?

Does this approach make any kind of intelligent decisions about sample si= zes and block sampling?

When histograms are present, does this approach always/never/sometimes re= generate the histogram with the correct number of buckets?

Does it seem to reliably choose the correct tables to analyze?

--

Jeremiah Wilton
ORA-600 Consulting
http://www.ora-600.net

--

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

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 04 2005 - 07:58:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US