Re: SQL Profile
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: karlarao@xxxxxxxxx
- Date: Thu, 23 Jul 2009 10:39:36 -0500
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 KarlWe have this SQL with a good SQL profile.. then suddenly somethingchanged on the optimizer and the good profile was not used anymore onits next execution.Mhmm... I'm puzzled. Do you mean that the execution plan has changed orthat the SQL profile is no longer used? These are two completely different things. In fact, by design, the SQL profile doesn't force aspecific execution plan. And, therefore, it is normal that the executionplan 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 SQLprofile regularly? I guess it's not the case. Therefore, there is onlyan old or new execution plan. If the profile is still there and, therefore, only the execution plan has changed, to "see" the oldexecution plan you have to bring the old execution environment back. For example, if the object statistics have changed, you have to restore themfrom 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_SQLPROFand apply it again using DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF. Is therea 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.
- Re: SQL Profile
- From: Karl Arao
- Re: SQL Profile