Re: how to read this execution plan
- From: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- To: amonte <ax.mount@xxxxxxxxx>
- Date: Sun, 28 Mar 2010 10:48:11 -0500
Alex,
In haste, I gave a simple answer which didn't fully cover the question.
Oracle of course handles different types of join methods in different ways.
For instance, a child RSO can begin sending rows to a parent NESTED LOOPS
RSO right away, before completing the child RSO. But a parent HASH JOIN RSO
requires all children to be completed before it can begin.
Rather than guessing what Oracle is doing, we can measure it. I recommend
reading through a series of blog posts from Tanel Poder, with a pertinent
example at
http://blog.tanelpoder.com/2008/06/15/advanced-oracle-troubleshooting-guide-part-6-understanding-oracle-execution-plans-with-os_explain/
In short, we can use pstack to see exactly what Oracle is doing for a
specific execution - no arguments needed :). Note his discussion about how
the HASH joins have a memory area assigned, so it is not necessary for one
HASH to finish before another can begin. I hope that helps.
Ron
On Sun, Mar 28, 2010 at 3:41 AM, amonte <ax.mount@xxxxxxxxx> wrote:
> From your approach seems that all hash join is performed as last steps?
> Doesnt sound right to me because I have always thought joins works this way,
> imagine t1, t2 and t3
>
> access t1 (index or full scan)
> access t2 (index or full scan)
> hash join t1 results and t2 results
> access t3 (index or full scan)
> hash join t1 and t2 join results with t3 results
>
> From your approach it seems that it is
>
> access t1 (index or full scan)
> access t2 (index or full scan)
> access t3 (index or full scan)
> hash join t1 results and t2 results
> hash join t1 and t2 join results with t3 results
>
> ?
>
> Thank you
>
> Alex
>
>
>
Other related posts:
