Re: non equi-joins

derek wrote:
> Hi David,
> Sincere apologies for duplicate posting. Thanks a lot for helping me
> out!! However, there is another aspect of the execution trace which I
> could not understand. I have the trace output of the same query as
> earlier pasted below. When I checked the "time" values in the "Row
> source operation" of the execution plan, I find that the time value of
> the merge phase is much higher than the value in the sort aggregate.
> Does that mean that the "merge time" is the projected time had the
> merge operation been materialised instead of just being pipelined to be
> aggregated? Or am I missing some thing? Hope to hear from you soon.
>
> best regards,
> Nagender.
>
>
>
>
> select /*+ ORDERED USE_MERGE (f1 f2)*/count(*)
> from
> test2 f2, test1 f1 where f1.a = f2.a
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 2 0.04 0.05 0 32 0
> 1
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 0.04 0.05 0 32 0
> 1
>
> Misses in library cache during parse: 0
> Optimizer mode: ALL_ROWS
> Parsing user id: 58
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 SORT AGGREGATE (cr=32 pr=0 pw=0 time=51752 us)
> 36000 MERGE JOIN (cr=32 pr=0 pw=0 time=159758 us)
> 3000 SORT JOIN (cr=16 pr=0 pw=0 time=15583 us)
> 3000 TABLE ACCESS FULL TEST2 (cr=16 pr=0 pw=0 time=3179 us)
> 36000 SORT JOIN (cr=16 pr=0 pw=0 time=43848 us)
> 3000 TABLE ACCESS FULL TEST1 (cr=16 pr=0 pw=0 time=3068 us)
>
> ********************************************************************************

This is tkprof output, generated from a session trace, where the query was actually executed and a result set returned, so no 'projected' values are present. All times reported, afaik, are actual elapsed times. Note, however, the time in the explain plan portion is in microseconds, and the times reported by tkprof in the summary section are in 1/100ths of a second. The MERGE JOIN consumed 159758 microseconds; this converts to roughly 160 milliseconds or 0.16 seconds (roundng up). The CPU time for this same query was only 0.04 seconds, so it would appear you were waiting for a resource for the merge join. I can't say for certain as I don't have the trace file to examine, but I wonder what value you've set for your sort_area_size. And I did notice this is not the same query you originally posted, as you're now using an equi-join.

Jonathan Lewis and Tom Kyte cover reading and interpreting this output in their texts. Also, since you're concerned with what the optimiser is actually doing, you might want to set event 10053 at level 1 for a dump of the optimiser calculations/operations for this same query. Between the two trace files you should be able to interpret what Oracle is doing with your query, and, hopefully, why it's choosing the operations it's reporting.

David Fitzjarrell Received on Wed Jun 01 2005 - 08:22:23 CDT