From oracle-l-bounce@freelists.org Fri Jun 18 16:42:10 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5ILft006243 for ; Fri, 18 Jun 2004 16:42:05 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5ILfj606229 for ; Fri, 18 Jun 2004 16:41:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C096472C048; Fri, 18 Jun 2004 16:25:58 -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 06005-28; Fri, 18 Jun 2004 16:25:58 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1B86272C027; Fri, 18 Jun 2004 16:25:58 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 18 Jun 2004 16:24:38 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8AF7E72C00D for ; Fri, 18 Jun 2004 16:24:38 -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 06284-06 for ; Fri, 18 Jun 2004 16:24:38 -0500 (EST) Received: from smtp804.mail.ukl.yahoo.com (smtp804.mail.ukl.yahoo.com [217.12.12.141]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id CFFAD72C00B for ; Fri, 18 Jun 2004 16:24:37 -0500 (EST) Received: from unknown (HELO Primary) (oracle-l@freelists.org@81.154.99.82 with poptime) by smtp804.mail.ukl.yahoo.com with SMTP; 18 Jun 2004 21:45:01 -0000 Message-ID: <011001c4557d$8390bbb0$7102a8c0@Primary> From: "Jonathan Lewis" To: References: <20040616221206.87761.qmail@web50605.mail.yahoo.com> Subject: Re: DBMS_STATS [resend chomped version] Date: Fri, 18 Jun 2004 22:45:00 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3097 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org One - don't let the database work out a sample size, at best it will waste time you don't have. Two - don't let the database decide which columns are skewed, at best it will generate far too many histograms. Three - (for Jared) if you analyze for all indexed columns you've almost certainly done it wrong: some of your unindexed columns may need histograms, most of your indexed ones won't. On the other hand - if you have several hours of free time for analyzing, and an overpowered machine, you don't often do much damage by analyzing to extremes. Optimum use of stats: Most tables need only a small percentage estimate A few columns (time or sequence-based) need very regular correction A few columns need histograms - designed to highlight the skewed data pattern. Small tables may as well have a compute - as a small error in the cardinality of the data set from a small table can produce a significant percentage error in cost estimate as it cascades through a plan - and small tables can be analyzed very quickly. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: "Barbara Baker" To: Sent: Wednesday, June 16, 2004 11:12 PM Subject: RE: DBMS_STATS [resend chomped version] : Regarding #1: : (9.2.0.4 Solaris 9) : We have a recommendation from the sw vendor to compute : with histograms using : : exec dbms_stats.gather_schema_stats(ownname => : 'SCOTT', estimate_percent => : dbms_stats.auto_sample_size, method_opt => 'for all : columns size skewonly', cascade => true); : : as an initial method to gather the stats. I did a bit : of looking and a bit of testing, and it looks good to : me. Down side: it did take several hours to gather : the initial set of stats. : : What are other folks doing with histograms?? : : Barb : ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------