2 Replies Latest reply: Oct 9, 2012 4:38 AM by 888695 RSS

    strange behavior of explain plan

    888695
      Hello

      I am facing a strange behavior of the explain plan. What i mean!

      I have a query which runs very fast and it is well optimized. the explain plan shows very good results in cost and execution time.

      when i am running the query for a second or a third time is appearing a different explain plan with not such a good cost and execution time.

      Could you please guide me what it might be wrong???

      thanks a lot
        • 1. Re: strange behavior of explain plan
          BluShadow
          What's wrong is that you haven't provided any details for us to help you.

          {message:id=9360002}

          {message:id=9360003}
          • 2. Re: strange behavior of explain plan
            888695
            sorry for any incovinience

            here is th plan when i am running the query for first time

            Plan hash value: 2034516081

            ----------------------------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            ----------------------------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 10 (100)| |
            |* 1 | COUNT STOPKEY | | | | | |
            | 2 | VIEW | | 1 | 525 | 10 (10)| 00:00:01 |
            |* 3 | SORT UNIQUE STOPKEY | | 1 | 321 | 10 (10)| 00:00:01 |
            |* 4 | FILTER | | | | | |
            | 5 | SORT GROUP BY | | 1 | 321 | 10 (10)| 00:00:01 |
            | 6 | NESTED LOOPS | | 1 | 321 | 9 (0)| 00:00:01 |
            | 7 | NESTED LOOPS | | 1 | 261 | 7 (0)| 00:00:01 |
            | 8 | NESTED LOOPS | | 1 | 218 | 6 (0)| 00:00:01 |
            | 9 | NESTED LOOPS | | 1 | 175 | 4 (0)| 00:00:01 |
            | 10 | NESTED LOOPS | | 1 | 120 | 3 (0)| 00:00:01 |
            | 11 | INLIST ITERATOR | | | | | |
            | 12 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_METADATA | 1 | 43 | 2 (0)| 00:00:01 |
            |* 13 | INDEX RANGE SCAN | DOCUMENT_KEY_INDEX | 1 | | 2 (0)| 00:00:01 |
            |* 14 | TABLE ACCESS BY INDEX ROWID | DOCUMENT | 1 | 77 | 1 (0)| 00:00:01 |
            |* 15 | INDEX UNIQUE SCAN | SYS_C0017662 | 1 | | 1 (0)| 00:00:01 |
            | 16 | TABLE ACCESS BY INDEX ROWID | ORIGINAL_NOTIFICATION | 1 | 55 | 1 (0)| 00:00:01 |
            |* 17 | INDEX UNIQUE SCAN | FOLDER_PATH | 1 | | 1 (0)| 00:00:01 |
            |* 18 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_METADATA | 1 | 43 | 2 (0)| 00:00:01 |
            |* 19 | INDEX RANGE SCAN | DOCUMENT_ID | 19 | | 1 (0)| 00:00:01 |
            |* 20 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_METADATA | 1 | 43 | 1 (0)| 00:00:01 |
            |* 21 | INDEX RANGE SCAN | DOCUMENT_ID | 19 | | 1 (0)| 00:00:01 |
            |* 22 | TABLE ACCESS BY INDEX ROWID | DOCUMENT | 3 | 180 | 2 (0)| 00:00:01 |
            |* 23 | INDEX RANGE SCAN | SYS_C0017663 | 3 | | 1 (0)| 00:00:01 |
            ----------------------------------------------------------------------------------------------------------------


            and this the explain plan when i am running the query for second time

            -------------------------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            -------------------------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 609 (100)| |
            |* 1 | COUNT STOPKEY | | | | | |
            | 2 | VIEW | | 1 | 525 | 609 (1)| 00:00:08 |
            |* 3 | SORT UNIQUE STOPKEY | | 1 | 232 | 608 (1)| 00:00:08 |
            | 4 | NESTED LOOPS | | 1 | 232 | 607 (1)| 00:00:08 |
            | 5 | NESTED LOOPS | | 1 | 219 | 605 (1)| 00:00:08 |
            | 6 | NESTED LOOPS | | 1 | 182 | 604 (1)| 00:00:08 |
            | 7 | NESTED LOOPS | | 1 | 145 | 602 (1)| 00:00:08 |
            | 8 | NESTED LOOPS | | 1 | 102 | 601 (1)| 00:00:08 |
            |* 9 | VIEW | index$_join$_006 | 1 | 37 | 600 (1)| 00:00:08 |
            |* 10 | HASH JOIN | | | | | |
            |* 11 | INDEX RANGE SCAN | DOCUMENT_KEY_INDEX | 1 | 37 | 38 (6)| 00:00:01 |
            | 12 | INDEX FAST FULL SCAN | DOCUMENT_ID | 1 | 37 | 701 (1)| 00:00:09 |
            |* 13 | TABLE ACCESS BY INDEX ROWID| DOCUMENT | 1 | 65 | 1 (0)| 00:00:01 |
            |* 14 | INDEX UNIQUE SCAN | SYS_C0017662 | 1 | | 1 (0)| 00:00:01 |
            | 15 | TABLE ACCESS BY INDEX ROWID | ORIGINAL_NOTIFICATION | 1 | 43 | 1 (0)| 00:00:01 |
            |* 16 | INDEX UNIQUE SCAN | FOLDER_PATH | 1 | | 1 (0)| 00:00:01 |
            |* 17 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_METADATA | 1 | 37 | 2 (0)| 00:00:01 |
            |* 18 | INDEX RANGE SCAN | DOCUMENT_ID | 19 | | 1 (0)| 00:00:01 |
            |* 19 | TABLE ACCESS BY INDEX ROWID | DOCUMENT_METADATA | 1 | 37 | 1 (0)| 00:00:01 |
            |* 20 | INDEX RANGE SCAN | DOCUMENT_ID | 19 | | 1 (0)| 00:00:01 |
            |* 21 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 13 | 2 (0)| 00:00:01 |
            | 22 | SORT AGGREGATE | | 1 | 60 | | |
            |* 23 | TABLE ACCESS BY INDEX ROWID | DOCUMENT | 3 | 180 | 2 (0)| 00:00:01 |
            |* 24 | INDEX RANGE SCAN | SYS_C0017663 | 3 | | 1 (0)| 00:00:01 |
            -------------------------------------------------------------------------------------------------------------

            the second explain plan its different from the first one

            thanks a lot