10 Replies Latest reply: Jul 23, 2010 2:53 PM by 787183 RSS

    same explain plan but different response time between two days

    Mohamed Houri
      Two days ago, I run a query which was performing very badly (33 records returned in more than 18 minutes). I then have generated its corresponding explain plan and started looking how to improve it. Finally, when I have changed a little bit the order by clause, the query performed very well.

      Today, I was going to implement this change of order by clause in Production, but, to my surprise the query without any change in it, it is performing very well(33 records in 1 seconde).

      I have again generated its corresponding new explain plan to point out any changes with regards to the old explain plan but unfortunately there is no changes : the same indexes, the same path, the same predicates, the same filters.

      The only differences are in the numbers of rows, bytes, costs and a new column named TempSpc has been added into the new explain plan.

      With the explanation given above(same explain plan), could you please let me know what could be the possibilities that made my query runs faster today while it was runing slowly two days ago

      Thanks a lot

      Mohamed Houri
        • 1. Re: same explain plan but different response time between two days
          Mohamed Houri
          And now at 10h30 it is again performing badly with exactly the same explain plan as the one corresponding to this morning very performant query!!!

          Mohamed Houri
          • 2. Re: same explain plan but different response time between two days
            Robert Geier
            I think you have answered your own question :- "The only differences are in the numbers of rows, bytes, costs and a new column named TempSpc has been added into the new explain plan."

            Do all tables and indexes have stats ?

            Did the two statements use the same bind variables, or different ? E.g

            select count(*) from employee where gender = &input1;

            The explain plan may be the same for MALE and FEMALE, but the row counts may be very different.

            Also check the OS, disk etc. Maybe it was at 100% CPU before, and now it is only at 20% CPU. If you have AWR or Statspack running you can check the waits at the time
            • 3. Re: same explain plan but different response time between two days
              Mohamed Houri
              Yes the two statements use the same bind variables.

              This morning I run the query and it tooks less than 1 seconde to complete. I have just go the time to post my thread and now the same query with the same bind variables are runing very slowly (about 5 minutes to complete).

              The explain plans of the two statements are now exactly the same.

              Regards

              Mohamed
              • 4. Re: same explain plan but different response time between two days
                Charles Hooper
                Mohamed Houri wrote:
                Yes the two statements use the same bind variables.

                This morning I run the query and it tooks less than 1 seconde to complete. I have just go the time to post my thread and now the same query with the same bind variables are runing very slowly (about 5 minutes to complete).

                The explain plans of the two statements are now exactly the same.

                Regards

                Mohamed
                Explain plans, especially when bind variables are used and histograms potentially exist, may be incorrect. Yes, plans generated by autotrace may also be incorrect even without bind variables.

                What Oracle release are you running, for example: 8.1.7.3, 9.2.0.8, 10.2.0.4, 11.1.0.7, 11.2.0.1. If you are running 10.1 or higher, use DBMS_XPLAN.DISPLAY_CURSOR to display the actual execution plan for the last SQL statement executed. Note that the same SQL statement may have multiple execution plans in the library cache, those plans may be retrieved with DBMS_XPLAN.DISPLAY_CURSOR.

                There are many causes for a SQL statement (even without bind variables) to execute quickly at times and very slowly at other times. Enable a 10046 trace at level 12 (to capture wait events and bind variables) if you want to see why the SQL statement executes slowly at times.

                Charles Hooper
                IT Manager/Oracle DBA
                K&M Machine-Fabricating, Inc.
                • 5. Re: same explain plan but different response time between two days
                  Mohamed Houri
                  Thanks Charles,

                  I am using Release 10.2.0.4.0

                  I am using explain plan for (query goes here.....) and select * from table(dbms_xplan.display) to get the explain plan.

                  Actually the query is runing fast. I will trace it with 10046 level 12 when it will performs badly


                  Regards

                  Mohamed
                  • 6. Re: same explain plan but different response time between two days
                    userxyz1234
                    Oracle's bind variable peeking may be at play, here.

                    Have a Google for it - there's good stuff on Jonathan Lewis' site about it.

                    An example: the first query to run one day uses search value "USA" for a query - later, "DCG" is used as the search value.

                    The first query is a lucky one: it leads to an index being used, as "USA" appears very infrequently in the table being queried, and a good plan is used. Hooray!

                    The second query is unlucky: "DCG" is appears very, very frequently in the table being searched. Sadly, the "USA" query's plan is used (because the query is, as far as Oracle is concerned, the same: only the value - a bind variable - has changed), and what should, really, be a Full Table Scan (because the "DCG" query will, in fact, visit just about every block in the table) is, instead, run using the index (like the "USA" query did). The result is horribly slow for "DCG" queries; lovely for "USA" queries.

                    The reverse would happen, of course, if the "DCG" query had been run first.

                    If this is the cause, then there are workarounds.
                    • 7. Re: same explain plan but different response time between two days
                      Charles Hooper
                      The EXPLAIN PLAN FOR ... syntax may also display inaccurate execution plans. Bind variable peeking does not take place with EXPLAIN PLANs, while it does take place during a normal query parse and execute in 9i and above. Additionally, you could find that all bind variables are treated as VARCHAR2 in an EXPLAIN PLAN. If I recall correctly, EXPLAIN PLAN FOR should (could?) trigger a hard parse of the SQL statement rather than reusing the previously parsed version of the SQL statement (another reason why it might not display the plan used in production).

                      After executing the SQL statement, both when it executes quickly and when it executes slowly, execute the following to display the actual execution plan used by the query:
                      SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                      It is possible to join V$SQL to the above in order to output all existing execution plans for a particular SQL statement, but I will leave that SQL statement for later.

                      Additionally, you should collect a 10046 trace for the query when it executes quickly and also when it executes slowly. This will allow you to see what has changed. Hopefully, the row source execution plan (the STAT lines) will also appear in the 10046 trace (execute a simple SQL statement after the other SQL statement, such as SELECT SYSDATE FROM DUAL; to increase the chances that the STAT lines will appear in the trace file).

                      Charles Hooper
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.
                      • 8. Re: same explain plan but different response time between two days
                        Jonathan Lewis
                        Mohamed Houri wrote:
                        Yes the two statements use the same bind variables.

                        This morning I run the query and it tooks less than 1 seconde to complete. I have just go the time to post my thread and now the same query with the same bind variables are runing very slowly (about 5 minutes to complete).

                        The explain plans of the two statements are now exactly the same.
                        Charles, and others, have pointed out the problems of using +"explain plan"+ when bind variables are involved and the effect of the optimizer peeking at bind values if it needs to recreate a plan - and this type of thing may be your problem.

                        However, I have [+*posted a few reasons*+|http://jonathanlewis.wordpress.com/2009/11/12/no-change/] on my blog about why exactly the same query with exactly the same inputs could run at different speeds at different times, and I've invited other people to come up with more reasons. The list is growing quite loing (although a couple of them are fairly special cases which are unlikely to apply to the generic "it randomly runs at different speeds").

                        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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                        • 9. Re: same explain plan but different response time between two days
                          705249
                          Hi all,

                          I prefer to retreive the execution plan with execution stats. Thus you will be able to compare the estimated rows with the number of rows really processed.

                          ex:
                          SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));

                          But you have to add the hint /*+ gather_plan_statistics */ into the sql statement or to set the TIMED_STATISTICS parameter to ALL.
                          • 10. Re: same explain plan but different response time between two days
                            787183
                            You mention workaround in your message. Where can I find this workaround?