RE: segment monitoring, stats, histograms

  • From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
  • To: "'jeremiah@xxxxxxxxxxx'" <jeremiah@xxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 4 Feb 2005 07:55:28 -0500 

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@xxxxxxxxxxx] 
Sent: Thursday, February 03, 2005 7:09 PM
To: oracle-l@xxxxxxxxxxxxx
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


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2005