Re: performance of joins

From: derek <dereksmi_at_gmail.com>
Date: 3 Jun 2005 14:45:09 -0700
Message-ID: <1117835109.852799.120550@z14g2000cwz.googlegroups.com>

Hi,

1. Yeah , ours is a specific application which needs this kind of non-equi join functionality. Although, we certainly understand that its output is typically the size of the cartesian product.
2. I totally agree that in a typical query execution, the "query" column is a good metric to measure performance. But in this particular case, it does not seem to be exactly the case. Because as you can see from the summary of the earlier results for 10k x 10k join, the nested loops has almost thrice the buffer gets compared with nested index join but still has similar performance. So cant this be a specific case where there is a lot of computation happening which probably overshadows the time for buffer gets?

NESTED LOOPS: 380,000 buffer gets
NESTED INDEX JOIN: 126,280 buffer gets
MERGE: 76 buffer gets

3) I am pasting the results with the autotrace on at the end of this posting. I hope that you would be able to help me out with understanding the trace. I will be more than glad to send you the actual trace file if you require it to analyse more deeply. One surprising thing I noticed in the output of the auto-trace is "sorts" being used in the nested indexed join. What does that mean? Also in sort merge join number of sorts is 4 -- does it mean that the sorted tables are trashed due to lack of memory?

4) For the comment 4, yeah I think Oracle probably doesnot care abt optimising some unusual queries and ths might explain the results. I am attaching th autotrace summaries first followed by summaries from the trace file of tkprof.

best regards,
Derek Smith.

select count(*) from (select /*+ ORDERED USE_NL(f1 f2)*/f2.a, f1.a from test2 f2, test1 f1 where f2.a < f1.a);

  COUNT(*)

---

  49995000

Execution Plan

---

   0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=80209 Card=1 Bytes
	  =26)

   1	0   SORT (AGGREGATE)
   2	1     NESTED LOOPS (Cost=80209 Card=5000000 Bytes=130000000)
   3	2	TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=9 Card=10
	  000 Bytes=130000)

   4	2	TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=8 Card=50
	  0 Bytes=6500)

Statistics

---

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

select /*+ USE_MERGE(t1 t2) */ count(*) from test2 t2, test1 t1 where t2.a < t1.a;

  COUNT(*)

---

  49995000

Execution Plan

---

   0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=121 Card=1 Bytes=2
	  6)

   1	0   SORT (AGGREGATE)
   2	1     MERGE JOIN (Cost=121 Card=5000000 Bytes=130000000)
   3	2	SORT (JOIN) (Cost=61 Card=10000 Bytes=130000)
   4	3	  TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=9 Card=
	  10000 Bytes=130000)

   5	2	SORT (JOIN) (Cost=61 Card=10000 Bytes=130000)
   6	5	  TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=9 Card=
	  10000 Bytes=130000)

Statistics

---

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

Index created.

select /*+ ORDERED USE_NL_WITH_INDEX (f1 testidx1) NO_USE_HASH(f1 f2) NO_USE_MERGE(f1 f2)*/count(*) from test2 f2, test1 f1 where f2.a < f1.a;

  COUNT(*)

---

  49995000

Execution Plan

---

   0	  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=20249 Card=1 Bytes
	  =26)

   1	0   SORT (AGGREGATE)
   2	1     NESTED LOOPS (Cost=20249 Card=5000000 Bytes=130000000)
   3	2	TABLE ACCESS (FULL) OF 'TEST2' (TABLE) (Cost=9 Card=10
	  000 Bytes=130000)

   4	2	INDEX (RANGE SCAN) OF 'TMPINDEX1' (INDEX) (Cost=2 Card
	  =500 Bytes=6500)

Statistics

---

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


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

//tkprof summaries

select /*+ USE_MERGE(t1 t2) */ count(*)
from
 test2 t2, test1 t1 where t2.a < t1.a

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          2          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2     27.46      27.09          0         76          0
       1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4     27.46      27.09          0         78          0
       1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=76 pr=0 pw=0 time=27090635 us)

49995000 MERGE JOIN (cr=76 pr=0 pw=0 time=150015649 us)   10000 SORT JOIN (cr=38 pr=0 pw=0 time=36999 us)   10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=10075 us) 49995000 SORT JOIN (cr=38 pr=0 pw=0 time=50035446 us)   10000 TABLE ACCESS FULL TEST1 (cr=38 pr=0 pw=0 time=10064 us)

---

select /*+ ORDERED USE_NL_WITH_INDEX (f1 testidx1) NO_USE_HASH(f1 f2)   NO_USE_MERGE(f1 f2)*/count(*)
from
 test2 f2, test1 f1 where f2.a < f1.a

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          3          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2     22.05      21.76         25     133896          0
       1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4     22.05      21.76         25     133899          0
       1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=133896 pr=25 pw=0 time=21761168 us)

49995000 NESTED LOOPS (cr=133896 pr=25 pw=0 time=149985200 us)   10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=30073 us) 49995000 INDEX RANGE SCAN TMPINDEX1 (cr=133858 pr=25 pw=0 time=50085537 us)(object id 52989)

---

select count(*)
from
 (select /*+ ORDERED USE_NL(f1 f2)*/f2.a, f1.a from test2 f2, test1 f1 where
  f2.a < f1.a)

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          3          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2     28.28      28.18          0     380038          0
       1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4     28.28      28.19          0     380041          0
       1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=380038 pr=0 pw=0 time=28187016 us)

49995000 NESTED LOOPS (cr=380038 pr=0 pw=0 time=149985109 us)   10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=40080 us) 49995000 TABLE ACCESS FULL TEST1 (cr=380000 pr=0 pw=0 time=50278540 us)

---

Received on Fri Jun 03 2005 - 16:45:09 CDT