From oracle-l-bounce@freelists.org Mon May 30 18:24:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4UNOKEc030415 for ; Mon, 30 May 2005 18:24:20 -0500 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 j4UNOKNi030411 for ; Mon, 30 May 2005 18:24:20 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 61E0C1B8813; Mon, 30 May 2005 17:21:17 -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 13120-04; Mon, 30 May 2005 17:21:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DB0B81B8699; Mon, 30 May 2005 17:21:16 -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:references; b=KbdGjLV39vKqH/QcQiYcymxAPfNfdV30I2vHThlJlYAOgV+Q1ipHi/FPCn/BZ+D6Q+Vt4Utto4eC08+XxSDtZZfCGESCGn9EvMsYTuu0z0fjTk8Rs+7BGq5F+w65ug7AIYUPhL+F6qoD4yPfBIJM1Uroko5RP94mTK1UbfnpEM0= Message-ID: Date: Mon, 30 May 2005 15:19:27 -0700 From: Jared Still To: lex.de.haan@naturaljoin.nl Subject: Re: Why CBO choose wrong way? Cc: zhai_jingmin@yahoo.com, oracle-l@freelists.org In-Reply-To: <20050528082446.1488B93CB8@user3.cybercity.dk> Mime-Version: 1.0 Content-type: text/plain References: <20050528082446.1488B93CB8@user3.cybercity.dk> Content-Transfer-Encoding: 8bit X-archive-position: 20434 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jkstill@gmail.com Precedence: normal Reply-To: jkstill@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 Hi Lex, Interesting. I am dealing with a new version of a COTS package that does that in a login trigger. (sets FIRST_ROWS that is) This had me rather confused for a few minutes when I would get a good plan for some problem queries when logged in as myself, and a very poor plan for the same query when logged in as the application account. At that point I started looking for a trigger. I am now working on convincing them to drop the trigger and stick with the default 'CHOOSE', or at least change to FIRST_ROWS_n. Jared On 5/28/05, Lex de Haan wrote: > > From: info [mailto:info@naturaljoin.nl] > Sent: Saturday, May 28, 2005 10:18 > To: 'zhai_jingmin@yahoo.com'; 'oracle-l@freelists.org' > Subject: RE: Why CBO choose wrong way? > the CBO thinks it saves an expensive(?) sort this way, to satisfy the > order by > clause. > why do you have optimizer_mode set to FIRST_ROWS? and even if you need it, > then > you should at least use the flavors with a trailing number. FIRST_ROWS is > only > maintained for backward compatibility, and is based on heuristics rather > than > statistics. and why do you have optimizer_index_cost_adj set to 30? > > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- http://www.freelists.org/webpage/oracle-l