1 2 Previous Next 26 Replies Latest reply on Jun 23, 2014 10:08 AM by NightWing

    Should I wait until the finish of the execution time of the query for execution plan?

      Hello Experts,

       

      I want to see the execution plan for the below query. However, it does takes more than 3 hours. Should I wait the whole time in order to see the execution plan?

       

      Note: EXPLAIN PLAN FOR doesn't work. (I mean I cannot see the actual row number, etc. with EXPLAIN PLAN FOR clause)

       

      You can see the output of the execution plan after I cancel execution after 1 minute.

       

      My first question is: What should I do in order to see the execution plan for long time execution queries?

       

      2nd question: When I cancel the query while running in order to see the execution plan, will I see the accurate execution plan or the wrong values? Because the first execution plan seems inaccurate, what do you think?

       

      3rd question: Why EXPLAIN PLAN FOR clause doesn't work? Also, Should I use EXPLAIN PLAN FOR clause for this scenerio? Can I see the execution result for long time queries without it?

       

       

      Thnaks for your help.

       

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      PL/SQL Release 11.2.0.2.0 - Production

      CORE 11.2.0.2.0 Production

      TNS for Linux: Version 11.2.0.2.0 - Production

      NLSRTL Version 11.2.0.2.0 - Production

       

       

      select /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */ J.INVOICEACCOUNT, J.INVOICEID, J.INVOICEDATE, (T.LINEAMOUNT+T.LINEAMOUNTTAX) price

      from custinvoicejour j join custinvoicetrans t on

      substr(nls_lower(j.dataareaid),1,7) = substr(nls_lower(t.dataareaid),1,7) and

      substr(nls_lower(J.INVOICEID),1,25) = substr(nls_lower(t.INVOICEID),1,25)

      where

      substr(nls_lower(T.DATAAREAID),1,7) = '201' and T.AVBROCHURELINENUM = 29457

      and substr(nls_lower(j.dataareaid),1,7) = '201' and

      J.INVOICEACCOUNT in

      (select CE.ACCOUNTNUM from drmpos.avtr_seg_cust_campend ce where CE.CAMPAIGN = '201406' and CE.SEGMENT_LEVEL in ('D', 'E'))

      and J.AVAWARDSALES > 190

      and substr(nls_lower(J.AVBILLINGCAMPAIGN),1,13) = '201406'

      and J.INVOICEDATE between '04.06.2014' and '13.06.2014';

      SQL_ID  dznya6x7st0t8, child number 0

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

      select /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */ J.INVOICEACCOUNT,

      J.INVOICEID, J.INVOICEDATE, (T.LINEAMOUNT+T.LINEAMOUNTTAX) price  from

      custinvoicejour j join custinvoicetrans t on

      substr(nls_lower(j.dataareaid),1,7) =

      substr(nls_lower(t.dataareaid),1,7) and

      substr(nls_lower(J.INVOICEID),1,25) =

      substr(nls_lower(t.INVOICEID),1,25) where

      substr(nls_lower(T.DATAAREAID),1,7) = '201' and T.AVBROCHURELINENUM =

      29457  and substr(nls_lower(j.dataareaid),1,7) = '201' and

      J.INVOICEACCOUNT in  (select CE.ACCOUNTNUM from

      drmpos.avtr_seg_cust_campend ce where CE.CAMPAIGN = '201406' and

      CE.SEGMENT_LEVEL in ('D', 'E')) and J.AVAWARDSALES > 190  and

      substr(nls_lower(J.AVBILLINGCAMPAIGN),1,13) = '201406' and

      J.INVOICEDATE between '04.06.2014' and '13.06.2014'

       

      Plan hash value: 2002317666

       

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

      | Id  | Operation                     | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

      |   0 | SELECT STATEMENT              |                                |      1 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |

      |*  1 |  HASH JOIN                    |                                |      1 |   3956 |      0 |00:00:00.01 |       0 |      0 |  2254K|  1061K| 2190K (0)|

      |*  2 |   HASH JOIN                   |                                |      1 |     87 |  16676 |00:00:01.64 |     227K|   3552 |  3109K|  1106K| 4111K (0)|

      |*  3 |    TABLE ACCESS BY INDEX ROWID| CUSTINVOICEJOUR                |      1 |   1155 |  31889 |00:00:01.16 |     223K|     15 |       |       |          |

      |*  4 |     INDEX RANGE SCAN          | I_062INVOICEDATEORDERTYPEIDX   |      1 |   4943 |    134K|00:00:00.83 |   45440 |      0 |       |       |          |

      |   5 |    PARTITION LIST SINGLE      |                                |      1 |  82360 |    173K|00:00:00.08 |    3809 |   3537 |       |       |          |

      |*  6 |     TABLE ACCESS FULL         | AVTR_SEG_CUST_CAMPEND          |      1 |  82360 |    173K|00:00:00.06 |    3809 |   3537 |       |       |          |

      |   7 |   TABLE ACCESS BY INDEX ROWID | CUSTINVOICETRANS               |      1 |   4560 |      0 |00:00:00.01 |       0 |      0 |       |       |          |

      |*  8 |    INDEX RANGE SCAN           | I_064INVLINENUMCAMPAIGNOFPRICE |      1 |   4560 |      0 |00:00:00.01 |       0 |      0 |       |       |          |

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

       

      Predicate Information (identified by operation id):

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

       

         1 - access("J"."SYS_NC00299$"="T"."SYS_NC00165$" AND SUBSTR(NLS_LOWER("J"."INVOICEID"),1,25)=SUBSTR(NLS_LOWER("T"."INVOICEID"),1,25))

         2 - access("J"."INVOICEACCOUNT"=SYS_OP_C2C("CE"."ACCOUNTNUM"))

         3 - filter("J"."AVAWARDSALES">190)

         4 - access("J"."SYS_NC00299$"=U'201' AND "J"."INVOICEDATE">=TO_DATE(' 2014-06-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

                    "J"."SYS_NC00307$"=U'201406' AND "J"."INVOICEDATE"<=TO_DATE(' 2014-06-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

             filter(("J"."INVOICEDATE">=TO_DATE(' 2014-06-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "J"."SYS_NC00307$"=U'201406' AND

                    "J"."INVOICEDATE"<=TO_DATE(' 2014-06-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

         6 - filter(("CE"."SEGMENT_LEVEL"='D' OR "CE"."SEGMENT_LEVEL"='E'))

         8 - access("T"."SYS_NC00165$"=U'201' AND "T"."AVBROCHURELINENUM"=29457)

             filter("T"."AVBROCHURELINENUM"=29457)

       

       

      EXPLAIN PLAN FOR

      select /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */ J.INVOICEACCOUNT, J.INVOICEID, J.INVOICEDATE, (T.LINEAMOUNT+T.LINEAMOUNTTAX) price

      from custinvoicejour j join custinvoicetrans t on

      substr(nls_lower(j.dataareaid),1,7) = substr(nls_lower(t.dataareaid),1,7) and

      substr(nls_lower(J.INVOICEID),1,25) = substr(nls_lower(t.INVOICEID),1,25)

      where

      substr(nls_lower(T.DATAAREAID),1,7) = '201' and T.AVBROCHURELINENUM = 29457

      and substr(nls_lower(j.dataareaid),1,7) = '201' and

      J.INVOICEACCOUNT in

      (select CE.ACCOUNTNUM from drmpos.avtr_seg_cust_campend ce where CE.CAMPAIGN = '201406' and CE.SEGMENT_LEVEL in ('D', 'E'))

      and J.AVAWARDSALES > 190

      and substr(nls_lower(J.AVBILLINGCAMPAIGN),1,13) = '201406'

      and J.INVOICEDATE between '04.06.2014' and '13.06.2014';

      SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

      SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('7h1nbzqjgwsp7', 2));

       

      SQL_ID  7h1nbzqjgwsp7, child number 2

                                        

      EXPLAIN PLAN FOR select /*+ GATHER_PLAN_STATISTICS NO_PARALLEL */

      J.INVOICEACCOUNT, J.INVOICEID, J.INVOICEDATE,

      (T.LINEAMOUNT+T.LINEAMOUNTTAX) price  from custinvoicejour j join

      custinvoicetrans t on substr(nls_lower(j.dataareaid),1,7) =

      substr(nls_lower(t.dataareaid),1,7) and

      substr(nls_lower(J.INVOICEID),1,25) =

      substr(nls_lower(t.INVOICEID),1,25) where

      substr(nls_lower(T.DATAAREAID),1,7) = '201' and T.AVBROCHURELINENUM =

      29457  and substr(nls_lower(j.dataareaid),1,7) = '201' and

      J.INVOICEACCOUNT in  (select CE.ACCOUNTNUM from

      drmpos.avtr_seg_cust_campend ce where CE.CAMPAIGN = '201406' and

      CE.SEGMENT_LEVEL in ('D', 'E')) and J.AVAWARDSALES > 190  and

      substr(nls_lower(J.AVBILLINGCAMPAIGN),1,13) = '201406' and

      J.INVOICEDATE between '04.06.2014' and '13.06.2014'

       

      NOTE: cannot fetch plan for SQL_ID: 7h1nbzqjgwsp7, CHILD_NUMBER: 2

            Please verify value of SQL_ID and CHILD_NUMBER;

            It could also be that the plan is no longer in cursor cache (check v$sql_plan)

        • 1. Re: Should I wait until the finish of the execution time of the query for execution plan?
          Martin Preiss

          I am not sure if I understand your questions correctly but:

          • explain plan orders the optimizer to create an execution plan but not to execute the query: in some cases this plan is not the same plan the CBO will create for the actual execution (since the tool has some limitation - it ignores the types of bind variables for example).
          • a plan with rowsource statistics (created by the use of the gather_plan_statistics hint) adds the actual number of rows (A-rows) to the estimated values used by the optimizer (E-rows). And of course it is necessary to finish the query execution before the actual vaues can be added.
          • for an optimized query that is still in the library cache you can always get the execution plan by using the table function dbms_xplan.display_cursor - but this plan will not contain the rowsource statistics before the query has finished.
          • to see the current operations (for example the increasing number of loop iterations) during the executions you would have to use the sql monitor (if you have the fitting licence).

          I hope that is somehow an answer to your questions.

          1 person found this helpful
          • 2. Re: Should I wait until the finish of the execution time of the query for execution plan?
            SomeoneElse

            > and J.INVOICEDATE between '04.06.2014' and '13.06.2014';

             

            This doesn't answer your question at all, but this line of code is a red flag.

             

            And you seem to be doing a function of some sort on nearly every column, even your join condition.

             

            That usually prevents the use of any indexes (unless you have FBIs).

            1 person found this helpful
            • 3. Re: Should I wait until the finish of the execution time of the query for execution plan?
              Mohamed Houri

              You don't need to wait until the query has finished to get its execution plan. If you have the corresponding sql_id you can get its execution plan from the memory using the dbms_xplan.display_cursor.

               

              It will be the real plan followed by your query but information like Starts and A-Rows might not be correct.

               

              For example if you want to judge the Estimations done by the CBO you would compare Starts*E-Rows to A-Rows. And as far as the query has not completely finished this comparison might not be reliable.

               

              But yes, you don't have to wait until the query has completely finished to get the execution plan it has used

               

              Best regards

              Mohamed Houri

              1 person found this helpful
              • 4. Re: Should I wait until the finish of the execution time of the query for execution plan?
                Randolf Geist

                Martin Preiss wrote:

                 

                • for an optimized query that is still in the library cache you can always get the execution plan by using the table function dbms_xplan.display_cursor - but this plan will not contain the rowsource statistics before the query has finished

                Martin,

                 

                one thing that is not so commonly known about the Rowsource Statistics is that *no matter how* the statement execution completed, the data will be available. So a long running query can be cancelled and the Rowsource Statistics will still be populated (but only after the statement execution stopped in some way). Usually the information then available is already good enough to tell why the execution takes so long.

                 

                So for long running queries it is not necessarily required to run them to successful completion to get Rowsource Statistics, at least for the part of the execution plan that was already executed.

                 

                Randolf

                1 person found this helpful
                • 5. Re: Should I wait until the finish of the execution time of the query for execution plan?

                  SomeoneElse wrote:

                   

                  > and J.INVOICEDATE between '04.06.2014' and '13.06.2014';

                   

                  This doesn't answer your question at all, but this line of code is a red flag.

                   

                   

                   

                  Oh yes, I didn't notice. INVOICEDATE column is date. So I have to use to_date function right?

                   

                   

                   

                  And you seem to be doing a function of some sort on nearly every column, even your join condition.

                   

                  That usually prevents the use of any indexes (unless you have FBIs).

                   

                  I use FBIs and for this reason I use functions even join condition for using FBIs. So, did I do the right thing?

                   

                  Regards

                  • 6. Re: Should I wait until the finish of the execution time of the query for execution plan?

                    Mohamed Houri wrote:

                     

                    You don't need to wait until the query has finished to get its execution plan. If you have the corresponding sql_id you can get its execution plan from the memory using the dbms_xplan.display_cursor.

                     

                    It will be the real plan followed by your query but information like Starts and A-Rows might not be correct.

                     

                    For example if you want to judge the Estimations done by the CBO you would compare Starts*E-Rows to A-Rows. And as far as the query has not completely finished this comparison might not be reliable.

                     

                    But yes, you don't have to wait until the query has completely finished to get the execution plan it has used

                     

                    Best regards

                    Mohamed Houri

                     

                    However, as you can see I use /*+ GATHER_PLAN_STATISTICS */ hint in order to see  E-Rows, A-Rows, A-Time etc.

                    So, should I wait for this? What can I do?

                     

                     

                    Regards

                    • 7. Re: Should I wait until the finish of the execution time of the query for execution plan?
                      Martin Preiss

                      Randolf,

                       

                      thank you for the clarification. When I wrote my answer I began to think about doing a check - but then I trusted my memory; in other words: I was too lazy...

                       

                      Martin

                      • 8. Re: Should I wait until the finish of the execution time of the query for execution plan?

                        Randolf Geist wrote:

                         

                        Martin Preiss wrote:

                         

                        • for an optimized query that is still in the library cache you can always get the execution plan by using the table function dbms_xplan.display_cursor - but this plan will not contain the rowsource statistics before the query has finished

                        Martin,

                         

                        one thing that is not so commonly known about the Rowsource Statistics is that *no matter how* the statement execution completed, the data will be available. So a long running query can be cancelled and the Rowsource Statistics will still be populated (but only after the statement execution stopped in some way). Usually the information then available is already good enough to tell why the execution takes so long.

                         

                        So for long running queries it is not necessarily required to run them to successful completion to get Rowsource Statistics, at least for the part of the execution plan that was already executed.

                         

                        Randolf

                         

                         

                        Hello Randolf,

                         

                        It is great to talk to you. You opinions is very important for me. So, what should I do in order to see accurate results for E-Rows, A-Rows, A-Time etc. ?

                         

                        I use /*+ GATHER_PLAN_STATISTICS */ hint in order to see them. But the query takes too long (about 3-4 hours) please tell me what should I do should I wait the execution time of the query? Because as far as I know otherwise the values might be inaccurate.

                         

                         

                        Thanks for your valuable and remarkable thoughts.

                        Best Regards

                        Charlie

                        • 9. Re: Should I wait until the finish of the execution time of the query for execution plan?

                          Martin Preiss wrote:

                           

                          Randolf,

                           

                          thank you for the clarification. When I wrote my answer I began to think about doing a check - but then I trusted my memory; in other words: I was too lazy...

                           

                          Martin

                           

                           

                          Also, what do you mean when you say "Rowsource Statistics"? Can you please explain?

                           

                          Regards

                          Charlie

                          • 10. Re: Should I wait until the finish of the execution time of the query for execution plan?
                            Mohamed Houri

                              However, as you can see I use /*+ GATHER_PLAN_STATISTICS */ hint in order to see  E-Rows, A-Rows, A-Time etc.

                            So, should I wait for this? What can I do?

                             

                            No you don't have to wait .

                             

                            See what Randolf has also wrote

                             

                            Several months ago I have cancelled a query on a big partitioned table (XXX_PER_YYY with 1493 partitions)

                             

                            Select

                                a

                               ,b

                               ,c

                               ,per_ind

                            from XXX_PER_YYY

                            where per_ind = 0;

                             

                             

                            Normally this query should return 6,118,184 rows as shown via the following select:

                             

                            select per_ind, count(1)

                            from XXX_PER_YYY

                            group by per_ind;

                             

                            per_ind cout(1)

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

                            0       6,118,184

                             

                            And this is the execution plan I've got after I have cancelled the query

                             

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

                            | Id | Operation                        | Name        | Starts | E-Rows | A-Rows |

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

                            | 0  | SELECT STATEMENT                 |             | 1      |        |550K    |

                            | 1  | PARTITION RANGE ALL              |             | 1      | 69     |550K    |

                            | 2  | TABLE ACCESS BY LOCAL INDEX ROWID| XXX_PER_YYY | 111    | 69     |550K    |

                            |* 3 |  INDEX RANGE SCAN                | XXX_PER_IND | 111    | 69     |550K    |

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

                            Predicate Information (identified by operation id):

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

                                3 - access("PER_IND"=0)


                            This is the real execution plan followed by the query. But instead of 6M of rows I got 550K due to the fact that the query has been cancelled


                            @Randolf,


                            Can you please let me know, how, in this particular case, we can not make wrong conclusion about the CBO doing wrong estimations as far as the row source information are due to the cancelation of the query?


                            Best regards

                            Mohamed Houri

                            1 person found this helpful
                            • 11. Re: Should I wait until the finish of the execution time of the query for execution plan?
                              Randolf Geist

                              NightWing wrote:

                               

                              I use /*+ GATHER_PLAN_STATISTICS */ hint in order to see them. But the query takes too long (about 3-4 hours) please tell me what should I do should I wait the execution time of the query? Because as far as I know otherwise the values might be inaccurate.

                               

                               

                              Charlie,

                               

                              as I pointed out to Martin you don't need to wait for the query to complete. You can cancel it and the use DBMS_XPLAN.DISPLAY_CURSOR with the *STATS (ALLSTATS / MEMSTATS / IOSTATS) formatting options to get the plan displayed along with the so called "Rowsource Statistics" which are those "A-Rows", "A-Time", "Starts" extra columns populated.

                               

                              The statistics aren't necessarily inaccurate - but using "GATHER_PLAN_STATISTICS" for example the "A-Time" column can be inaccurate, but this has nothing to do with cancelling the query or not. So you probably should describe in more detail what you mean by "otherwise inaccurate".

                               

                              Of course, if the query didn't run to completion, you'll only see the "Rowsource Statistics" for those plan lines that were already active, but as I said, for a long running query the information is usually good enough to understand why the query takes longer than expected.

                               

                              Randolf

                              1 person found this helpful
                              • 12. Re: Should I wait until the finish of the execution time of the query for execution plan?
                                Martin Preiss

                                The additional statistics you get with the gather_plan_statistics hint (or by setting the statistics_level parameter) are called "rowsource statisctics".

                                1 person found this helpful
                                • 13. Re: Should I wait until the finish of the execution time of the query for execution plan?

                                  Martin Preiss wrote:

                                   

                                  The additional statistics you get with the gather_plan_statistics hint (or by setting the statistics_level parameter) are called "rowsource statisctics".

                                   

                                  Thanks a lot Sir.

                                   

                                  Regards

                                  Charlie

                                  • 14. Re: Should I wait until the finish of the execution time of the query for execution plan?

                                    Randolf Geist wrote:

                                     

                                    NightWing wrote:

                                     

                                    I use /*+ GATHER_PLAN_STATISTICS */ hint in order to see them. But the query takes too long (about 3-4 hours) please tell me what should I do should I wait the execution time of the query? Because as far as I know otherwise the values might be inaccurate.

                                     

                                     

                                    Charlie,

                                     

                                    as I pointed out to Martin you don't need to wait for the query to complete. You can cancel it and the use DBMS_XPLAN.DISPLAY_CURSOR with the *STATS (ALLSTATS / MEMSTATS / IOSTATS) formatting options to get the plan displayed along with the so called "Rowsource Statistics" which are those "A-Rows", "A-Time", "Starts" extra columns populated.

                                     

                                    The statistics aren't necessarily inaccurate - but using "GATHER_PLAN_STATISTICS" for example the "A-Time" column can be inaccurate, but this has nothing to do with cancelling the query or not. So you probably should describe in more detail what you mean by "otherwise inaccurate".

                                     

                                    Of course, if the query didn't run to completion, you'll only see the "Rowsource Statistics" for those plan lines that were already active, but as I said, for a long running query the information is usually good enough to understand why the query takes longer than expected.

                                     

                                    Randolf

                                     

                                     

                                    I need to know Actual Rows (A-Rows). However, when I cancel it, the A-Rows will be inaccurate, So what do you recommend?

                                     

                                    And also Cost, does cancellation also effect the value of Cost column in the execution plan?

                                     

                                     

                                    Regards

                                    Charlie

                                    1 2 Previous Next