Re: SQL Profile

First, I think you need to convince yourself as to whether the profile is being used or not. There is a column in v$sql (sql_profile) which will have the name of the profile if one is being used. Also xplan will show that a profile is being used at the bottom of the output.

If the profile is being used, but the plan is not what you expected, there are a couple of likely culprits.

First, if it's a profile generated by SQL Tuning Advisor it's quite possible that it will have one or more OPT_ESTIMATE hints - which apply scaling factors to various operations. These types of profiles can and do switch plans fairly easily, because they are not even attempting to lock anything. As the stats change, the plans can change.

The second common possibility is that Oracle decided to use an index hint that doesn't specify the index name, but rather the columns that it would like to use an index on. For example:

   INDEX_RS_ASC(_at_"SEL$1" "TEST_TABLE"@"SEL$1" ("TEST_TABLE"."COL2" "TEST_TABLE"."TEST_TABLE_ID")) This hint does not specify an index, but rather columns to use an index on. I'm not sure when this form became a preferred approach but it certainly seems to show up pretty often. So that leaves the optimizer with the flexibility to pick an index based on stats. In systems with lots of indexes, this makes it more likely that a plan will switch plans even though it's using an Outline or Profile. In fact, you may have a statement with multiple plans in the shared pool, pick one to create an outline or profile with, enable it, and have the new child use a plan that's different from the plan of the child you used to create it with. Very frustrating.

One last comment. I believe that all this is just trickery to try to work around a situation where you can't get to the code. Because of all the issues, I don't think they are the best long term solutions. But they can provide quick relief in a pinch.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Jul 23, 2009, at 6:37 AM, Karl Arao wrote:

> Kerry,
>
> Yes I've read that.. and currently doing some multiple read on these
> blog posts.
>
> http://kerryosborne.oracle-guy.com/2008/09/sql-tuning-advisor/
> http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/
> http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/
> http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/
> http://kerryosborne.oracle-guy.com/2009/03/bind-variable-peeking-drives-me-nuts/
> http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/
> http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints/
> http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints-take-2/
>
> http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
> http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/
>
> http://antognini.ch/papers/SQLProfiles_20060622.pdf
>
>
>
>
> Chris,
>
> Probably the execution plan has changed but still using the same
> profile.
>
> A possible solution is to get the outline hints as indicated in
> http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
>
>
> SQL> -- Create the SQL profile based on that cursor
> SQL> _at_create_profile_from_shared_pool c2trqja6wh561 0 TEST true
> SQL> declare
> 2 ar_profile_hints sys.sqlprof_attr;
> 3 cl_sql_text clob;
> 4 begin
> 5 select
> 6 extractvalue(value(d), '/hint') as outline_hints
> 7 bulk collect
> 8 into
> 9 ar_profile_hints
> 10 from
> 11 xmltable('/*/outline_data/hint'
> 12 passing (
> 13 select
> 14 xmltype(other_xml) as xmlval
> 15 from
> 16 v$sql_plan
> 17 where
> 18 sql_id = '&&1'
> 19 and child_number = &&2
> 20 and other_xml is not null
> 21 )
> 22 ) d;
> 23
> 24 select
> 25 sql_text
> 26 into
> 27 cl_sql_text
> 28 from
> 29 -- replace with dba_hist_sqltext
> 30 -- if required for AWR based
> 31 -- execution
> 32 v$sql
> 33 -- sys.dba_hist_sqltext
> 34 where
> 35 sql_id = '&&1'
> 36 and child_number = &&2;
> 37 -- plan_hash_value = &&2;
> 38
> 39 dbms_sqltune.import_sql_profile(
> 40 sql_text => cl_sql_text
> 41 , profile => ar_profile_hints
> 42 , category => '&&3'
> 43 , name => 'PROFILE_&&1'
> 44 -- use force_match => true
> 45 -- to use CURSOR_SHARING=SIMILAR
> 46 -- behaviour, i.e. match even with
> 47 -- differing literals
> 48 , force_match => &&4
> 49 );
> 50 end;
> 51 /
>
>
>
> - Karl Arao
> http://karlarao.wordpress.com
>
>
>
>
> On Thu, Jul 23, 2009 at 2:58 PM, Christian
> Antognini<Christian.Antognini_at_trivadis.com> wrote:
>> Hi Karl
>>
>>> We have this SQL with a good SQL profile.. then suddenly something
>>> changed on the optimizer and the good profile was not used anymore
>>> on
>>> its next execution.
>>
>> Mhmm... I'm puzzled. Do you mean that the execution plan has
>> changed or
>> that the SQL profile is no longer used? These are two completely
>> different things. In fact, by design, the SQL profile doesn't force a
>> specific execution plan. And, therefore, it is normal that the
>> execution
>> plan can change.
>>
>>> 1) Can I force the SQL to use the old SQL profile or SQL profile's
>>> plan? I don't see on the DBMS_SQLTUNE any option to use the old
>>> profile or plan.
>>
>> Again, there is no old or new SQL profile... Or you recreate the SQL
>> profile regularly? I guess it's not the case. Therefore, there is
>> only
>> an old or new execution plan. If the profile is still there and,
>> therefore, only the execution plan has changed, to "see" the old
>> execution plan you have to bring the old execution environment
>> back. For
>> example, if the object statistics have changed, you have to restore
>> them
>> from the history...
>>
>>> 2) If I have a backup of the profile from a table using
>>> DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF and
>>> DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
>>> and apply it again using DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF. Is
>>> there
>>> a way to use the good old plan of that profile?
>>
>> Again, the SQL profile doesn't force to use a specific execution
>> plan.
>> Hence, you need exactly the old execution environment to have the old
>> execution plan.
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l