1 2 Previous Next 19 Replies Latest reply: Jul 31, 2013 6:35 AM by michaelrozar17 RSS

    Which execution plan is best?

    Raghav.786

      Hi Experts

      Could you please help me to idetify which of the below execution plan is best (I mean fastest running query)? When we look at the cost, we can say second one is faster than first. But one of my DBA said that first one is faster. Is it true? Please help
      Due to some reason I am unable to provide you the actual queries.

       

      Plan # 1

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

      | Id  | Operation                                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

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

      |   0 | SELECT STATEMENT                                  |                            |       |       |   118 (100)|          |

      |   1 |  FAST DUAL                                        |                            |     1 |       |     2   (0)| 00:00:01 |

      |   2 |  VIEW                                             |                            |     1 |  1349 |   118   (1)| 00:00:02 |

      |   3 |   COUNT STOPKEY                                   |                            |       |       |            |          |

      |   4 |    VIEW                                           |                            |     1 |  1349 |   118   (1)| 00:00:02 |

      |   5 |     WINDOW SORT                                   |                            |     1 |   611 |   118   (1)| 00:00:02 |

      |   6 |      NESTED LOOPS OUTER                           |                            |     1 |   611 |   118   (1)| 00:00:02 |

      |   7 |       NESTED LOOPS OUTER                          |                            |     1 |   583 |   116   (1)| 00:00:02 |

      |   8 |        NESTED LOOPS OUTER                         |                            |     1 |   459 |   114   (1)| 00:00:02 |

      |   9 |         NESTED LOOPS                              |                            |     1 |   443 |   112   (1)| 00:00:02 |

      |  10 |          NESTED LOOPS                             |                            |     1 |   341 |   111   (1)| 00:00:02 |

      |  11 |           NESTED LOOPS SEMI                       |                            |     1 |   292 |   110   (1)| 00:00:02 |

      |  12 |            HASH JOIN RIGHT SEMI                   |                            |     1 |   265 |   109   (1)| 00:00:02 |

      |  13 |             VIEW                                  | VW_NSO_1                   |     1 |     9 |    22   (0)| 00:00:01 |

      |  14 |              FILTER                               |                            |       |       |            |          |

      |  15 |               HASH JOIN RIGHT OUTER               |                            |     1 |   668 |    22   (0)| 00:00:01 |

      |  16 |                TABLE ACCESS BY INDEX ROWID        | USER_PREFERENCES           |     3 |    93 |     2   (0)| 00:00:01 |

      |  17 |                 INDEX RANGE SCAN                  | IDX_USER_PREFS_UIDCAT      |     3 |       |     1   (0)| 00:00:01 |

      |  18 |                VIEW                               |                            |    68 | 43316 |    20   (0)| 00:00:01 |

      |  19 |                 NESTED LOOPS                      |                            |       |       |            |          |

      |  20 |                  NESTED LOOPS                     |                            |     3 |   576 |    18   (0)| 00:00:01 |

      |  21 |                   NESTED LOOPS                    |                            |     3 |   357 |    15   (0)| 00:00:01 |

      |  22 |                    MERGE JOIN CARTESIAN           |                            |     4 |   348 |    11   (0)| 00:00:01 |

      |  23 |                     NESTED LOOPS                  |                            |       |       |            |          |

      |  24 |                      NESTED LOOPS                 |                            |     1 |    67 |     6   (0)| 00:00:01 |

      |  25 |                       NESTED LOOPS OUTER          |                            |     1 |    50 |     4   (0)| 00:00:01 |

      |  26 |                        INDEX RANGE SCAN           | PROFILE_ID_INDX_DUM        |     1 |    19 |     2   (0)| 00:00:01 |

      |  27 |                        TABLE ACCESS BY INDEX ROWID| USER_PREFERENCES           |     1 |    31 |     2   (0)| 00:00:01 |

      |  28 |                         INDEX RANGE SCAN          | IDX_USER_PREFS_UIDCAT      |     1 |       |     1   (0)| 00:00:01 |

      |  29 |                       INDEX RANGE SCAN            | ENT_USER_ID_DUE            |     2 |       |     1   (0)| 00:00:01 |

      |  30 |                      TABLE ACCESS BY INDEX ROWID  | DMS_USER_ENTITLEMENTS      |     1 |    17 |     2   (0)| 00:00:01 |

      |  31 |                     BUFFER SORT                   |                            |     7 |   140 |     9   (0)| 00:00:01 |

      |  32 |                      TABLE ACCESS FULL            | COLLECTIONLIST             |     7 |   140 |     5   (0)| 00:00:01 |

      |  33 |                    INDEX RANGE SCAN               | IDX_DTCOLLS_CIDCTDT        |     1 |    32 |     1   (0)| 00:00:01 |

      |  34 |                     SORT AGGREGATE                |                            |     1 |    41 |            |          |

      |  35 |                      TABLE ACCESS BY INDEX ROWID  | DMS_USER_MASTER            |     1 |    41 |     2   (0)| 00:00:01 |

      |  36 |                       INDEX UNIQUE SCAN           | UN_DMS_USER_MASTER_USER_ID |     1 |       |     1   (0)| 00:00:01 |

      |  37 |                   INDEX UNIQUE SCAN               | DTM_DC_DT                  |     1 |       |     0   (0)|          |

      |  38 |                  TABLE ACCESS BY INDEX ROWID      | DOCTYPE_MASTER             |     1 |    73 |     1   (0)| 00:00:01 |

      |  39 |             TABLE ACCESS BY INDEX ROWID           | INDEX_MASTER               |    54 | 13824 |    86   (0)| 00:00:02 |

      |  40 |              INDEX SKIP SCAN                      | IM_INDMASTER_HOGAN_BASENO  |    79 |       |    18   (0)| 00:00:01 |

      |  41 |            INDEX RANGE SCAN                       | T_VIS_1000074910_IOT       |   215 |  5805 |     1   (0)| 00:00:01 |

      |  42 |           TABLE ACCESS BY INDEX ROWID             | ENTITLEMENT_GROUP_MASTER_A |     1 |    49 |     1   (0)| 00:00:01 |

      |  43 |            INDEX UNIQUE SCAN                      | EGM_PMA_ENT_NO             |     1 |       |     0   (0)|          |

      |  44 |          TABLE ACCESS BY INDEX ROWID              | DOCTYPE_MASTER             |     1 |   102 |     1   (0)| 00:00:01 |

      |  45 |           INDEX UNIQUE SCAN                       | DTM_DC_DT                  |     1 |       |     0   (0)|          |

      |  46 |         INDEX RANGE SCAN                          | TD_IM_TID_VER              |     1 |    16 |     2   (0)| 00:00:01 |

      |  47 |        TABLE ACCESS BY INDEX ROWID                | TRACKING_DATA_MASTER       |     1 |   124 |     2   (0)| 00:00:01 |

      |  48 |         INDEX UNIQUE SCAN                         | TRACK_ID_PK                |     1 |       |     1   (0)| 00:00:01 |

      |  49 |       TABLE ACCESS BY INDEX ROWID                 | BANKER_MASTER_A            |     1 |    28 |     2   (0)| 00:00:01 |

      |  50 |        INDEX UNIQUE SCAN                          | IDX_BM_A_PERSNO            |     1 |       |     1   (0)| 00:00:01 |

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

      Plan # 2

       

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

      | Id  | Operation                                          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

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

      |   0 | SELECT STATEMENT                                   |                            |       |       |    84 (100)|          |

      |   1 |  FAST DUAL                                         |                            |     1 |       |     2   (0)| 00:00:01 |

      |   2 |  VIEW                                              |                            |     1 |  1349 |    84   (3)| 00:00:02 |

      |   3 |   COUNT STOPKEY                                    |                            |       |       |            |          |

      |   4 |    VIEW                                            |                            |     1 |  1349 |    84   (3)| 00:00:02 |

      |   5 |     WINDOW SORT                                    |                            |     1 |   611 |    84   (3)| 00:00:02 |

      |   6 |      NESTED LOOPS OUTER                            |                            |     1 |   611 |    84   (3)| 00:00:02 |

      |   7 |       NESTED LOOPS OUTER                           |                            |     1 |   487 |    82   (3)| 00:00:01 |

      |   8 |        NESTED LOOPS OUTER                          |                            |     1 |   459 |    80   (3)| 00:00:01 |

      |   9 |         NESTED LOOPS SEMI                          |                            |     1 |   443 |    78   (3)| 00:00:01 |

      |  10 |          NESTED LOOPS                              |                            |     1 |   416 |    77   (3)| 00:00:01 |

      |  11 |           NESTED LOOPS                             |                            |     1 |   314 |    76   (3)| 00:00:01 |

      |  12 |            NESTED LOOPS                            |                            |     1 |   265 |    75   (3)| 00:00:01 |

      |  13 |             VIEW                                   | VW_NSO_1                   |     1 |     9 |    19   (6)| 00:00:01 |

      |  14 |              HASH UNIQUE                           |                            |     1 |   668 |            |          |

      |  15 |               FILTER                               |                            |       |       |            |          |

      |  16 |                HASH JOIN RIGHT OUTER               |                            |     1 |   668 |    19   (6)| 00:00:01 |

      |  17 |                 TABLE ACCESS BY INDEX ROWID        | USER_PREFERENCES           |     3 |    93 |     2   (0)| 00:00:01 |

      |  18 |                  INDEX RANGE SCAN                  | IDX_USER_PREFS_UIDCAT      |     3 |       |     1   (0)| 00:00:01 |

      |  19 |                 VIEW                               |                            |    15 |  9555 |    16   (0)| 00:00:01 |

      |  20 |                  MERGE JOIN OUTER                  |                            |     1 |   192 |    14   (0)| 00:00:01 |

      |  21 |                   NESTED LOOPS                     |                            |       |       |            |          |

      |  22 |                    NESTED LOOPS                    |                            |     1 |   161 |    12   (0)| 00:00:01 |

      |  23 |                     NESTED LOOPS                   |                            |     1 |    88 |    11   (0)| 00:00:01 |

      |  24 |                      MERGE JOIN CARTESIAN          |                            |     2 |   112 |     9   (0)| 00:00:01 |

      |  25 |                       NESTED LOOPS                 |                            |       |       |            |          |

      |  26 |                        NESTED LOOPS                |                            |     1 |    36 |     4   (0)| 00:00:01 |

      |  27 |                         INDEX RANGE SCAN           | PROFILE_ID_INDX_DUM        |     1 |    19 |     2   (0)| 00:00:01 |

      |  28 |                         INDEX RANGE SCAN           | ENT_USER_ID_DUE            |     2 |       |     1   (0)| 00:00:01 |

      |  29 |                        TABLE ACCESS BY INDEX ROWID | DMS_USER_ENTITLEMENTS      |     1 |    17 |     2   (0)| 00:00:01 |

      |  30 |                       BUFFER SORT                  |                            |     4 |    80 |     7   (0)| 00:00:01 |

      |  31 |                        INLIST ITERATOR             |                            |       |       |            |          |

      |  32 |                         TABLE ACCESS BY INDEX ROWID| COLLECTIONLIST             |     4 |    80 |     5   (0)| 00:00:01 |

      |  33 |                          INDEX UNIQUE SCAN         | INDX_COLLLIST_COLLID       |     4 |       |     1   (0)| 00:00:01 |

      |  34 |                      INDEX RANGE SCAN              | IDX_DTCOLLS_CIDCTDT        |     1 |    32 |     1   (0)| 00:00:01 |

      |  35 |                       SORT AGGREGATE               |                            |     1 |    41 |            |          |

      |  36 |                        TABLE ACCESS BY INDEX ROWID | DMS_USER_MASTER            |     1 |    41 |     2   (0)| 00:00:01 |

      |  37 |                         INDEX UNIQUE SCAN          | UN_DMS_USER_MASTER_USER_ID |     1 |       |     1   (0)| 00:00:01 |

      |  38 |                     INDEX UNIQUE SCAN              | DTM_DC_DT                  |     1 |       |     0   (0)|          |

      |  39 |                    TABLE ACCESS BY INDEX ROWID     | DOCTYPE_MASTER             |     1 |    73 |     1   (0)| 00:00:01 |

      |  40 |                   BUFFER SORT                      |                            |     1 |    31 |    13   (0)| 00:00:01 |

      |  41 |                    TABLE ACCESS BY INDEX ROWID     | USER_PREFERENCES           |     1 |    31 |     2   (0)| 00:00:01 |

      |  42 |                     INDEX RANGE SCAN               | IDX_USER_PREFS_UIDCAT      |     1 |       |     1   (0)| 00:00:01 |

      |  43 |             TABLE ACCESS BY INDEX ROWID            | INDEX_MASTER               |     1 |   256 |    55   (0)| 00:00:01 |

      |  44 |              INDEX RANGE SCAN                      | IM_CAT_TYPE_NUM_VER_STAT   |    63 |       |    27   (0)| 00:00:01 |

      |  45 |            TABLE ACCESS BY INDEX ROWID             | ENTITLEMENT_GROUP_MASTER_B |     1 |    49 |     1   (0)| 00:00:01 |

      |  46 |             INDEX UNIQUE SCAN                      | EGM_PMB_ENT_NO             |     1 |       |     0   (0)|          |

      |  47 |           TABLE ACCESS BY INDEX ROWID              | DOCTYPE_MASTER             |     1 |   102 |     1   (0)| 00:00:01 |

      |  48 |            INDEX UNIQUE SCAN                       | DTM_DC_DT                  |     1 |       |     0   (0)|          |

      |  49 |          INDEX RANGE SCAN                          | T_VIS_1010127276_IOT       |   126K|  3335K|     1   (0)| 00:00:01 |

      |  50 |         INDEX RANGE SCAN                           | TD_IM_TID_VER              |     1 |    16 |     2   (0)| 00:00:01 |

      |  51 |        TABLE ACCESS BY INDEX ROWID                 | BANKER_MASTER_B            |     1 |    28 |     2   (0)| 00:00:01 |

      |  52 |         INDEX UNIQUE SCAN                          | IDX_BM_B_PERSNO            |     1 |       |     1   (0)| 00:00:01 |

      |  53 |       TABLE ACCESS BY INDEX ROWID                  | TRACKING_DATA_MASTER       |     1 |   124 |     2   (0)| 00:00:01 |

      |  54 |        INDEX UNIQUE SCAN                           | TRACK_ID_PK                |     1 |       |     1   (0)| 00:00:01 |

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

       

        • 1. Re: Which execution plan is best?
          michaelrozar17

          Tom has said that we cannot compare the Cost between execution plans (read below) to know which is efficient one. Try to execute and check which query runs faster..( however you would know this :-))

          Myself even confused on how to determine an efficient plan or query except by its execution time.

          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:313416745628

          • 2. Re: Which execution plan is best?
            Sven W.

            Since there is a count stopkey in the plan it looks like you are using a condition like <= ROWNUM . Be aware that because of that, the results of the select might be different, if the execution plan changes. Depends a lot on the sorting that you also seem to do (WINDOW SORT).

             

            Further more nested loops are in general only good, if you fetch a very small dataset. As soon as you have larger results, then hash joins might be much better. But that is the CBOs decision to take. Therefore don't try to force any special execution plan.

             


            • 3. Re: Which execution plan is best?
              Nikolay Savvinov

              Hi,

               

              it's impossible to find which plan is better by just looking at them. All you can do is say which one has higher cost. Cost is basically optimizer estimate of how long the query would take. If all optimizer assumptions are correct, then this estimate is accurate, but this is not always the case.

               

              Why don't you run the query with both plans and compare?

               

              Best regards,

                Nikolay

              • 4. Re: Which execution plan is best?
                Hoek

                Well, perhaps your DBA means the second query is range scanning 146K rows (line 49) and the first query does not. So, less work could be considered 'faster'.

                However, if we look at the timings then there's no (noticable) difference.

                • 5. Re: Which execution plan is best?
                  BluShadow

                  Better would be to look at the execution traces rather than the explain plans.  As already mentioned, the plans are just the optimizer's 'best guess' as to how to execute the queries based on the information it has.  The actual execution of the queries and which is faster may be different from the plan if the information it had is not as accurate as is needed.  There's more to performance tuning than just looking at the plans.

                  • 6. Re: Which execution plan is best?
                    michaelrozar17

                    As already mentioned, the plans are just the optimizer's 'best guess' as to how to execute the queries based on the information it has

                    So even the execution plan available in V$SQL_PLAN is not the final one used by the Optimizer?

                    • 7. Re: Which execution plan is best?
                      Hoek

                      If you want to know the 'real deal', then use trace/tkprof.

                      The Tom Kyte Blog: When the explanation doesn&amp;#39;t sound quite right...

                      • 8. Re: Which execution plan is best?
                        Jonathan Lewis

                        michaelrozar17 wrote:

                         

                        As already mentioned, the plans are just the optimizer's 'best guess' as to how to execute the queries based on the information it has

                        So even the execution plan available in V$SQL_PLAN is not the final one used by the Optimizer?

                        v$sql_plan reports something that actually happened - and when you have trace enabled the "Rowsource Operation" session generated in the tkprof output is simply a formatted copy of a partial dump of v$sql_plan that went in trace file.  (Technically it's probably v$sql_plan_statistics_all - but the plan bit is the same.)

                         

                        Regards

                        Jonathan Lewis

                        Now on Twitter: @jloracle

                        • 9. Re: Which execution plan is best?
                          michaelrozar17

                          Thank you Jonathan :-). From what you said I infer that we can rely on the plan available in V$SQL_PLAN and also we could use dbms_xplan.display_cursor package to view the actual explain plan. I believe dbms_xplan.display_cursor takes data from V$SQL_PLAN and few other fixed tables as well.

                          As a developer we do not usually have access to TKPROF/Trace so I would rely on dbms_xplan.display_cursor for execution plan.

                          • 10. Re: Which execution plan is best?
                            Jonathan Lewis

                            michaelrozar17 wrote:

                             

                            Thank you Jonathan :-). From what you said I infer that we can rely on the plan available in V$SQL_PLAN and also we could use dbms_xplan.display_cursor package to view the actual explain plan. I believe dbms_xplan.display_cursor takes data from V$SQL_PLAN and few other fixed tables as well.

                            As a developer we do not usually have access to TKPROF/Trace so I would rely on dbms_xplan.display_cursor for execution plan.

                            That is correct; you just need to be aware that a single statement could end up with multiple child cursors which may all have different execution plans - so you need to know that you're looking at the right one, so if you're testing you might add a bit of unique identifying text to the end of any hints you supply. It's also possible to be fooled if your plan gets aged out of the library cache before you review it but some other plan is still there - timing becomes important.

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Which execution plan is best?
                              michaelrozar17

                              Can you please answer this.

                              1. What are the measure to determine a efficient plan ?

                              2. Do the statistics information from AUTOTRACE: db block gets and consistent gets play vital role in affecting performance of a query?

                              • 12. Re: Which execution plan is best?
                                BluShadow

                                michaelrozar17 wrote:

                                 

                                Can you please answer this.

                                1. What are the measure to determine a efficient plan ?

                                LOL! If it was that easy, then the optimizer would include such measures and you'd always get the most optimal plan.

                                 

                                2. Do the statistics information from AUTOTRACE: db block gets and consistent gets play vital role in affecting performance of a query?

                                Yes, the 'gets' are part of the I/O of the query and I/O is one of the biggest parts of performance due to the time it takes to read and write data to the disks.  Of course data can be cached, so determining the proper I/O for a query can be misleading if the data is already in cache.

                                • 13. Re: Which execution plan is best?
                                  michaelrozar17

                                  michaelrozar17 wrote:

                                   

                                  Can you please answer this.

                                  1. What are the measure to determine a efficient plan ?

                                  LOL! If it was that easy, then the optimizer would include such measures and you'd always get the most optimal plan.

                                  I meant from a developer's point of view from the available execution information like Execution plan, AUTOTRACE, TKPROF (not always available to a developer), what are the parameters we must take into consider for a query's performance ? As it's said in Tom's AskTom that we cant take into account the Cost in execution plan as its merely a mathematically calculated number, so if Cost does not help then what other parameters we should consider ?

                                  2. Do the statistics information from AUTOTRACE: db block gets and consistent gets play vital role in affecting performance of a query?

                                  Yes, the 'gets' are part of the I/O of the query and I/O is one of the biggest parts of performance due to the time it takes to read and write data to the disks.  Of course data can be cached, so determining the proper I/O for a query can be misleading if the data is already in cache.

                                  But aren't these are logical I/O's ? AFAIK consistent get is read from Undo segment and db block get is read from from the DB block ( which may or may not be present in the buffer cache). Please correct me if i am wrong.

                                  • 14. Re: Which execution plan is best?
                                    Nikolay Savvinov

                                    Hi,

                                     

                                    1) on a simplistic level, one can say that query tuning is minimizing I/O operations per row retrieved. So the most important characteristics of an execution plan are consistent gets (i.e. total number of logical reads) and disk reads (number of logical reads that result in disk I/O). Keep in mind that you cannot get these from explain plan because explain plan only tell you optimizer's idea of what would happen (you can only find out what actually happened after query has been executed)

                                    2) don't read too much into the word "consistent". "consistent gets" are simply logical reads. they include reads from UNDO, but aren't limited to them

                                     

                                    Best regards,

                                    Nikolay

                                    1 2 Previous Next