From oracle-l-bounce@freelists.org Fri Jul 23 16:41:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6NLfNw29988 for ; Fri, 23 Jul 2004 16:41:33 -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 i6NLfD629963 for ; Fri, 23 Jul 2004 16:41:23 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A626372C03D; Fri, 23 Jul 2004 16:20:10 -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 21592-19; Fri, 23 Jul 2004 16:20:10 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E8BE172C01B; Fri, 23 Jul 2004 16:20:09 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 23 Jul 2004 16:18:39 -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 439C972CA00 for ; Fri, 23 Jul 2004 16:18:39 -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 19977-38 for ; Fri, 23 Jul 2004 16:18:39 -0500 (EST) Received: from CWYSMTP01.acxiom.com (cwysmtp01.acxiom.com [198.160.100.127]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C703F72C9C2 for ; Fri, 23 Jul 2004 16:18:38 -0500 (EST) Received: from relay2.Corp.Acxiom.net (unverified [139.61.199.172]) by CWYSMTP01.acxiom.com (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Fri, 23 Jul 2004 16:45:00 -0500 Received: from dngmsc01.Corp.Acxiom.net (unverified [10.76.40.237]) by relay2.Corp.Acxiom.net (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Fri, 23 Jul 2004 16:45:00 -0500 Received: by dngmsc01.dg.acxiom.com with Internet Mail Service (5.5.2657.72) id ; Fri, 23 Jul 2004 16:44:59 -0500 Message-ID: <433A07749711884D8032B6A0AB115262C2BCA3@conmsx07.corp.acxiom.net> From: Wolfson Larry - lwolfs To: "'oracle-l@freelists.org'" Subject: RE: Creating Histograms Date: Fri, 23 Jul 2004 16:42:14 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 5920 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lawrence.wolfson@acxiom.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org We tend to create a stats table for each application STATID COUNT(*) ------------------------------ ---------- APR11SUN2000 6738 APR12MON2000 6738 APR13TUE2000 6738 APR14WED2000 7461 Analyze -client requirement APR15THU2000 7423 APR16FRI2000 7423 APR17SAT2000 7423 APR18SUN2000 6793 APR19MON2000 6793 APR20TUE2000 6793 APR21WED2000 7461 Analyze -client requirement APR22THU2000 7423 APR23FRI2000 7423 APR24SAT2000 7423 D D T A A I T Y M E E Hmmmm, we seem to loose stats on Thursdays and Sundays. "Can I keep multiple sets of statistics for a single schema? Is that what the statid is for?" YES You export(wish they used different term like "save") them to a stat table and then you can exp them to a file you can use on another DB. If you create under SYS you could make application name part of the STATID. I think someone explained you may not always get same plan. One reason is CBO still looks at table HWM. But it's still worth doing. We've taken stats from test systems (close to production) and put them in production. May save time on upgrade or if you're changing the way you generate them. Normally an upgrade is not best time to change your methods. Of course, you back up your current stats first. Larry -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Freeman, Donald Sent: Friday, July 23, 2004 10:38 AM To: oracle-l@freelists.org Subject: RE: Creating Histograms I have Wolfgang's presentation from = http://www.centrexcc.com/SQL%20Tuning%20with%20Statistics.ppt.pdf but = have a question about exporting statistics. I understand that before I = start mucking about I need to save them. And, I'm gathering that each = time before we rerun them in our current Russian Roulette system we = should save them, just 'in case.'=20 When creating the stats table to whom should I grant ownership? I = found an article with an example creating it under SYS schema in the = SYSTEM tablespace. Wolfgang's example creates it in Scott's schema and = tablespace. If this is going to be a production process should I set it = up under an application schema owner? Or does each application schema = owner need their own stats table? Can I keep multiple sets of statistics = for a single schema? Is that what the statid is for? Or, do I need to = export them to save them? I'm a little bit foggy on the big picture = here since I'm not currently doing any of this. Sorry if this is = sounding ignorant! I want to verify that if I export my production statistics to a test = machine I will get the same execution plan regardless of the fact that I = only have 10% of the data on that system. Right? ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- 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 -----------------------------------------------------------------