From oracle-l-bounce@freelists.org Thu Mar 10 10:05:25 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2AG5Pto023931 for ; Thu, 10 Mar 2005 10:05:25 -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 j2AG5Oem023923 for ; Thu, 10 Mar 2005 10:05:24 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3D99D8457F; Thu, 10 Mar 2005 10:03:41 -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 05553-05; Thu, 10 Mar 2005 10:03:41 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B85BB84590; Thu, 10 Mar 2005 10:03:40 -0500 (EST) Message-ID: <42306163.6010509@centrexcc.com> Date: Thu, 10 Mar 2005 08:01:55 -0700 From: Wolfgang Breitling Organization: Centrex Consulting Corporation User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707) X-Accept-Language: en-us, en MIME-Version: 1.0 To: CMarquez@aarp.org Cc: oracle-l@freelists.org Subject: Re: Mutating (SQL) execution plan!?...is that possible...strangest thing I have every seen References: <7E412C164E6ECB468834A39F31E6E0D4074B5E1A@mbs06dc.na.aarp.int> In-Reply-To: <7E412C164E6ECB468834A39F31E6E0D4074B5E1A@mbs06dc.na.aarp.int> Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-UCIT-MailScanner-Information: Please contact IT Help Desk at (403) 220-5555 for more information X-UCIT-MailScanner: Found to be clean X-UCIT-MailScanner-From: breitliw@centrexcc.com X-archive-position: 17135 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: breitliw@centrexcc.com 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.1 required=5.0 tests=AWL,PLING_QUERY autolearn=no version=2.60 X-Spam-Level: Marquez, Chris wrote: >>>Do you per chance gather histograms ( "for=20 >>>all indexed columns size skewonly" or somethink like it?). > > Yes, Yes, Yes, we do! > At the recommendation of Oracle *guru*? (web site article). > > >>>(you also seem to run with cursor_sharing=3Dforce)=20 > > Yes again. Fix the application (obviously you can change the sql) to use bind variables where appropriate and dump that cursor_sharing=force crutch. > > >>>I have the feeling you are setting yourself up for the=20 >>>bind variable peeking trap and appear to be falling into it. > > What the heck is "bind variable peeking trap"? The Oracle 9 (and higher) optimizer uses the bind value of the variable when it parses the plan as if the sql contained the value rather than a bind variable. Bind variables (even faked ones through c_s=force) let the optimizer reuse the sql without parsing (that's the idea of binding). Therefore, the first bind value - the one that causes the parse - determines the access path for all other executions. Now if you have a skewed data distribution and a histogram and that first bind value is such that a full scan is appropriate all the other executions will use a full scan as well - until the sql is parsed again. Maybe because it aged out, maybe because someone changed "AND" to "and", maybe because one of the tables or indexes got somehow modified or analyzed, maybe someone issued an "alter system flush shared_pool". Then things start over again. Read up on bind variable peeking in the concepts or performance guide. > > Thanks...any suggestions? a) Stop collecting histograms on "all indexed columns". Not all indexed columns should have a histogram and some non-indexed columns DO need one. Collect histograms specifically and only for those columns that need it. b) Don't listen to *guru*s -- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l