1 2 Previous Next 15 Replies Latest reply: Oct 9, 2013 10:46 AM by jgarry RSS

    Explain plan donot match with execution path!! Performance issue!!

    953679

      Hi Experts,

       

      I see a strange behavior of execution path changing when ever gathering stats including indexes. I have  created stored out line as well,

      how ever explain plan is showing rite plan and actual execution is picking bad plan. Can any one help here. Version 9i . Comments please .

       

      Regards,

      Ram.P

        • 1. Re: Explain plan donot match with execution path!! Performance issue!!
          sb92075

          953679 wrote:

           

          Hi Experts,

           

          I see a strange behavior of execution path changing when ever gathering stats including indexes. I have  created stored out line as well,

          how ever explain plan is showing rite plan and actual execution is picking bad plan. Can any one help here. Version 9i . Comments please .

           

          Regards,

          Ram.P

           

           

           

          How do I ask a question on the forums?

          https://forums.oracle.com/message/9362002#9362002

           

          I don't know what you have.

          I don't know what you do.

          I don't know what you see.

          It is really, Really, REALLY difficult to fix a problem that can not be seen.

          use COPY & PASTE so we can see what you do & how Oracle responds.

          • 2. Re: Explain plan donot match with execution path!! Performance issue!!
            Hoek

            Please post relevant details.

            Follow the steps explained here:

            Oracle related stuff: Basic SQL statement performance diagnosis - HOW TO, step by step instructions

            And consider upgrading your database version from the previous century to a more recent one.

            • 3. Re: Explain plan donot match with execution path!! Performance issue!!
              jgarry

              Explain plan lies.  The query has to match exactly. Ask Tom Using Stored Outlines

              • 5. Re: Explain plan donot match with execution path!! Performance issue!!
                jgarry

                Credit where credit is due: Ask Tom explain plan lies

                I'm sure he said it earlier than that, but googlefail.

                • 6. Re: Explain plan donot match with execution path!! Performance issue!!
                  953679

                  Thanks for your response.

                   

                  I can not post entire source query and plan .  The problem is when i create an index oracle is picking up rite plan. When ever i gather stats on table lncluding indexes plan is getting changed. I have create an outline , explain plan is showing correct plan even after gather_stats due to outline. How ever actual execution is getting changed .

                   

                  Regards,

                  Ram.P

                  • 7. Re: Explain plan donot match with execution path!! Performance issue!!
                    953679

                    I have also tried by flushing shared_pool . How ever plan shows in explain_plan and actual execution is differnt only after collecting stats including indexes .

                    • 8. Re: Explain plan donot match with execution path!! Performance issue!!
                      sb92075

                      I can't post complete solution, but V11 CBO make much better choices than V9.

                      • 9. Re: Explain plan donot match with execution path!! Performance issue!!
                        jgarry

                        OK, we can't help too much if you can't show us your plan.  However, you know what your good plan is, so perhaps you can work out hints necessary to make your code do the right thing (Jonathan Lewis has written much about the mechanics of this).  Working out the correct statistics (perhaps you have some histogram issue, or a skew in your data the stats miss), or the actual correct stored outline would be better solutions, but sometimes the brutish method is called for.  Sometimes it will lead to understanding what was missed with the outline.

                        • 10. Re: Explain plan donot match with execution path!! Performance issue!!
                          953679

                          Hi All,

                           

                          I have also tried using index hint as well , how ever explain plan is picking up better plan where as execution path is not able to .. Remember this happens only when i gather stats on table including indexes on table_2 .enclosing sample plan.

                           

                          Bad plan

                           

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

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

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

                          |   0 | SELECT STATEMENT                      |                               | 92146 |    19M|       |   389K|

                          |   1 |  SORT GROUP BY                        |                               | 92146 |    19M|    41M|   389K|

                          |   2 |   NESTED LOOPS                        |                               | 92146 |    19M|       |   386K|

                          |*  3 |    HASH JOIN                          |                               | 92146 |    17M|       |   201K|

                          |   4 |     TABLE ACCESS FULL                 |    table_1              |  5683 |   110K|       |     7 |

                          |*  5 |     HASH JOIN SEMI                    |                               | 92146 |    15M|    15M|   201K|

                          |*  6 |      HASH JOIN                        |                               | 92146 |    14M|    13M|   201K|

                          |*  7 |       HASH JOIN SEMI                  |                               | 92146 |    12M|    11M|   199K|

                          |*  8 |        HASH JOIN                      |                               | 92146 |    10M|       |   199K|

                          |   9 |         INLIST ITERATOR               |                               |       |       |       |       |

                          |* 10 |          TABLE ACCESS BY INDEX ROWID  |   table_2                     |  1637 | 78576 |       |   622 |

                          |* 11 |           INDEX RANGE SCAN            |   table_2_IDX_002          |  1776 |       |       |     5 |

                          |* 12 |         HASH JOIN                     |                               |  1088M|    73G|       | 64388 |

                          |  13 |          MERGE JOIN CARTESIAN         |                               |  6225 |   200K|       |   536 |

                          |  14 |           TABLE ACCESS BY INDEX ROWID |     table_3                   |   519 |  8304 |       |    17 |

                          |* 15 |            INDEX RANGE SCAN           |     table_3_indx              |   519 |       |       |     6 |

                          |  16 |           BUFFER SORT                 |                               |    12 |   204 |       |   519 |

                          |  17 |            TABLE ACCESS BY INDEX ROWID|     table_4              |    12 |   204 |       |     1 |

                          |* 18 |             INDEX RANGE SCAN          |     table_4_indx      |    12 |       |       |       |

                          |  19 |          TABLE ACCESS FULL            |     table_5      |    30M|  1138M|       | 57154 |

                          |* 20 |        TABLE ACCESS FULL              |     table_6            |     3 |    51 |       |     4 |

                          |  21 |       TABLE ACCESS FULL               |     table_7      |   813K|    20M|       |  1144 |

                          |* 22 |      TABLE ACCESS FULL                |     table_8      |     3 |    51 |       |     4 |

                          |  23 |    TABLE ACCESS BY INDEX ROWID        |     table_9      |     1 |    21 |       |     2 |

                          |* 24 |     INDEX UNIQUE SCAN                 |     table_9_indx      |     1 |       |       |     1 |

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

                           

                          Regards,

                          Ram.P

                          • 11. Re: Explain plan donot match with execution path!! Performance issue!!
                            953679

                            Better plan

                             

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

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

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

                            |   0 | SELECT STATEMENT                      |                               | 40612 |  8764K|       |   133K|

                            |   1 |  SORT GROUP BY                        |                               | 40612 |  8764K|    18M|   133K|

                            |*  2 |   HASH JOIN                           |                               | 40612 |  8764K|       |   131K|

                            |   3 |    TABLE ACCESS FULL                  | table_1              |  5691 |   105K|       |     7 |

                            |*  4 |    HASH JOIN SEMI                     |                               | 40612 |  8011K|    11M|   131K|

                            |*  5 |     HASH JOIN                         |                               | 60918 |    10M|     9M|   131K|

                            |*  6 |      HASH JOIN SEMI                   |                               | 60918 |  9399K|    13M|   129K|

                            |*  7 |       HASH JOIN                       |                               | 91377 |    12M|       |   129K|

                            |   8 |        TABLE ACCESS BY INDEX ROWID    | table_3                       |    12 |   204 |       |     2 |

                            |*  9 |         INDEX RANGE SCAN              | table_3_indx                  |    12 |       |       |     1 |

                            |* 10 |        HASH JOIN                      |                               |  1340K|   158M|   146M|   129K|

                            |* 11 |         HASH JOIN                     |                               |  1340K|   131M|       | 65175 |

                            |* 12 |          TABLE ACCESS BY INDEX ROWID  | table_2              |     1 |    48 |       |    13 |

                            |  13 |           NESTED LOOPS                |                               |   528 | 33792 |       |  6764 |

                            |  14 |            TABLE ACCESS BY INDEX ROWID| table_4                       |   519 |  8304 |       |    17 |

                            |* 15 |             INDEX RANGE SCAN          | table_4_indx                  |   519 |       |       |     6 |

                            |  16 |            INLIST ITERATOR            |                               |       |       |       |       |

                            |* 17 |             INDEX RANGE SCAN          | table_2_IDX_002               |  1776 |       |       |     1 |

                            |  18 |          TABLE ACCESS FULL            | table_5                       |    30M|  1145M|       | 57448 |

                            |  19 |         TABLE ACCESS FULL             | table_9                      |    35M|   707M|       | 44474 |

                            |* 20 |       TABLE ACCESS FULL               | table_8                       |     1 |    17 |       |     4 |

                            |  21 |      TABLE ACCESS FULL                | table_7                       |   824K|    21M|       |  1144 |

                            |* 22 |     TABLE ACCESS FULL                 | table_8                       |     1 |    17 |       |     4 |

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

                             

                            Regards,

                            Ram.P

                            • 12. Re: Explain plan donot match with execution path!! Performance issue!!
                              817202

                              Just confirm that whether the table is updated or not

                              • 13. Re: Explain plan donot match with execution path!! Performance issue!!
                                953679

                                Hi VK,

                                 

                                Table is not updated. It is just a select. The problem is access more bytes in wrong plan i.e even though using index 73G of data is scanned, where as undergoing less data scan in MB in case of optimal plan using index.

                                 

                                wrong plan:

                                 

                                * 10 |          TABLE ACCESS BY INDEX ROWID  |   table_2                     |  1637 | 78576 |       |   622 |

                                |* 11 |           INDEX RANGE SCAN            |   table_2_IDX_002          |  1776 |       |       |     5 |

                                |* 12 |         HASH JOIN                     |                               |  1088M|    73G|       | 64388 |

                                 

                                 

                                Correct Plan;

                                 

                                |*  7 |       HASH JOIN                       |                               | 91377 |    12M|       |   129K|

                                |   8 |        TABLE ACCESS BY INDEX ROWID    | table_3                       |    12 |   204 |       |     2 |

                                |*  9 |         INDEX RANGE SCAN              | table_3_indx                  |    12 |       |       |     1 |

                                |* 10 |        HASH JOIN                      |                               |  1340K|   158M|   146M|   129K|

                                |* 11 |         HASH JOIN                     |                               |  1340K|   131M|       | 65175 |

                                |* 12 |          TABLE ACCESS BY INDEX ROWID  | table_2              |     1 |    48 |       |    13 |

                                |  13 |           NESTED LOOPS                |                               |   528 | 33792 |       |  6764 |

                                |  14 |            TABLE ACCESS BY INDEX ROWID| table_4                       |   519 |  8304 |       |    17 |

                                |* 15 |             INDEX RANGE SCAN          | table_4_indx                  |   519 |       |       |     6 |

                                |  16 |            INLIST ITERATOR            |                               |       |       |       |       |

                                |* 17 |             INDEX RANGE SCAN          | table_2_IDX_002               |  1776 |       |       |     1 |

                                 

                                Regards,

                                Ram.P

                                • 14. Re: Explain plan donot match with execution path!! Performance issue!!
                                  Dom Brooks

                                  It's not strange, it's relatively normal.

                                   

                                  It's so normal that there are two template tuning threads designed to either:

                                  a) help you to help us to help you or

                                  b) to help yourself.

                                   

                                  See:

                                  HOW TO: Post a SQL statement tuning request - template posting

                                  When your query takes too long ...

                                   

                                  Stop wasting time posting incomplete information.

                                  Either post everything that's needed from threads above or use the same information to figure it out yourself.

                                   

                                  But if you keep posting snippets and a fraction of what is required for others to help, you're just going to get guesswork, possibly irrelevant, and it will take longer to get to the answer

                                  1 2 Previous Next