From oracle-l-bounce@freelists.org Wed Jun 30 05:38:13 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5UAbwB06613 for ; Wed, 30 Jun 2004 05:38:08 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5UAbm606568 for ; Wed, 30 Jun 2004 05:37:58 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8072772C85C; Wed, 30 Jun 2004 05:20: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 16425-18; Wed, 30 Jun 2004 05:20:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C68BC72C1E8; Wed, 30 Jun 2004 05:20:16 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 30 Jun 2004 05:18:53 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6D8D872C76C for ; Wed, 30 Jun 2004 05:18:52 -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 15730-56 for ; Wed, 30 Jun 2004 05:18:52 -0500 (EST) Received: from relay2.iflexsolutions.com (unknown [202.144.91.165]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0E17572C75A for ; Wed, 30 Jun 2004 05:18:51 -0500 (EST) Received: from fmgrt.rt.i-flex.com (pppserver [192.168.50.2]) by relay2.iflexsolutions.com (8.12.3/8.12.3) with ESMTP id i5UG92bv017235 for ; Wed, 30 Jun 2004 16:09:02 GMT Received: from fmgrt.rt.i-flex.com ([192.168.50.2]) by fmgrt.rt.i-flex.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 30 Jun 2004 16:15:49 +0530 X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: ksms.o Date: Wed, 30 Jun 2004 16:15:48 +0530 Message-ID: <10898BE7CA96D611988B000802255AAF06E77FB8@fmgrt.rt.i-flex.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: ksms.o Thread-Index: AcRejvBYgDD/o19KRcOc5fNTbl1jswAAFoSQ From: To: X-OriginalArrivalTime: 30 Jun 2004 10:45:49.0308 (UTC) FILETIME=[6819EBC0:01C45E8F] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4053 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Satheesh.Babu@iflexsolutions.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Is there a way to open this file? Or atleast to view their contents. Thanks and Regards, Satheesh Babu.S Associate Consultant. 080-57593938 Bangalore. India. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Lex de Haan Sent: Wednesday, June 30, 2004 4:08 PM To: oracle-l@freelists.org Subject: RE: Physics of the FILTER operation within SQL_PLANE. well, the Oracle source code is rather complicated,=20 and I don't have access to it anymore ;-) but there are *definitely* differences between RBO and CBO code paths; RBO and CBO for sure start from different Oracle source code modules. =20 =20 Kind regards, Lex. =20 --------------------------------------------- visit my website at http://www.naturaljoin.nl=20 --------------------------------------------- =20 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of J.Velikanovs@alise.lv Sent: Wednesday, June 30, 2004 11:03 To: oracle-l@freelists.org Subject: RE: Physics of the FILTER operation within SQL_PLANE. Before this day I was sure that Optimizer code path working just to create=20 Execution plan,=20 then feach, execute opeartions are executed exactly the same way=20 independently of used optimizator. Please correct me if I am wrong. Jurijs "Lex de Haan" Sent by: oracle-l-bounce@freelists.org 30.06.2004 13:00 Please respond to oracle-l =20 To: cc: Subject: RE: Physics of the FILTER operation within=20 SQL_PLANE. Jurijs, I fully agree with you -- RBO is ideal for that purpose, but there is a risk that you use RBO code path. in my humble opinion (open for corrections!) it is better to test things against the CBO, with "proper" statistics available, and then to force certain execution plans by using CBO hints... (there are more than enough of them ;-) Kind regards, Lex. --------------------------------------------- visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of J.Velikanovs@alise.lv Sent: Wednesday, June 30, 2004 10:33 To: oracle-l@freelists.org Cc: oracle-l@freelists.org; oracle-l-bounce@freelists.org Subject: RE: Physics of the FILTER operation within SQL_PLANE. Lex, Thank for comment, I use RBO just for proof of concept. I am sure that the same situation can be emulated with CBO, but in this case it was easy for me to use RBO. >> about the optimizer not being able to calculate differences Even if would use CBO it will not be able to calculate difference in case of FILTER operation, was my point. Jurijs "Lex de Haan" Sent by: oracle-l-bounce@freelists.org 30.06.2004 12:30 Please respond to oracle-l To: cc: Subject: RE: Physics of the FILTER operation within SQL_PLANE. Hi Jurijs, allow me one comment about your statement below, about the optimizer not being able to calculate differences: you are forcing RBO behavior, a technology now obsolete in 10g and untouched by Oracle development for many, many years... (interesting email thread, by the way) Kind regards, Lex. --------------------------------------------- visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of J.Velikanovs@alise.lv Sent: Wednesday, June 30, 2004 10:02 To: oracle-l@freelists.org Subject: Re: Physics of the FILTER operation within SQL_PLANE. If your hypnotize is true (regerding remembering last value), then I have one more interesting conclusion: The cost of the same SQL with the same execution plans can differ depending on rows ordering in the driving rowset. Like with index range scan and clustering factor, but with one little difference - Oracle optimizer in case of FILTER operation can't evaluate this cost (in case of range scan - clustering factor statistics available). Even more, if your hypnotize is true, then cost difference can appears not from bigger LIO count, but also because in one case Oracle need to manage hash table in other doesn't (just use last remembered value). For my point demonstrating purpose, I have a little bit modified my initial testcase (1,2). Take a look: the same SQL, the same ExplPlan, time differs by 18% and Oracle optimizer can't caltulate this difference. Jurijs TESTCACE 1 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D SELECT /*+ RULE */ count(m.v) from main_tab m where exists (select v from filter_tab f where f.n=3Dm.n and f.v like 'a%') TESTCACE 2 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D SELECT /*+ RULE */ count(m.v) from main_tab m where exists (select v from filter_tab f where f.n=3Dm.n and f.v like 'a%') -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------