Re: easuring sql performance (elapsed time and scalability) by number of logical reads
- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
- To: "Bernard Polarski" <bpolarsk@xxxxxxxxx>
- Date: Tue, 2 May 2006 15:00:29 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: easuring sql performance (elapsed time and scalability) by number of logical reads
