8 Replies Latest reply: Jan 11, 2014 7:44 PM by ABOracle RSS

    How to provide tuning solution from explain plan only

    958657


      Dear all,

       

      If I do not have any kind of access to the database and only have explain plan with me,how I can provideperformance or query  tuning solutions from that??

       

       

      Regards

       

      Anirban

        • 1. Re: How to provide tuning solution from explain plan only
          Baris Yildirim

          In my opinion, the most important thing is that you have to learn the table design of your database and also data distribution.

          if you can learn so I think an explain plan is enough for tuning.

           

          Regards

          • 2. Re: How to provide tuning solution from explain plan only
            Hoek

            By learning how to interprete the execution plan, but one way or the other: without knowing your data (the number of rows in the tables, indexed columns, data distribution and so on), this will be a though one...

            You might like to read: http://www.antognini.ch/papers/InterpretingExecutionPlans_20091017.pdf

            • 3. Re: How to provide tuning solution from explain plan only
              David Berger

              Hello 958657

               

              You can do it, if select statements were executed with the Hint " /*+ gather_plan_statistics */ " and the execution plan you have was selected from the DBMS_XPLAIN.display_cursor (... format => 'ALLSTATS LAST').

               

              In this case you can see the estimated and actual rows and you can have a guess that there could be performance problem where the estimated and actual rows have a big difference.

              -> In that case you can state that there can be a problem with the statistics on those objects. (Improper statistic gathering )

               

              But I think this is the maximum what you can reach with it... Without knowing the DB-Structure etc. tuning is pointless.

               

              Regards,

              David

              • 4. Re: How to provide tuning solution from explain plan only
                rp0428
                If I do not have any kind of access to the database and only have explain plan with me,how I can provideperformance or query  tuning solutions from that??

                 

                For interview questions like this that you want help with you need to provide us with ALL of the information that the interviewers provided you with when they ask you the question.

                 

                My standard response to a question such as 'how would you tune this query' is

                I wouldn't change anything - I have no reason to think that it even needs to be tuned.

                Tuning is a solution to a problem.

                 

                Until you have identified, and verified, the problem there IS no tuning to be done.

                 

                After my statement above I would tell the interviewers that I need answers to several questions before proceeding:

                1. What are the indications that the query needs to be tuned?

                2. What changes to the database or the tables/views/etc involved have been made since the query performed well?

                3. What changes to the data have been made since the query performed well? Any large data loads, deletions, updates?

                4. Are the statistics for the objects involved current? Are they the same as when the query performed well?

                5. Are the indexes the same as they were when the query performed well? Are any indexes missing? Were any new ones added?

                6. Do I have an 'explain plan'? Or is it an 'execution plan'?

                 

                In short - if it ain't broke, don't fix it!

                • 5. Re: How to provide tuning solution from explain plan only
                  958657


                  Thanks rp0428 for replying. Here goes my answer, which you said I could ask the interviewer.

                   

                  1. What are the indications that the query needs to be tuned?

                  Ans:- Its taking long time to execute e.g. many hours in getting executed.

                  2. What changes to the database or the tables/views/etc involved have been made since the query performed well?
                  Ans:-Probably this details will be provided that this and these changes have been made

                   

                  3. What changes to the data have been made since the query performed well? Any large data loads, deletions, updates?

                  Ans:-That too will be provided.

                  4. Are the statistics for the objects involved current? Are they the same as when the query performed well?

                  Ans:-Statistics are current.

                  5. Are the indexes the same as they were when the query performed well? Are any indexes missing? Were any new ones added?

                  Ans :-That I can ask them & need to know from them

                  6. Do I have an 'explain plan'? Or is it an 'execution plan'?

                  Ans:-Its explain plan

                   

                  If all these details are provided,then can I tune the query from explain plan??

                  • 6. Re: How to provide tuning solution from explain plan only
                    Hoek

                    No it's not an 'explain plan', that's the command.

                    Issuing an explain plan command results in an execution plan for a query.

                    DBMS_XPLAN.DISPlAY_CURSOR supersedes explain plan.

                    I suggest you read up on that.

                    • 7. Re: How to provide tuning solution from explain plan only
                      rp0428

                      1. What are the indications that the query needs to be tuned?

                      Ans:- Its taking long time to execute e.g. many hours in getting executed.

                      Sorry - but IMHO that is not an indication that there is anything wrong.

                       

                      What if I stated the issue this way: I have a query that takes many hours to execute.

                       

                      Question: Does that query need to be tuned?

                       

                      What is your answer? Please post it along with your reasoning.

                       

                      My answer would be: I have absolutely no idea if that query needs to be tuned or not. How would you, or anyone else have any idea at all how long that query should take to execute?

                      1. What are the indications that the query needs to be tuned?

                      Identifying a performance issue usually involves a comparison of some sort: current execution time versus previous execution time FOR THE SAME conditions.

                       

                      So if a query is taking 'many hours' today but before today it usually executed in 2 minutes then, YES, there appears to be a performance issue FOR SOME REASON. That reason may not be the query. After all, if it ran for days, weeks, or months in 2 minutes but suddenly is taking 'many hours' why would the query be the problem?

                       

                      Before you can diagnose a problem when a query takes 'many hours' to execute you have to have some valid reason for why the query should NOT be taking that long. Until you have those reasons there is NOTHING to tune.

                      • 8. Re: How to provide tuning solution from explain plan only
                        ABOracle

                         

                        If I do not have any kind of access to the database and only have explain plan with me,how I can provide performance or query  tuning solutions from that??

                         

                         

                        This is contradictory as you said you don't have access but you have explain plan. You wont get any explain plan until you connect to the database and run "Explain plan for" statement for the query. How do you get the "explain plan"? If it is provided by someone to you, you might request to get the "Execution Plan" for the query.

                         

                        Keep in mind that   - these two are not same.

                        2. Dynamic:  Execution plan - Run time Plan

                        3. awr/ statspack execution plan --Run time from the past - this is again dynamic execution plan of query runs in the past

                         


                        Tuning recommendation is possible by comparing run time of the same query in the past and today's run time and based on further analysis.