This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 26, 2010 8:17 AM by Taral RSS

Explain Plan Understanding

Taral Journeyer
Currently Being Moderated
Hi,

Is this both plan are same. Table names are change. Both execution wise would choose same plan of execution
No Hint
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                    
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                     
|   0 | INSERT STATEMENT       |             |  1285M|   326G|       |    45M  (1)|178:06:59 |       |       |                                                                                                                                                                                     
|   1 |  LOAD AS SELECT        | E           |       |       |       |            |          |       |       |                                                                                                                                                                                     
|*  2 |   HASH JOIN            |             |  1285M|   326G|  5153M|    45M  (1)|178:06:59 |       |       |                                                                                                                                                                                     
|   3 |    TABLE ACCESS FULL   | D           |   135M|  3607M|       |   254K  (2)| 00:59:17 |       |       |                                                                                                                                                                                     
|*  4 |    HASH JOIN           |             |  1261M|   287G|  2857M|    32M  (1)|124:52:03 |       |       |                                                                                                                                                                                     
|   5 |     TABLE ACCESS FULL  | C           |    76M|  1978M|       |   143K  (2)| 00:33:33 |       |       |                                                                                                                                                                                     
|*  6 |     HASH JOIN          |             |  1241M|   252G|  1727M|    20M  (1)| 78:33:50 |       |       |                                                                                                                                                                                    
|   7 |      TABLE ACCESS FULL | B           |    54M|  1099M|       | 23217   (4)| 00:05:26 |       |       |                                                                                                                                                                                    
|   8 |      PARTITION HASH ALL|             |  1241M|   227G|       |  3452K  (4)| 13:25:29 |     1 |    64 |                                                                                                                                                                                     
|   9 |       TABLE ACCESS FULL| A           |  1241M|   227G|       |  3452K  (4)| 13:25:29 |     1 |    64 |                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                     
Fix card for table A with 10M
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                    
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                     
|   0 | INSERT STATEMENT       |             |    10M|  2696M|       |  4578K  (1)| 17:48:26 |       |       |                                                                                                                                                                                     
|   1 |  LOAD AS SELECT        | E           |       |       |       |            |          |       |       |                                                                                                                                                                                     
|*  2 |   HASH JOIN            |             |    10M|  2696M|  2491M|  4578K  (1)| 17:48:26 |       |       |                                                                                                                                                                                     
|*  3 |    HASH JOIN           |             |    10M|  2374M|  2193M|  3996K  (1)| 15:32:36 |       |       |                                                                                                                                                                                    
|*  4 |     HASH JOIN          |             |    10M|  2079M|  1727M|  3636K  (1)| 14:08:30 |       |       |                                                                                                                                                                                    
|   5 |      TABLE ACCESS FULL | B           |    54M|  1099M|       | 23217   (4)| 00:05:26 |       |       |                                                                                                                                                                                     
|   6 |      PARTITION HASH ALL|             |    10M|  1878M|       |  3362K  (1)| 13:04:42 |     1 |    64 |                                                                                                                                                                                     
|   7 |       TABLE ACCESS FULL| A           |    10M|  1878M|       |  3362K  (1)| 13:04:42 |     1 |    64 |                                                                                                                                                                                     
|   8 |     TABLE ACCESS FULL  | C           |    76M|  1978M|       |   143K  (2)| 00:33:33 |       |       |                                                                                                                                                                                     
|   9 |    TABLE ACCESS FULL   | D           |   135M|  3607M|       |   254K  (2)| 00:59:17 |       |       |                                                                                                                                                                                    
--------------------------------------------------------------------------------------------------------------                                                   

Also, both have same predicates

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                             
   2 - access(A."ID"="D"."ID")                                                                                                                                                                                                                                                          
   3 - access("A"."E_ID"="C"."E_ID")                                                                                                                                                                                                                                                         
   4 - access("A"."M_ID"="B"."M_ID")                                                                                                                                   
If my understanding is right then

1. A Is hashed and then Join with B with hash join. While accessing B will apply predicate 4
2. Then result will be joined to C while with hash join while accessing C will apply using 3 access predicate
3. Then result will be applied to D
4. Then will load with direct path to E

Is not the case please explain both in details and also, please let me know which is best with some guidance and explanation
  • 1. Re: Explain Plan Understanding
    Anurag Tibrewal Guru
    Currently Being Moderated
    Taral Desai wrote:
    Hi,

    Is this both plan are same. Table names are change. Both execution wise would choose same plan of execution
    No Hint
    --------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
    | Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                    
    --------------------------------------------------------------------------------------------------------------                                                                                                                                                                                     
    |   0 | INSERT STATEMENT       |             |  1285M|   326G|       |    45M  (1)|178:06:59 |       |       |                                                                                                                                                                                     
    |   1 |  LOAD AS SELECT        | E           |       |       |       |            |          |       |       |                                                                                                                                                                                     
    |*  2 |   HASH JOIN            |             |  1285M|   326G|  5153M|    45M  (1)|178:06:59 |       |       |                                                                                                                                                                                     
    |   3 |    TABLE ACCESS FULL   | D           |   135M|  3607M|       |   254K  (2)| 00:59:17 |       |       |                                                                                                                                                                                     
    |*  4 |    HASH JOIN           |             |  1261M|   287G|  2857M|    32M  (1)|124:52:03 |       |       |                                                                                                                                                                                     
    |   5 |     TABLE ACCESS FULL  | C           |    76M|  1978M|       |   143K  (2)| 00:33:33 |       |       |                                                                                                                                                                                     
    |*  6 |     HASH JOIN          |             |  1241M|   252G|  1727M|    20M  (1)| 78:33:50 |       |       |                                                                                                                                                                                    
    |   7 |      TABLE ACCESS FULL | B           |    54M|  1099M|       | 23217   (4)| 00:05:26 |       |       |                                                                                                                                                                                    
    |   8 |      PARTITION HASH ALL|             |  1241M|   227G|       |  3452K  (4)| 13:25:29 |     1 |    64 |                                                                                                                                                                                     
    |   9 |       TABLE ACCESS FULL| A           |  1241M|   227G|       |  3452K  (4)| 13:25:29 |     1 |    64 |                                                                                                                                                                                     
    --------------------------------------------------------------------------------------------------------------                                                                                                                                                                                     
    Fix card for table A with 10M
    --------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
    | Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                    
    --------------------------------------------------------------------------------------------------------------                                                                                                                                                                                     
    |   0 | INSERT STATEMENT       |             |    10M|  2696M|       |  4578K  (1)| 17:48:26 |       |       |                                                                                                                                                                                     
    |   1 |  LOAD AS SELECT        | E           |       |       |       |            |          |       |       |                                                                                                                                                                                     
    |*  2 |   HASH JOIN            |             |    10M|  2696M|  2491M|  4578K  (1)| 17:48:26 |       |       |                                                                                                                                                                                     
    |*  3 |    HASH JOIN           |             |    10M|  2374M|  2193M|  3996K  (1)| 15:32:36 |       |       |                                                                                                                                                                                    
    |*  4 |     HASH JOIN          |             |    10M|  2079M|  1727M|  3636K  (1)| 14:08:30 |       |       |                                                                                                                                                                                    
    |   5 |      TABLE ACCESS FULL | B           |    54M|  1099M|       | 23217   (4)| 00:05:26 |       |       |                                                                                                                                                                                     
    |   6 |      PARTITION HASH ALL|             |    10M|  1878M|       |  3362K  (1)| 13:04:42 |     1 |    64 |                                                                                                                                                                                     
    |   7 |       TABLE ACCESS FULL| A           |    10M|  1878M|       |  3362K  (1)| 13:04:42 |     1 |    64 |                                                                                                                                                                                     
    |   8 |     TABLE ACCESS FULL  | C           |    76M|  1978M|       |   143K  (2)| 00:33:33 |       |       |                                                                                                                                                                                     
    |   9 |    TABLE ACCESS FULL   | D           |   135M|  3607M|       |   254K  (2)| 00:59:17 |       |       |                                                                                                                                                                                    
    --------------------------------------------------------------------------------------------------------------                                                   
    
    Also, both have same predicates
    
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
    
    2 - access(A."ID"="D"."ID")                                                                                                                                                                                                                                                          
    3 - access("A"."E_ID"="C"."E_ID")                                                                                                                                                                                                                                                         
    4 - access("A"."M_ID"="B"."M_ID")                                                                                                                                   
    If my understanding is right then

    1. A Is hashed and then Join with B with hash join. While accessing B will apply predicate 4
    2. Then result will be joined to C while with hash join while accessing C will apply using 3 access predicate
    3. Then result will be applied to D
    4. Then will load with direct path to E

    Is not the case please explain both in details and also, please let me know which is best with some guidance and explanation
    Both the explain plan are good when you compare the number of rows in each table.
    Here I see for same table optimizer is taking different number of rows in both the xplain plan.
    Are they both generated on same database or different different database?

    It is always better to do a hash on small table and join to the bigger table which it is doing in both the plan

    Regards
    Anurag
  • 2. Re: Explain Plan Understanding
    680087 Pro
    Currently Being Moderated
    Hi,

    no, the plans are different though both of them use the very same join order: B->A->C->D. The difference is the first plan scans tables as D->C->B->A while the second does B->A->C->D; plus the first plan uses 3 work areas at the same time for hash joins, while the second - just 1.
    I would recommend reading Christian Antognini's TOP Chapter 10 - it's the best description (that I'm aware of) of how to understand execution plan.
  • 3. Re: Explain Plan Understanding
    Taral Journeyer
    Currently Being Moderated
    Hello Timur,

    You have said

    "plus the first plan uses 3 work areas at the same time for hash joins, while the second - just 1."

    Can you please explain this.
  • 4. Re: Explain Plan Understanding
    637286 Explorer
    Currently Being Moderated
    I would recommend the second plan with the following reasons:
    - It did more efficient processing that way, works with smaller dataset and requires less temporary space
    - It seems a lot faster than 1st execution plan either.

    How is the actual execution time? Could you share the result for everyone's benefit?
  • 5. Re: Explain Plan Understanding
    Taral Journeyer
    Currently Being Moderated
    Well, Second plan we had used cardinal hint and set it to 10M.

    If you see all other data are same. From 1st plan we didn't set cardinality and it's taking data from table stats. While 2nd we have set 10k for table A thus there is difference.

    But i still can't understand meaning for using 1 hash area vs 3 and which is good and why
  • 6. Re: Explain Plan Understanding
    Taral Journeyer
    Currently Being Moderated
    Can you please help understanding workarea
  • 7. Re: Explain Plan Understanding
    CharlesHooper Expert
    Currently Being Moderated
    Taral Desai wrote:
    Can you please help understanding workarea
    A search of the documentation found this page:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/glossary.htm
    "work area: A private allocation of memory used for sorts, hash joins, and other operations that are memory-intensive. A sort operator uses a work area (the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input."

    There may be multiple active work areas in a single SQL statement. While not the original purpose of this blog article, it does show how to see the amount of memory in use for active work areas:
    http://hoopercharles.wordpress.com/2010/01/19/pga-memory-the-developers-secret-weapon-for-stealing-all-of-the-memory-in-the-server-2/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 8. Re: Explain Plan Understanding
    Taral Journeyer
    Currently Being Moderated
    Hello Sir,

    Thanks for this information but i was asking in reply of timur in his previous reply he had said

    *"plus the first plan uses 3 work areas at the same time for hash joins, while the second - just 1."*

    So, i need to know what does it mean and also some details of same with how this can affect performance. So, i can decide which is best plan to go for among this two
  • 9. Re: Explain Plan Understanding
    637286 Explorer
    Currently Being Moderated
    the first plan uses 3 work areas at the same time for hash joins, while the second - just 1.
    I may be miss somewhere, but both plans perform 3 hash join operations. None of them performs only 1 hash join and none of them performs 3 hash joins at the same time. Anybody pls CMIIW

    Edited by: jh77267 on Jan 25, 2010 3:00 AM
  • 10. Re: Explain Plan Understanding
    CharlesHooper Expert
    Currently Being Moderated
    Taral Desai wrote:
    Hello Sir,

    Thanks for this information but i was asking in reply of timur in his previous reply he had said

    *"plus the first plan uses 3 work areas at the same time for hash joins, while the second - just 1."*

    So, i need to know what does it mean and also some details of same with how this can affect performance. So, i can decide which is best plan to go for among this two
    You replied to my previous reply in less time than it took for me to put the reply together - I suspect that means that you are looking for an entirely different answer. If you have a copy of the book "Troubleshooting Oracle Performance" I highly recommend that you read pages 434 through 439 - those pages describe how hash joins work and describe work areas.

    Keep in mind that you are only looking at the optimizer's estimates for number of rows, time, memory usage, and temp tablespace usage. You are then attempting to conclude which plan is faster/better based on the original estimates and an altered plan with a hinted estimate - something is very wrong with this approach. Have you considered actually testing the performance?

    Timur stated "no, the plans are different though both of them use the very same join order: B->A->C->D. The difference is the first plan scans tables as D->C->B->A while the second does B->A->C->D; plus the first plan uses 3 work areas at the same time for hash joins, while the second - just 1."

    I might be slightly misunderstanding what Timur wrote. Essentially, the difference between the two plans is which table is the build input, and which table is the probe input. The first table (or row source) listed below the words HASH JOIN is the source for the hash table (the optimizer typically tries to select the smaller estimated row source of the two row sources as the source for the hash table). The second table (or row source) is fully scanned, probing the generated hash table in search of a match. By artifically altering the optimizer's estimated rows returned by table A, you have flipped which table (or row source) is the build input, and which table (or row source) is the probe input - this could significantly increase, or significantly decrease, or have no impact on the amount of time required for the query to execute, the amount of memory used, or the amount of temp space needed.

    I am not sure how Timur has determined that fewer work areas would be in use at one time with one plan compared to the other plan.

    My answer, as it was in my previous reply (although not explicitly stated), is to test the performance to see which execution plan is more efficient, rather than guessing. My blog article that I posted in the previous reply has SQL statements that may be used to see the number of work areas that are active at any point, as well as the amount of RAM and temp space in use. You could continue to guess, but why guess?

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 11. Re: Explain Plan Understanding
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >

    The bit about three workareas compared to one workarea may not be completely correct.

    In the first plan Oracle builds a hash table from table D, then another from table C, then a third from table B - then it selects a row from table A and probes hash table B, then hash table C then hash table D. So you get three hash tables in existence at once - which means three workareas in use.

    In the second plan Oracle builds a hash table from B and probes it with table A to produce a result set. This result set is used to create a hash table (which means that there are two hash tables in existence at once, hence two work areas). As the scan of table A completes the hash table of table B can be closed (down to one hash table and one workarea) and the build of the first result set hash completes. Oracle then scans table C and uses it to proble the first hash result, producing a second result set and building a hash table from it - which means we are back to two hash tables in existence at once. And so it proceeds: as each probe completes one hash table can be closed but the next probe starts and opens a new hash table.

    I think (but I'm not 100% sure) that Timur is incorrect when he says there will be just one workarea active - clearly I've stated that there will be one or two; however it is possible (perhaps only in some cases) that a result set is not hashed until the entire result set has been built. If so Timur would be correct. The exact behaviour may be version dependent, or dependent in some way on the sizes or patterns of the data sets involved.

    For any specific long running query you could check by finding the sql_id of the query and then querying v$sql_workarea_active+ as rapidly as possible to see what workareas appear and disappear.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 12. Re: Explain Plan Understanding
    Taral Journeyer
    Currently Being Moderated
    Thank you Jonathan And Charles for all information and spending time on my question. Now i have clear picture but still confirming

    I know as Charles told previousely also that benchmarking both would be best thing to find which if better but still one more thing i need to understand.

    1. In first plan 3 workarea would be open which would be very small hash structure of reletively small table then big table rather than in second after a result set for first hash join for this big table(A) why big because it's actually big but we had set cardinality hint to 10m but actually when it scans it will scan all data and then will generate joing this two table a big hash structure which can spill to disk as lack of space and every time to scan other small tables we need to go through this big hash structure. If i am understanding this right.

    Then hypothetically first plan would be better then second.
  • 13. Re: Explain Plan Understanding
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Taral Desai wrote:
    Thank you Jonathan And Charles for all information and spending time on my question. Now i have clear picture but still confirming

    I know as Charles told previousely also that benchmarking both would be best thing to find which if better but still one more thing i need to understand.

    1. In first plan 3 workarea would be open which would be very small hash structure of reletively small table then big table rather than in second after a result set for first hash join for this big table(A) why big because it's actually big but we had set cardinality hint to 10m but actually when it scans it will scan all data and then will generate joing this two table a big hash structure which can spill to disk as lack of space and every time to scan other small tables we need to go through this big hash structure. If i am understanding this right.

    Then hypothetically first plan would be better then second.
    If your statistics are reasonably accurate, then yes.

    You have fooled Oracle in the second plan into thinking that the first join will produce a (relatively) small result set - whereas the result set it produces will be about 250GB according to my estimate from the figures in your plan - so Oracle thinks it will be quite cheap to build a hash from the result when in fact it will be hugely expensive with a massive spill to disc.

    If you want to see what the 2nd plan would cost without hacking the cardinality, I think the following set of hints would get it:
    <ul>
    leading(a b c d)
    use_hash(b) swap_join_inputs(b)
    use_hash(c) no_swap_join_inputs(c)
    use_hash(d) no_swap_join_inputs(d)
    </ul>

    (I'm assuming you're using 10g, no_swap_join_inputs() didn't exist in 9i).


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 14. Re: Explain Plan Understanding
    Taral Journeyer
    Currently Being Moderated
    Thank you Sir for all the time you spent and as always I learn new things every time Charles, Jonathan and other guys replies.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points