This discussion is archived
7 Replies Latest reply: Nov 20, 2012 3:23 AM by Dom Brooks RSS

SQL Profile

928943 Newbie
Currently Being Moderated
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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    PavanKumar Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    "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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points