From oracle-l-bounce@freelists.org Fri Dec 2 13:11:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jB2JB87d010032 for ; Fri, 2 Dec 2005 13:11:08 -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 jB2JArAX009999 for ; Fri, 2 Dec 2005 13:10:53 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6133C240F98; Fri, 2 Dec 2005 14:10:44 -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 10719-01; Fri, 2 Dec 2005 14:10:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C2566240DE0; Fri, 2 Dec 2005 14:10:43 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 02 Dec 2005 14:08:48 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1738C240EBC for ; Fri, 2 Dec 2005 14:08:48 -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 10186-02 for ; Fri, 2 Dec 2005 14:08:47 -0500 (EST) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.203]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5BD16240EB9 for ; Fri, 2 Dec 2005 14:08:47 -0500 (EST) Received: by zproxy.gmail.com with SMTP id l8so617654nzf for ; Fri, 02 Dec 2005 11:08:47 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:from:to:cc:subject:date:mime-version:content-type:x-priority:x-msmail-priority:x-mailer:x-mimeole; b=BJw++OiKAWIWd3o1SzWUiczCfrMfVzdb0UqiqV+bQP8PAW22Fx5zslCmjacaxJ/1Tp8021hs9OYbDCP26e4crrfW7OBCenKY7wRHOv1bzRtg9ePlFN+6zj/kWxSUlnkvlhLQGXVoNoT9JZXkZgH39Z2OTQ/rkjEHdiKQZlN+wew= Received: by 10.65.96.7 with SMTP id y7mr1698332qbl; Fri, 02 Dec 2005 11:08:46 -0800 (PST) Received: from IBME1D11967173 ( [212.171.9.111]) by mx.gmail.com with ESMTP id e14sm890014qbe.2005.12.02.11.08.44; Fri, 02 Dec 2005 11:08:46 -0800 (PST) Message-ID: <005401c5f773$d441d0f0$4900a8c0@IBME1D11967173> From: "Radoulov, Dimitre" To: "Christian Antognini" , "Jonathan Lewis" Cc: Subject: Re: CBO - hash join vs nested loops Date: Fri, 2 Dec 2005 20:08:47 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0051_01C5F77C.3441F3B0" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-archive-position: 28873 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cichomitiko@gmail.com Precedence: normal Reply-To: cichomitiko@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.2 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, RCVD_IN_DSBL,RCVD_IN_NJABL,RCVD_IN_SORBS,UPPERCASE_25_50 autolearn=no version=2.63 ------=_NextPart_000_0051_01C5F77C.3441F3B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm missing somethink. I answered too quickly, without verifing the = result. The NL access choosen does the same lio as the hash join and the = hinted version is always different: 20:05:24 SQL> select count(distinct fdc.cbv_code) 20:05:33 2 from filtro_dati_catalogo fdc inner join opt_vp vo on fdc.id_subcatalogo in (0) and vo.codice_opt =3D 29 and fdc.pv_code =3D vo.pv_code20:05:33 3 = 20:05:33 4 ; COUNT(DISTINCTFDC.CBV_CODE) --------------------------- 29 Elapsed: 00:00:00.93 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D409 Card=3D1 = Bytes=3D46) 1 0 SORT (GROUP BY) 2 1 NESTED LOOPS (Cost=3D409 Card=3D2076 Bytes=3D95496) 3 2 INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO' (UNIQUE) (Cost=3D409 Card=3D424315 Bytes=3D11456505) 4 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4276 consistent gets 0 physical reads 0 redo size 510 bytes sent via SQL*Net to client 655 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed 20:05:35 SQL> select /*+ USE_NL(fdc vo) */ count(distinct fdc.cbv_code) 20:05:44 2 from filtro_dati_catalogo fdc inner join opt_vp vo on fdc.id_subcatalogo in (0) and vo.codice_opt =3D 29 and fdc.pv_code =3D vo.pv_code20:05:44 3 = 20:05:44 4 ; COUNT(DISTINCTFDC.CBV_CODE) --------------------------- 29 Elapsed: 00:00:00.56 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D409 Card=3D1 = Bytes=3D46) 1 0 SORT (GROUP BY) 2 1 NESTED LOOPS (Cost=3D409 Card=3D2076 Bytes=3D95496) 3 2 INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO' (UNIQUE) (Cost=3D409 Card=3D424315 Bytes=3D11456505) 4 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3088 consistent gets 0 physical reads 0 redo size 510 bytes sent via SQL*Net to client 655 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed 20:05:46 SQL> alter session set optimizer_index_caching=3D10; Session altered. Elapsed: 00:00:00.00 20:05:52 SQL> select count(distinct fdc.cbv_code) 20:06:05 2 from filtro_dati_catalogo fdc inner join opt_vp vo on fdc.id_subcatalogo in (0) and vo.codice_opt =3D 29 and fdc.pv_code =3D vo.pv_code20:06:05 3 = 20:06:05 4 ; COUNT(DISTINCTFDC.CBV_CODE) --------------------------- 29 Elapsed: 00:00:00.94 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D409 Card=3D1 = Bytes=3D46) 1 0 SORT (GROUP BY) 2 1 NESTED LOOPS (Cost=3D409 Card=3D2076 Bytes=3D95496) 3 2 INDEX (FAST FULL SCAN) OF 'XIF01FILTRO_DATI_CATALOGO' (UNIQUE) (Cost=3D409 Card=3D424315 Bytes=3D11456505) 4 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_14178' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4276 consistent gets 0 physical reads 0 redo size 510 bytes sent via SQL*Net to client 655 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed Regards, Dimitre Radoulov ------=_NextPart_000_0051_01C5F77C.3441F3B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'm missing somethink. = I answered too=20 quickly, without verifing the result. The NL access choosen does the = same lio as=20 the hash join and the hinted version is always different:
 
 
20:05:24 SQL> select = count(distinct=20 fdc.cbv_code)
20:05:33   2  from filtro_dati_catalogo=20 fdc
inner join opt_vp vo on fdc.id_subcatalogo in (0)
and = vo.codice_opt =3D=20 29 and fdc.pv_code =3D vo.pv_code20:05:33   3  = 20:05:33  =20 4  ;
 
COUNT(DISTINCTFDC.CBV_CODE)
---------------------------
&n= bsp;           &nb= sp;           =20 29
 
Elapsed: = 00:00:00.93
 
Execution=20 Plan
----------------------------------------------------------
&nb= sp; =20 0      SELECT STATEMENT Optimizer=3DCHOOSE = (Cost=3D409=20 Card=3D1 Bytes=3D46)
   1    0   = SORT (GROUP=20 BY)
   2    1     NESTED = LOOPS=20 (Cost=3D409 Card=3D2076 Bytes=3D95496)
   = 3   =20 2       INDEX (FAST FULL SCAN) OF=20 'XIF01FILTRO_DATI_CATALOGO'
       =   =20 (UNIQUE) (Cost=3D409 Card=3D424315 Bytes=3D11456505)
 
   = 4   =20 2       INDEX (UNIQUE SCAN) OF = 'SYS_IOT_TOP_14178'=20 (UNIQUE)
 
 
 

Statistics
--------------------------------------------------= --------
         =20 0  recursive=20 calls
          0  = db block=20 gets
       4276  consistent=20 gets
          0  = physical=20 reads
          0  = redo=20 size
        510  bytes sent = via=20 SQL*Net to client
        = 655  bytes=20 received via SQL*Net from=20 client
          2  = SQL*Net=20 roundtrips to/from=20 client
          1  = sorts=20 (memory)
          = 0  sorts=20 (disk)
          1  = rows=20 processed
 
20:05:35 SQL> select /*+ = USE_NL(fdc vo)=20 */ count(distinct fdc.cbv_code)
20:05:44   2  from=20 filtro_dati_catalogo fdc
inner join opt_vp vo on fdc.id_subcatalogo = in=20 (0)
and vo.codice_opt =3D 29 and fdc.pv_code =3D = vo.pv_code20:05:44  =20 3  20:05:44   4  ;
 
COUNT(DISTINCTFDC.CBV_CODE)
---------------------------
&n= bsp;           &nb= sp;           =20 29
 
Elapsed: = 00:00:00.56
 
Execution=20 Plan
----------------------------------------------------------
&nb= sp; =20 0      SELECT STATEMENT Optimizer=3DCHOOSE = (Cost=3D409=20 Card=3D1 Bytes=3D46)
   1    0   = SORT (GROUP=20 BY)
   2    1     NESTED = LOOPS=20 (Cost=3D409 Card=3D2076 Bytes=3D95496)
   = 3   =20 2       INDEX (FAST FULL SCAN) OF=20 'XIF01FILTRO_DATI_CATALOGO'
       =   =20 (UNIQUE) (Cost=3D409 Card=3D424315 Bytes=3D11456505)
 
   = 4   =20 2       INDEX (UNIQUE SCAN) OF = 'SYS_IOT_TOP_14178'=20 (UNIQUE)
 
 
 

Statistics
--------------------------------------------------= --------
         =20 0  recursive=20 calls
          0  = db block=20 gets
       3088  consistent=20 gets
          0  = physical=20 reads
          0  = redo=20 size
        510  bytes sent = via=20 SQL*Net to client
        = 655  bytes=20 received via SQL*Net from=20 client
          2  = SQL*Net=20 roundtrips to/from=20 client
          1  = sorts=20 (memory)
          = 0  sorts=20 (disk)
          1  = rows=20 processed
 
20:05:46 SQL> alter session = set=20 optimizer_index_caching=3D10;
 
Session altered.
 
Elapsed: = 00:00:00.00
20:05:52 SQL>=20 select count(distinct fdc.cbv_code)
20:06:05   2  from = filtro_dati_catalogo fdc
inner join opt_vp vo on fdc.id_subcatalogo = in=20 (0)
and vo.codice_opt =3D 29 and fdc.pv_code =3D = vo.pv_code20:06:05  =20 3  20:06:05   4  ;
 
COUNT(DISTINCTFDC.CBV_CODE)
---------------------------
&n= bsp;           &nb= sp;           =20 29
 
Elapsed: = 00:00:00.94
 
Execution=20 Plan
----------------------------------------------------------
&nb= sp; =20 0      SELECT STATEMENT Optimizer=3DCHOOSE = (Cost=3D409=20 Card=3D1 Bytes=3D46)
   1    0   = SORT (GROUP=20 BY)
   2    1     NESTED = LOOPS=20 (Cost=3D409 Card=3D2076 Bytes=3D95496)
   = 3   =20 2       INDEX (FAST FULL SCAN) OF=20 'XIF01FILTRO_DATI_CATALOGO'
       =   =20 (UNIQUE) (Cost=3D409 Card=3D424315 Bytes=3D11456505)
 
   = 4   =20 2       INDEX (UNIQUE SCAN) OF = 'SYS_IOT_TOP_14178'=20 (UNIQUE)
 
 
 

Statistics
--------------------------------------------------= --------
         =20 0  recursive=20 calls
          0  = db block=20 gets
       4276  consistent=20 gets
          0  = physical=20 reads
          0  = redo=20 size
        510  bytes sent = via=20 SQL*Net to client
        = 655  bytes=20 received via SQL*Net from=20 client
          2  = SQL*Net=20 roundtrips to/from=20 client
          1  = sorts=20 (memory)
          = 0  sorts=20 (disk)
          1  = rows=20 processed
 
 
 
Regards,
Dimitre Radoulov
 
------=_NextPart_000_0051_01C5F77C.3441F3B0-- -- http://www.freelists.org/webpage/oracle-l