Re: SQL Profile
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>
- Date: Thu, 23 Jul 2009 19:37:23 +0800
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> @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@xxxxxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
