Re: SQL Profile

Karl,
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(@"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> @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.

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




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

Other related posts: