From oracle-l-bounce@freelists.org Wed May 11 09:41:11 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4BEfBXe026637 for ; Wed, 11 May 2005 09:41:11 -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 j4BEfB4Z026633 for ; Wed, 11 May 2005 09:41:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C671819260C; Wed, 11 May 2005 08:38:28 -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 08243-02; Wed, 11 May 2005 08:38:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 41DAE192473; Wed, 11 May 2005 08:38:28 -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:content-disposition:references; b=Zh3kGXWX0WyZYtP/34Hx/pD97dOb0orVR0Ar9T+65Y+nC5iQvaCH9qrv/2qApy/af1E9ixM5mt8FF1MMHkwsywvM2jX3XHuieKBW4QVLCgEU3ka/VK1TLsIG7cxlbysX0PQcSTpQm9zuvKxN6nxyIHNIF5vxVbeNXFlVPTk/q8g= Message-ID: <962cf44b0505110636728d94ef@mail.gmail.com> Date: Wed, 11 May 2005 21:36:41 +0800 From: zhu chao To: Christian Antognini Subject: Re: Optimizer change from 8i-9i Cc: oracle-l@freelists.org, breitliw@centrexcc.com In-Reply-To: <7F0C000A3ABA6241A10C9ABF37EEB46D040851@MSXVS01.trivadis.com> Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline References: <7F0C000A3ABA6241A10C9ABF37EEB46D040851@MSXVS01.trivadis.com> X-archive-position: 19577 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: zhuchao@gmail.com Precedence: normal Reply-To: zhuchao@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.1 required=5.0 tests=AWL,UPPERCASE_25_50 autolearn=no version=2.63 As Wolfgang said, I think although your autotrace shows RULE optimizer, oracle maybe actually using CBO. Can you enable SQL Trace and check whether it is actually parsed by RBO or = CBO? By the way, team, anyone aware of some common situlation that autotrace can lie to us printing the execution plan and the optimizer used? Wolfgang, Thanks very much for you so detailed answer. Can I ask one more question , about the=20 "the index tie-break rule being the only reasonable exception, all the other possibilities are a bit far fetched IMO" I never knew this, index tie-break rule. Thanks. By the way, Chao is my given name. We speel the name like "Family Name-Given name":) On 5/11/05, Christian Antognini wrote: > Hi >=20 > > About the RBO change in 9i, can you show me a test case? >=20 > E.g. complex_view_merging could lead to different execution plans. > If I run the following statements in 8.1.7 and 9.2.0 I get different resu= lts... > (notice that both database have a "minimal" INIT.ORA that doesn't modify = any optimizer parameter) >=20 > ALTER SESSION SET optimizer_mode =3D rule; >=20 > SELECT * FROM v$version WHERE rownum =3D 1; >=20 > CREATE OR REPLACE VIEW min_zip_v AS > SELECT city, min(zip) zip > FROM addresses > GROUP BY city; >=20 > SET TIMING ON > SET AUTOTRACE ON EXP >=20 > SELECT adr.* > FROM persons prs, addresses adr > WHERE (adr.city, adr.zip) IN (SELECT city, zip FROM min_zip_v) > AND adr.prs_id =3D prs.id > AND prs.lastname =3D 'Meier'; >=20 > BANNER > ---------------------------------------------------------------- > Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production >=20 > Elapsed: 00:00:26.01 >=20 > Execution Plan > ---------------------------------------------------------- > SELECT STATEMENT Optimizer=3DRULE > NESTED LOOPS > NESTED LOOPS > VIEW OF 'MIN_ZIP_V' > SORT (GROUP BY) > TABLE ACCESS (FULL) OF 'ADDRESSES' > TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES' > INDEX (RANGE SCAN) OF 'ADR_CITY' (NON-UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS' > INDEX (UNIQUE SCAN) OF 'PRS_PK' (UNIQUE) >=20 > BANNER > ---------------------------------------------------------------- > Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production >=20 > Elapsed: 00:00:00.03 >=20 > Execution Plan > ---------------------------------------------------------- > SELECT STATEMENT Optimizer=3DRULE > FILTER > SORT (GROUP BY) > TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES' > NESTED LOOPS > NESTED LOOPS > TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS' > INDEX (RANGE SCAN) OF 'PRS_LASTNAME' (NON-UNIQUE) > TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES' > INDEX (RANGE SCAN) OF 'ADR_PRS_ID' (NON-UNIQUE) > INDEX (RANGE SCAN) OF 'ADR_CITY' (NON-UNIQUE) >=20 > Therefore don't think that the migration will be easy! Carefully plan it = and, of course, do some load tests. >=20 > HTH > Chris >=20 --=20 Regards Zhu Chao www.cnoug.org -- http://www.freelists.org/webpage/oracle-l