Re: SQL Profile
Date: Thu, 23 Jul 2009 19:37:23 +0800
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/
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;
6 extractvalue(value(d), '/hint') as outline_hints 7 bulk collect
12 passing (
14 xmltype(other_xml) as xmlval
18 sql_id = '&&1'
19 and child_number = &&2
20 and other_xml is not null
22 ) d;
29 -- replace with dba_hist_sqltext 30 -- if required for AWR based 31 -- execution
33 -- sys.dba_hist_sqltext
35 sql_id = '&&1'
36 and child_number = &&2;
37 -- plan_hash_value = &&2;
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
- Karl Arao http://karlarao.wordpress.com
On Thu, Jul 23, 2009 at 2:58 PM, Christian
> 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
>> 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.