From oracle-l-bounce@freelists.org Thu Feb 3 20:01:15 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1421Fr7011358 for ; Thu, 3 Feb 2005 20:01:15 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j1421Fem011354 for ; Thu, 3 Feb 2005 20:01:15 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BC1A16CFC7; Thu, 3 Feb 2005 20:00:18 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04295-06; Thu, 3 Feb 2005 20:00:18 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3C6796CEFD; Thu, 3 Feb 2005 20:00:18 -0500 (EST) Message-ID: <20050204005836.30200.qmail@web86901.mail.ukl.yahoo.com> Date: Fri, 4 Feb 2005 00:58:36 +0000 (GMT) From: Connor McDonald Subject: Re: segment monitoring, stats, histograms To: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 15785 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: hamcdc@yahoo.co.uk Precedence: normal Reply-To: hamcdc@yahoo.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: My $0.02 worth based on experience at a current site I'd opt explicit dbms_stats facilities on some/most tables, with monitoring and gather_stale as the fallback option for those without any specific requirement. My main reasons for not relying solely on monitoring and/or auto sample size are: a) I've found that auto histogramming gives a LOT of histograms, many of which upon further inspection were not actually required. COL_USAGE$ is nice to have though to see roughly how things are being accessed though b) I've been very disappointed with auto-sample size. It runs more expensive (because its implemented as repeated iterations with increasing sample sizes). Also, I've found it be poor with skewed column data - the resulting sample sizes are not large enough c) I've been bitten a few times with sampling on indexes - I can't remember the specifics but it tends to get clustering factor and distinct keys a little wrong. So I generally run without cascade and always do 100% samples if possible on the indexes. Our process automatically turns on monitoring for any tables not covered by special requirements, and runs a gather stale at regular intervals (the fewer the better - we try to minimise the amount of statistics runs we do - generally once per release). We've detected no overheads with monitoring (although admittedly we haven't really spent a great deal of time looking for them) Other small exceptions - we've got dynamic sampling set to 2 (because the app uses temp tables heavily) and we've stats on the dictionary (app does lots of parsing, and having dyn sampling set to 2 means dictionary queries also invoked dyn sampling). hth connor --- Jeremiah Wilton wrote: > 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 > ===== Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald@yahoo.com Coming Soon! "Oracle Insight - Tales of the OakTable" "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ------------------------------------------------------------ __________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- http://www.freelists.org/webpage/oracle-l