From oracle-l-bounce@freelists.org Thu Mar 3 13:20:11 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j23JKBBg017498 for ; Thu, 3 Mar 2005 13:20:11 -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 j23JK8em017492 for ; Thu, 3 Mar 2005 13:20:09 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B31B77DD7C; Thu, 3 Mar 2005 13:10:55 -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 23405-03; Thu, 3 Mar 2005 13:10:55 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3E4007DB4A; Thu, 3 Mar 2005 13:10:55 -0500 (EST) X-Server-Uuid: 40F9ED1A-D9DC-4A5C-8325-E88787309FDC X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: RE: Different execution plans for same query with same cost Date: Thu, 3 Mar 2005 12:08:33 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Different execution plans for same query with same cost Thread-Index: AcUgGTCr6iitJdx1QPqjd+fHAVa8wgAAmWmw From: "Dirschel, Steve" To: jo_holvoet@amis.com, oracle-l@freelists.org X-OriginalArrivalTime: 03 Mar 2005 18:08:33.0553 (UTC) FILETIME=[033E8810:01C5201C] X-WSS-ID: 6E398D282CG872204-01-01 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 16906 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Steve.Dirschel@bestbuy.com Precedence: normal Reply-To: Steve.Dirschel@bestbuy.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.0 required=5.0 tests=none autolearn=ham version=2.60 X-Spam-Level: We ran into bugs in 9.2.0.3 where FORCE would cause certain queries to reparse every time they executed...that may explain why it's reparsing the query. But it still doesn't explain why the plan would change. By the way- a point of interest- we used to use CURSOR_SHARING=3DSIMILAR with histograms on ALL INDEXED columns. I see you're doing the same with histograms. If you have CURSOR_SHARING=3DSIMILAR and a histogram'd column is in the where clause Oracle will reparse a statement EVERY time it executes, regardless of the value used...this is a "feature". -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of jo_holvoet@amis.com Sent: Thursday, March 03, 2005 11:39 AM To: oracle-l@freelists.org Subject: RE: Different execution plans for same query with same cost Actually CURSOR_SHARING=3DFORCE. Long story, crappy third-party app (no=20 binds, very little normalization, ...). mvg/regards Jo -- http://www.freelists.org/webpage/oracle-l