Execution plan sequence — oracle-tech

    Forum Stats

  • 3,714,552 Users
  • 2,242,576 Discussions
  • 7,844,931 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Execution plan sequence

ronald_2017
ronald_2017 Member Posts: 456 Blue Ribbon
edited December 2020 in SQL & PL/SQL

Hi All,


The following execution plan is excerpted from the following link.

https://jonathanlewis.wordpress.com/2010/12/10/quiz-night-10


I am just wondering what is the sequence of the following execution plan?


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

| Id  | Operation             | Name | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT      |      |     1 |    24 |    11 |

|   1 |  SORT AGGREGATE       |      |     1 |    24 |       |

|*  2 |   HASH JOIN           |      |    70 |  1680 |    11 |

|   3 |    TABLE ACCESS FULL  | T4   |    70 |   420 |     2 |

|*  4 |    HASH JOIN          |      |    70 |  1260 |     8 |

|   5 |     TABLE ACCESS FULL | T3   |    70 |   420 |     2 |

|*  6 |     HASH JOIN         |      |    70 |   840 |     5 |

|   7 |      TABLE ACCESS FULL| T2   |    70 |   420 |     2 |

|   8 |      TABLE ACCESS FULL| T1   |    70 |   420 |     2 |

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

Is the sequence below correct?

3-> 5 -> 7 -> 8 -> 6 -> 4 -> 2-> 1


First creates a hash table for T4, then creates a second hash table for T3 then creates a third hash table for T2 right?

If So, 3 hash tables has been built in the memory before joining the tables?

Also, the join order and the execution plan operation order are not the same right?


Thanks in advance

Best Answer

Answers

  • evgenyg
    evgenyg Member Posts: 330 Bronze Badge

    I would say from in to out.

    i.e. in your case FTS on (T1,T2) hash join, FTS onT3, Hash join between T3 and outcome of #6 then #3 and hash jon between outcome of #4 and then final sort.

    I think in this case join order and execution plan order it the same, you doing hash joins of output of table scans and anothe hash joins (you have nothing to join before you did some).

    Regarding how it done in memory it more complex question .

    What the issue you trying to resolve?

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown

    I would say from in to out.

    This is probably the most acurate way to describe it.

    I don't think there is an "order of operation". Just "dependencies".

    Remember, the answer (your resulting rows) is streamed. AFAIK - each step does not have to be complete for "the next step" to do some work.

    My $0.02

    MK

  • ronald_2017
    ronald_2017 Member Posts: 456 Blue Ribbon
    edited December 2020

    It looks as if this execution plan is copied from https://jonathanlewis.wordpress.com/2010/12/10/quiz-night-10

    Yes it is. I updated my question.


    You haven't supplied a definition of what you mean by the expression "sequence of the following execution plan", but I hope you're thinking something roughly like "the order in which each line produces a rowsource to pass up to its parent"

    I am trying to learn in which order the execution plan perform each step.


    As the note points out, there were 8 differently shaped execution plans that could all have come from the same single join order.


    Yes, there are 8 different shaped execution plans. However, this one is the plan that take the most memory in the PGA because it build 3 hash table before joining any table, right? I'm trying to clarify this part.


    Thanks in advance.

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown

    this one is the plan that take the most memory in the PGA

    I suspect the "memory usage" would spill over to a TEMP tablespace.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,512 Gold Crown
    edited December 2020

    I am trying to learn in which order the execution plan perform each step.

    That information was set out in detail in the note (as repeated above).

    However, this one is the plan that take the most memory in the PGA because it build 3 hash table before joining any table, right?

    That is a good question - and one that means I might have to write a little follow-up note. In the article l made the point (highlighted here, preceded by a little context):

    Notice: the number of in-memory hash (build) tables we have in the first of the 8 plans at any instant after the first join starts is two and (no matter how many tables are involved in this pattern) the number of in-memory hash tables will always be two. The actual size of the two hash tables is a little unpredictable

    The pattern of the plan you've shown has an easily predictable memory demand - but that doesn't mean it has to be the largest consumer of PGA, in fact in some cases it will obviously be the smallest (that's "obvious" to the DBA, not necessarily to the optimizer).

    Consider an example where t1 is a FACT table and t2, ,t3, t4 are small dimension tables. The optmizer would probably follow the plan you showed above, and the three in-memory hash table would probably be relatively small, possibly each consisting of a short list of (id, description).

    Imagine, however that the optimizer decided to use a plan that used the result of the join between t1 and t2 to create an in-memory hash table (you'd hope the optimizer would get the arithmetic right but for this thought experiment - and often in real life - the consequences aren't so clear cut and the arithmetic goes wrong) and then used the result of the join with t3 to build the next in-memory hash table.

    Even one incorrectly placed intermediate result could mean that you use far more memory than you would for having three small in-memory hash tables in existence at the same time.

    Regards

    Jonathan Lewis

    ronald_2017
Sign In or Register to comment.