7 Replies Latest reply: Nov 20, 2012 5:23 AM by Dom Brooks RSS

    SQL Profile

    928943
      As i am reading the Oracle documents about SQL profile , i got stuck due to unawareness of some terminology which i am highlighting here in Bold.


      The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.


      Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.



      How can optimizer produce inaccurate estimates and what is an attribute of statment?
      What is auxiliary statistics?
      The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off how?
      what is sampling and partial execution techniques?

      Sumon
        • 1. Re: SQL Profile
          Aman....
          925940 wrote:
          As i am reading the Oracle documents about SQL profile , i got stuck due to unawareness of some terminology which i am highlighting here in Bold.


          The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.


          Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.



          How can optimizer produce inaccurate estimates and what is an attribute of statment?
          I believe the answer is in the statement that you have quoted yourself already,
          The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans.
          What is auxiliary statistics?
          Statistics that are not really of the segment but are of the computing environment , for example CPU, IO etc.

          The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off how?
          Didn't understand that what's the question actually?
          what is sampling and partial execution techniques?
          Sampling is to generate teh statistics but for a partial sample .

          HTH
          Aman....
          • 2. Re: SQL Profile
            Dom Brooks
            How can optimizer produce inaccurate estimates and what is an attribute of statment?
            The optimizer is reliant on object statistics being accurate - they may not be.
            Depends when/how they are gathered.

            Specific parameters are passed into sql statements as literals and/or bind variables.
            Bind variables may be peeked for initial hard parse and the estimates used for those peeked variables not suitable for parameters subsequently supplied to the parsed statement.
            The optimizer also applies standard selectivity percentages to certain predicates.

            And then there's data skew and histograms which may or may not exist and which complicate matters, and for which the actual endpoints captured for the histogram buckets might be critical to your statement.
            What is auxiliary statistics?
            So... taking this whole bundle of complications above, a sql profile looks at the actual (or maybe just a partial) execution of a statement, notes what the actual cardinalities were for table access and for joins and adjusts the estimates in the execution plan using these actual numbers via hints such as opt_estimate.
            • 3. Re: SQL Profile
              928943
              As Aman Said..

              Statistics that are not really of the segment but are of the computing environment , for example CPU, IO etc.

              and you said

              So... taking this whole bundle of complications above, a sql profile looks at the actual (or maybe just a partial) execution of a statement, notes what the actual cardinalities were for table access and for joins and adjusts the estimates in the execution plan using these actual numbers via hints such as opt_estimate.

              I reckon Aman is talking about Performance Statistics and Dom is talking about Optimizer Statistics i.e actual segment (table).Which one is correct for Auxiliary Statistics?I googled out but did not get a chance about getting some stuff about Auxiliary Statistics.

              @Aman i could'nt get "Sampling is to generate teh statistics but for a partial sample".

              Samun
              • 4. Re: SQL Profile
                Dom Brooks
                Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement.
                I suggest you create a profile and look at what information that profile contains.
                As mentioned, the sql profile should contain hints like OPT_ESTIMATE, COLUMN_STATS, TABLE_STATS containing these "auxiliary statistics".
                • 5. Re: SQL Profile
                  Pavan Kumar
                  Hi,

                  refer to below links
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:61313086268493
                  http://antognini.ch/papers/SQLProfiles_20060622.pdf (good one)

                  HTH

                  - Pavan Kumar N
                  • 6. Re: SQL Profile
                    928943
                    Thanks for it Dom, i am a bit clear now as before.But i am still unwell about the following statement.

                    "The output from the ATO is not, however, an execution plan. Instead, it produces a group of actions, along with their expected impact, and the rationale behind each."

                    I have seen many times the recommended execution plan at DB console , i do not know is it from ATO (SQL Tuning Advisor) or not but i believe ATO.

                    Sumon

                    Edited by: 925940 on 19/11/2012 19:10
                    • 7. Re: SQL Profile
                      Dom Brooks
                      "The output from the ATO is not, however, an execution plan. Instead, it produces a group of actions, along with their expected impact, and the rationale behind each."
                      I have seen many times the recommended execution plan at DB console , i do not know is it from ATO (SQL Tuning Advisor) or not but i believe ATO.
                      The SQL tuning advisor does not recommend a specific execution plan.
                      It's actions are more like:
                      1. What happens if we plug in the actual cardinalities from the SQL execution?
                      2. What happens if we use parallel?
                      3. What happens if we change this index?
                      etc.

                      It uses these options to evaluate alternative execution plans and uses those actual or estimated execution plans and statistics to say: "this action should produce this benefit".

                      So, the SQL Tuning Advisor reports includes the execution plan that the recommended action results in.
                      But these plans aren't the recommendation. Just the justification.

                      So, nine times out of ten, the advisor will recommend a SQL profile.
                      It will include the execution plan that the SQL profile resulted in as justification of its findings and recommendation.
                      But it's is the SQL Profile that is the output of the advisor, not the specific plan.

                      Do you see the distinction?