Interpret tkprof
Hi,
two queries that retrieves the same result set but using different join algorithm.
The row source operation shows the first one has more consistent reads but use less time. The second one has less consistent reads but spends more time. So I think the nested loop
that spends less time is a better choice even though it has more consistent reads. Can anyone confirm that?
Thanks,
Sha
Rows Row Source Operation
------- ---------------------------------------------------
29 NESTED LOOPS SEMI (cr=2654 pr=0 pw=0 time=0 us cost=1766 size=306 card=18)
35 TABLE ACCESS FULL ICAT (cr=18 pr=0 pw=0 time=306 us cost=5 size=180 card=18)
29 INDEX FAST FULL SCAN IMSTI2 (cr=2636 pr=0 pw=0 time=0 us cost=98 size=508417 card=72631)(object id 75499)
two queries that retrieves the same result set but using different join algorithm.
The row source operation shows the first one has more consistent reads but use less time. The second one has less consistent reads but spends more time. So I think the nested loop
that spends less time is a better choice even though it has more consistent reads. Can anyone confirm that?
Thanks,
Sha
Rows Row Source Operation
------- ---------------------------------------------------
29 NESTED LOOPS SEMI (cr=2654 pr=0 pw=0 time=0 us cost=1766 size=306 card=18)
35 TABLE ACCESS FULL ICAT (cr=18 pr=0 pw=0 time=306 us cost=5 size=180 card=18)
29 INDEX FAST FULL SCAN IMSTI2 (cr=2636 pr=0 pw=0 time=0 us cost=98 size=508417 card=72631)(object id 75499)
0