From oracle-l-bounce@freelists.org Thu Mar 3 12:26:38 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j23IQcWA009141 for ; Thu, 3 Mar 2005 12:26:38 -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 j23IQZem009132 for ; Thu, 3 Mar 2005 12:26:35 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A2807C9D4; Thu, 3 Mar 2005 12:12:08 -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 02911-07; Thu, 3 Mar 2005 12:12:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C61777D38B; Thu, 3 Mar 2005 12:12:07 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=BoZRmwxUutfe2Odl7YxthB/EIhIG5O0TjDBVFHdMb9s5Ei2ktWVB4a1H+rBvPW3Zi45+9bP14vEpmUx1RKjw2rB6CtzlvRRHlw+n//8b+muICn9+fnste5IMjKDbbMfFgSSixG8YqBW9qZL5BtT4eIuPC7mVnBxkA00ItmNItmA= Message-ID: <7765c89705030309104023adcd@mail.gmail.com> Date: Thu, 3 Mar 2005 17:10:03 +0000 From: Niall Litchfield To: jo_holvoet@amis.com Subject: Re: Different execution plans for same query with same cost Cc: oracle-l@freelists.org In-Reply-To: Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: X-archive-position: 16890 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: niall.litchfield@gmail.com Precedence: normal Reply-To: niall.litchfield@gmail.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=AWL autolearn=ham version=2.60 X-Spam-Level: On Thu, 03 Mar 2005 17:56:58 +0100, jo_holvoet@amis.com wrote: > Hi all, > > Oracle 8.1.7.4 on 64-bit Solaris 8. > I've searched Metalink but I'm having a hard time finding search terms > that don't return about 2 million hits :) and how many of them relate to rdb :) > I've run into a situation where the same query run on the same instance > from the same session gets a different execution plan from one run to the > next (confirmed this with the STAT-lines in a 10046 trace). Both execution > plans apparently get the same cost from CBO. Am I right in thinking that > in such a case the CBO is not "deterministic" (i.e. it will not arrive at > the same plan every time) but randomly arrive at one of the plans with the > same lowest cost ? I'm not sure that I believe that the query *is* the same. If it really is the same query then the second run should use the same execution plan as the first one this is rather the point of bind variables for example . Are there perhaps different literal values in the query? different case or capitalisation, FGAC going on if that was around in 8i (think it was). That all said if when evaluating execution plans the cost the CBO arrives at is not better than the best it already has it will not choose the second plan to be evaluated. (not sure in the absence of the ordered or other hints what drives the order of evaluation - Wolfgang might know). -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com -- http://www.freelists.org/webpage/oracle-l