7 Replies Latest reply: Jul 5, 2011 8:03 AM by Jonathan Lewis RSS

    SQL Profile

    user13653962
      When i run schedule SQL tuning advisor in oracle dbconsole for a query it gives me following recommendations.
      SQL Profile     
          A potentially better execution plan was found for this statement.     
          Consider accepting the recommended SQL profile.     
      Shoiuld i implement/accept this recommendations/SQL profile ? if i accept it and any adverse effect occur then how can i revert it back.
        • 1. Re: SQL Profile
          Fran
          when you accept a better plan from a SQL the execution of this sql will be better and faster.
          • 2. Re: SQL Profile
            CKPT
            read this note from metalink

            *Automatic SQL Tuning - SQL Profiles [ID 271196.1]*
            • 3. Re: SQL Profile
              Aman....
              You don't need to guess actually. With the recommendation, if you are on the EM, there comes a nice comparison eyeglass to compare the new and old plan that oracle is suggesting. See if there is any significant difference and if you like it, switch over.

              Aman....
              • 4. Re: SQL Profile
                Pavan Kumar
                Hi,

                You can get the demo on the concept from Oracle Ace itself
                http://www.oracle-developer.net/display.php?id=317

                Further from Optimizer Team

                http://optimizermagic.blogspot.com/2009/01/sql-plan-management-part-3-of-4.html

                HTH

                - Pavan kumar N
                • 5. Re: SQL Profile
                  user61472
                  Implementing a SQL profile will not always necessarily give you better results for your SQL statement. You should properly test it in your performance testing environment first to make sure that there are no ill-effects.

                  If you have implemented the SQL profile and you want to revert it back then you can do so via OEM Grid:-
                  1) Go to the Performance page and click Top Activity
                  2) Under the Top SQL pane, click the SQL ID of the statement you implemented the profile for
                  3) Click the Plan Control tab. You should see the the SQL profile you implemented. You can now either disable the profile or delete it.

                  Hope this helps.
                  • 6. Re: SQL Profile
                    Pavan Kumar
                    Hi,

                    Additionally, some information the below query can provide the sql profile adjustments.

                    select
                    sp.sp_name, sa.attr#, sa.attr_val
                    from
                    sqlprof$ sp,
                    sqlprof$attr sa
                    where
                    sp.signature = sa.signature
                    and sp.category = sp.category
                    order by
                    sp.sp_name,
                    sa.attr#
                    ;

                    - Pavan kumar N
                    • 7. Re: SQL Profile
                      Jonathan Lewis
                      Pavan Kumar wrote:
                      Hi,

                      Additionally, some information the below query can provide the sql profile adjustments.
                      select
                      sp.sp_name, sa.attr#, sa.attr_val
                      from
                      sqlprof$      sp,
                      sqlprof$attr  sa
                      where
                      sp.signature = sa.signature
                      and     sp.category  = sp.category
                      order by
                      sp.sp_name,
                      sa.attr#
                      ;
                      That's a piece of code that will tell you what the profile looks like AFTER you've accepted it.
                      There's an example on my blog of a query that works (or did the last time I used it) if you want to see the profile before you accept it: http://jonathanlewis.wordpress.com/2007/02/11/profiles/

                      For the OP - Oracle gives you the pl/sql call to accept a profile - the package used includes a call to drop the profile.

                      Regards
                      Jonathan Lewis