Home » RDBMS Server » Performance Tuning » Execution Plan Changing with same SQL Profile (Oracle 10.2.0.3.0 on AIX)
Execution Plan Changing with same SQL Profile [message #578822] Tue, 05 March 2013 09:04 Go to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hi

One of our clients is using Rule Based Optimizer on Oracle 10.2.0.3.0

2-3 weeks backs, during performance issue in one of the sql queries, one of our team members executed tuning advisor for it, created SQL profile and the subsequent execution of the SQL did not took much time (less I/O)
Now it took hardly a minute to execute

When this happened I checked that the SQL profile forced that particular query to use CBO (say plan_hash_value is PHV1 here)

Yesterday the same query again took 15-20 minutes for execution
I checked that even for this execution the query used the same SQL profile but "this time" with different plan_hash_value - say PHV2

Today again the query executed in less than a minute and used the plan_hash_value as PHV1

select distinct plan_hash_value,timestamp from dba_hist_sql_plan  where sql_id='mysqlid' order by 1,2;

PLAN_HASH_VALUE TIMESTAMP
--------------- --------------------
      890360113 20-feb-2013 16:38:39
     3736413466 04-mar-2013 08:12:52
     1237282258 03-jan-2013 17:15:02


I confirmed from awrsqrpt as well that different plans were used for different plan_hash_values and everythime same SQL profile was used
SQL> select name,CATEGORY,SIGNATURE,CREATED,LAST_MODIFIED,TYPE,STATUS,FORCE_MATCHING from dba_sql_profiles;

NAME                           CATEGORY                        SIGNATURE CREATED              LAST_MODIFIED        TYPE      STATUS   FOR
------------------------------ ------------------------------ ---------- -------------------- -------------------- --------- -------- ---
SYS_SQLPROF_015ffffcc3e1c5b000   DEFAULT                        1.5512E+19 20-feb-2013 16:30:48 20-feb-2013 16:30:48 MANUAL    ENABLED  NO


I am unable to undestand how execution plan and thus plan_hash_value is changing for the same SQL Profile

I read that SQL Profile (unlike stored outline) keeps up with increasing data volume and may not keep up with changing data distribution

Bearing this in mind I checked that values for 4 bind variables out of 81 are different for execution betwenn today and yesterdays' run(queried v$sql_bind_capture based on last_captured)

My questions are
1) does the different plan_hash_values with different execution plans for query using same SQL profile mean the query was hard parsed multiple times and still used the same SQL profile?
2) If that is the case why I never saw child_number = 1 in any of the views for the same sql_id. I tried it reapeatedly over last 2 weeks and always found child_number=0 in v$sql (also loaded_versions=1)
3) Does the different values of bind variable are causing this flip-flop of the plans? How can I conclude this?

Finally how to resolve the issue?
I have 2 plans with 2 different plan_hash_values. I know which would be better
How can I force the sql to use better plan in the two in this case where I am using Rule Based Optimizer and have SQL profile created
If this is not possible then how can I create stored outline from the existing plan (not waiting for subsequent execution to take place)

Thanks and Regards
sysdba007
Re: Execution Plan Changing with same SQL Profile [message #578878 is a reply to message #578822] Wed, 06 March 2013 01:21 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
1. sql profile contains statistics. It cannot fix the execution plan forever. If your optimizer statistics or parameter setting changed, the existing sql profile will be still applied but the execution plan can be different than before,
2. there is no official way in 10g for fixing the execution plan produced with sql profile. In 11g one can use sql plan baselines for this purpose,
3. you can try to fix your execution plan by the following way:
- find out the outlines for fixing by dbms_xplan.display or dbms_xplan.display_cursor with parameter format=>'ADVANCED',
- you can fix then your execution plan with these outlines as "hidden" hints

Re: Execution Plan Changing with same SQL Profile [message #579830 is a reply to message #578822] Fri, 15 March 2013 16:37 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

sysdba007 wrote on Tue, 05 March 2013 19:04

1) does the different plan_hash_values with different execution plans for query using same SQL profile mean the query was hard parsed multiple times and still used the same SQL profile?

Naturally
sysdba007 wrote on Tue, 05 March 2013 19:04

2) If that is the case why I never saw child_number = 1 in any of the views for the same sql_id. I tried it reapeatedly over last 2 weeks and always found child_number=0 in v$sql (also loaded_versions=1)
It doesn't matter.
sysdba007 wrote on Tue, 05 March 2013 19:04

3) Does the different values of bind variable are causing this flip-flop of the plans? How can I conclude this?

Yep, it can be caused by "bind variable peeking" mechanism of cbo. You can see it in 10053 trace.
sysdba007 wrote on Tue, 05 March 2013 19:04

Finally how to resolve the issue?
I have 2 plans with 2 different plan_hash_values. I know which would be better
How can I force the sql to use better plan in the two in this case where I am using Rule Based Optimizer and have SQL profile created

First of all show us your sql profile hints(outlines). You can use use for it sql_profile_hints.sql by Kerry Osborne
Also you can create new sql profile with outlines from good plan. SQL tuning advisor's profiles are very different from outline_data of execution plan.
So you can just import them from v$sql_plan.other_xml where id=1 for your good plan or from xbms_xplan.display_cursor(:sql_id,:child_no,'OUTLINE')
sysdba007 wrote on Tue, 05 March 2013 19:04

If this is not possible then how can I create stored outline from the existing plan (not waiting for subsequent execution to take place)

Don't use outlines. SQL profile is enough.
Re: Execution Plan Changing with same SQL Profile [message #579832 is a reply to message #578878] Fri, 15 March 2013 16:56 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

LNossov wrote on Wed, 06 March 2013 11:21
1. sql profile contains statistics. It cannot fix the execution plan forever. If your optimizer statistics or parameter setting changed, the existing sql profile will be still applied but the execution plan can be different than before,
it depends. Just usually advisor creates profiles only with statistics corrections
LNossov wrote on Wed, 06 March 2013 11:21

2. there is no official way in 10g for fixing the execution plan produced with sql profile. In 11g one can use sql plan baselines for this purpose,
You can import profile with outlines from execution plans
Re: Execution Plan Changing with same SQL Profile [message #579868 is a reply to message #579832] Sat, 16 March 2013 04:02 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
1. statistics corrections are still statistics,
2.
- the import of profiles isn't documented, so it isn't the official way,
- you can take the outlines for the profile not only from the explain plan but also from dba_sqltune_plans directly. So there are several methods for fixing execution plans produced with sql profiles in 10g. But all these methods base on the outlines stored in the database as "hidden hints".
Re: Execution Plan Changing with same SQL Profile [message #579926 is a reply to message #579868] Sun, 17 March 2013 16:06 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

LNossov wrote on Sat, 16 March 2013 13:02
1. statistics corrections are still statistics,

I said that advisor's profiles can contain not statistics only. I think you just never seen when advisor returns choice of more than 1 plan(Alternative plans section).
Also read about another cases: http://kerryosborne.oracle-guy.com/2010/02/sql-tuning-advisor-profiles/
LNossov wrote

2. there is no official way in 10g for fixing the execution plan produced with sql profile.
- the import of profiles isn't documented, so it isn't the official way,

Again you are wrong... Read Note 1487302.1 and post by Kerry Osborne

LNossov wrote

- you can take the outlines for the profile not only from the explain plan but also from dba_sqltune_plans directly. So there are several methods for fixing execution plans produced with sql profiles in 10g. But all these methods base on the outlines stored in the database as "hidden hints".

First of all, dba_sqltune_plans contains only results of SQL tuning advisor's task, but profiles can be created not only with it(in addition to importing as i showed above, exists loading/migrating through staging table )
Secondly, outlines of execution plans and sql tuning advisor results are completely different.
Re: Execution Plan Changing with same SQL Profile [message #579927 is a reply to message #579926] Sun, 17 March 2013 18:43 Go to previous message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
Quote:
I think you just never seen when advisor returns choice of more than 1 plan(Alternative plans section).


I haven't seen "Alternative plans section" in 10g, just in 11.2 Don't we discuss 10.2?

Quote:
Again you are wrong... Read Note 1487302.1 and post by Kerry Osborne


I didn't know about coe_xfr_sql_profile.sql in SQLT. Thanks for referencing.

Quote:
Secondly, outlines of execution plans and sql tuning advisor results are completely different.


Surely they are different. I mean the following: the plan from the section "Using SQL profile" is stored in the view dba_sqltune_plans with attribute='Using SQL profile'. One can fix it by storing its outlines in the sql profile. If there is no tuning task anymore, you can fix the execution plan but without warranty, that you fixed the plan suggested originally for your sql profile.

[Updated on: Sun, 17 March 2013 18:43]

Report message to a moderator

Previous Topic: execute to parse % - 50%
Next Topic: dbms_xplan for format model
Goto Forum:
  


Current Time: Fri Aug 01 22:21:49 CDT 2014

Total time taken to generate the page: 0.06419 seconds