Re: easuring sql performance (elapsed time and scalability) by number of logical reads

> Well, we all see the hash join in the fisrt query while the second one is
> doing the nested loop and here is your time.
>
> 3309 HASH JOIN (cr=1523 r=0 w=0 time=223005 us)
>
> what about the memory allocate to the hash join is too small and the
> hash join is pushed to disk?

No,
the hash join is in memory:

14:59:08 SQL> r
  1 select *

  2            from (select a. *, rownum r
  3              from (select  distinct atp.part_number as codice,
  4                                    lsc.ds_lunga    as descr,
  5                                    ''              as TIPOOPT,
  6                                    ''              as b
  7                      from filtro_dati_catalogo fdc,
  8                           an_telai             ati,
  9                           vp_tavole            vpt,
 10                           assoc_tavole_parts   atp,
 11                           an_parts             ap,
 12                           lessico_pn           lsc
 13                     where fdc.id_subcatalogo in (0) and ati.targa = 
'FNZ8243' and
 14                           lsc.language_code = 1 and
 15                           fdc.pv_code = ati.pv_code and
 16                           fdc.pv_code = vpt.pv_code and
 17                           vpt.cod_tavola_grafica =
 18                           atp.cod_tavola_grafica and
 19                           ap.part_number = atp.part_number and
 20                           lsc.codice_lessico = ap.codice_lessico and
 21                           (lsc.ds_lunga like '1118647' or
 22                           Trim(ap.part_number) like '1118647')
 23                     order by codice asc) a
 24             where rownum <= 23) b
 25*          where r > 0

Elapsed: 00:00:00.83

Statistics

          0  recursive calls
          0  db block gets
       1725  consistent gets
          0  physical reads
          0  redo size
        758  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

14:59:09 SQL> select  *
14:59:15   2            from (select a. *, rownum r
14:59:15   3              from (select /*+ USE_NL(lsc ap) */ distinct 
atp.part_number as codice,
14:59:15   4                                    lsc.ds_lunga    as descr,
14:59:15   5                                    ''              as TIPOOPT,
14:59:15   6                                    ''              as b
14:59:15   7                      from filtro_dati_catalogo fdc,
14:59:15   8                           an_telai             ati,
14:59:15   9                           vp_tavole            vpt,
14:59:15  10                           assoc_tavole_parts   atp,
14:59:15  11                           an_parts             ap,
14:59:15  12                           lessico_pn           lsc
14:59:15  13                     where fdc.id_subcatalogo in (0) and 
ati.targa = 'FNZ8243' and
14:59:15  14                           lsc.language_code = 1 and
14:59:15  15                           fdc.pv_code = ati.pv_code and
14:59:15  16                           fdc.pv_code = vpt.pv_code and
14:59:15  17                           vpt.cod_tavola_grafica =
14:59:15  18                           atp.cod_tavola_grafica and
14:59:15  19                           ap.part_number = atp.part_number and
14:59:15  20                           lsc.codice_lessico = 
ap.codice_lessico and
14:59:15  21                           (lsc.ds_lunga like '1118647' or
14:59:15  22                           Trim(ap.part_number) like '1118647')
14:59:15  23                     order by codice asc) a
14:59:15  24             where rownum <= 23) b
14:59:15  25           where r > 0;

Elapsed: 00:00:00.66

Statistics

          0  recursive calls
          0  db block gets
       7559  consistent gets
          0  physical reads
          0  redo size
        758  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Dimitre

--
http://www.freelists.org/webpage/oracle-l