SQL Plan Baselines
Mike, October 26, 2009 - 2:56 pm UTC
Thanks for the response.
You note that the 'incumbent' plan is run first, which may allow the 'challenger' to get a free ride from the caching. I had also wondered about how that came into play. So it sounds like the scoring is weighted to try to overcome that?
I tried doing two 'evolve' calls back-to-back for the same plan. As you predicted, the numbers came out almost identical - this must certainly be a caching effect:
(I no longer have both plans from the above example - so I used another case where there seemed to be an 'opportunity')
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =
PLAN_NAME = SYS_SQL_PLAN_22ea11acbead43a9
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = NO
Plan: SYS_SQL_PLAN_22ea11acbead43a9
-----------------------------------
Plan was verified: Time used 4.46 seconds.
Failed performance criterion: Compound improvement ratio < 1.01
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 36 36
Elapsed Time(ms): 3467 524 6.62
CPU Time(ms): 779 470 1.66
Buffer Gets: 10016 9927 1.01
Disk Reads: 9527 1103 8.64
Direct Writes: 0 0
Fetches: 1253 210 5.97
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.90
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =
PLAN_NAME = SYS_SQL_PLAN_22ea11acbead43a9
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = NO
Plan: SYS_SQL_PLAN_22ea11acbead43a9
-----------------------------------
Plan was verified: Time used 1.03 seconds.
Failed performance criterion: Compound improvement ratio < 1.01
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 36 36
Elapsed Time(ms): 383 399 .96
CPU Time(ms): 396 380 1.04
Buffer Gets: 10037 9927 1.01
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.07
So, with such a significant effect from caching, are the comparisons really meaningful?
Also, the 'challenger' plans came from auto-capture, not a SQL Tuning Set. So I am hoping the bind variables were captured at the time the plan was added as a 'non-accepted' plan to the baseline.
October 26, 2009 - 3:35 pm UTC
... So, with such a significant effect from caching, are the comparisons really meaningful? ....
yes, they are - and you just showed it?
Baseline evaluation
Mike, October 26, 2009 - 8:32 pm UTC
But the first comparison measures a plan with no favorable cache to a plan with favorable cache, and it claims to be a meaningful comparison.
The second comparison measures a plan with favorable cache to another plan with favorable cache. I think this is an equitable comparison but I think it is less interesting than comparing both plans with no favorable cache.
It strikes me as similar to saying 'The blue car gets 30 mpg in typical highway driving. The red car gets 80 mpg going down a long hill. Therefore the blue car is better.'
We can also see that both cars get 80 mpg going down a long hill. But we haven't seen a comparison of both cars in typical highway driving, and that is more useful than a downhill-only comparison.
I will try to see if I can switch around which is the 'incumbent' and which is the 'challenger'. But then the comparison will use different bind variables, which is again not the most meaningful/useful comparison.
October 27, 2009 - 11:11 am UTC
... But the first comparison measures a plan with no favorable cache to a plan with
favorable cache, and it claims to be a meaningful comparison....
and - it DID do a favorable comparision - did it not? Therein lies the confusion you had in your first asking - you said "oh wow, that second one is so much better" - you thought it was an incorrect comparision...
To which I said "all about the physical IO, you cannot really count that - the state of the cache is unknown for either query - always, run multiple times and see if you don't start to agree with their findings". Which means - the comparison was in fact meaningful - it ignored that which should be ignored
(side note: I always laugh when people flush the buffer cache or shared pool during a 'benchmark' to make it 'fair', what a wrong thing to do)
The report shows many numbers and then draws a conclusion "it is not worth upgrading to the new plan".
Was that conclusion correct? Yes
Were the numbers correct? Yes
Do all of the numbers contribute to the conclusion? No <<<<------
.... It strikes me as similar to saying 'The blue car gets 30 mpg in typical highway
driving. The red car gets 80 mpg going down a long hill. Therefore the blue car
is better.' ...
how so??? Please explain.
Baseline evaluation
Mike, October 28, 2009 - 6:19 am UTC
I am having difficulty with the position that 'testing with a favorable cache, so that I/O as a contributor is minimized in the comparison, is the right test to make'. I will think about that some more.
But this does significantly help my understanding:
Do all of the numbers contribute to the conclusion? No <<<<------
Can we get more information about what does contribute to the conclusion?
Do you consider those other numbers (other than the compound improvement ratio) to be useful or meaningful at all? (The compund improvement ratio must be significant, since that is what seems to be what drives the go/no-go decision.)
October 28, 2009 - 8:43 am UTC
... Can we get more information about what does contribute to the conclusion? ...
not documented, subject to change, has probably already been changed....
I look myself almost exclusively at
a) buffer gets (logical IO's, reduce them and physical IO's take care of themselves)
b) amount of cpu needed
It is a function of those two things mostly.
Which baseline is the 'baseline' baseline?
Michael Tefft, December 22, 2009 - 6:59 am UTC
We have a job that runs once a week to run DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE for all non-accepted baselines where LAST_VERIFIED is null or is over 60 days ago. (LAST_VERIFIED is set by DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE, so if we just determined on the last run that a baseline was not worth evolving, we want to wait 60 days before checking it again.)
Since we have now passed 60 days since our first run, we are starting to see our first re-evaluations. And I have an interesting scenario.
On 10/20 we evaluated the following plans for the same SQL handle (I will call them A and B for brevity). There was a manually-loaded plan in place (call it C), which was the 'Baseline Plan' against which the new plans were compared. (The text between the '===' banner and the first '---' banner is produced by our 'wrapper' procedure.)
===============================================================================
SQL handle is: SYS_SQL_2efe4faa065026d2
Plan name is: SQL_PLAN_2xzkgp83509qk2b261865
Creator is : MTRSPROD Origin is: AUTO-CAPTURE
Accepted is: NO Fixed is: NO
Last verified is:
Bind Sensitivity: N Bind Aware: N
*** Evolve Result: TESTED; INSUFFICIENT IMPROVEMENT ***
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_2efe4faa065026d2
PLAN_NAME = SQL_PLAN_2xzkgp83509qk2b261865
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_2xzkgp83509qk2b261865
-----------------------------------
Plan was verified: Time used 3.06 seconds.
Failed performance criterion: Compound improvement ratio < 1.01
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 2966 17 174.47
CPU Time(ms): 54 8 6.75
Buffer Gets: 536 536 1
Disk Reads: 312 0
Direct Writes: 0 0
Fetches: 312 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
--------------------------------------------------------------------------------
================================================================================
SQL handle is: SYS_SQL_2efe4faa065026d2
Plan name is: SYS_SQL_PLAN_065026d27e03e564
Creator is : MTRSPROD Origin is: AUTO-CAPTURE
Accepted is: NO Fixed is: NO
Last verified is:
Bind Sensitivity: N Bind Aware: N
*** Evolve Result: TESTED; PROMOTED ***
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_2efe4faa065026d2
PLAN_NAME = SYS_SQL_PLAN_065026d27e03e564
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_065026d27e03e564
-----------------------------------
Plan was verified: Time used 276.69 seconds.
Passed performance criterion: Compound improvement ratio >= 3.2
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 31 31
Elapsed Time(ms): 120693 250 482.77
CPU Time(ms): 1732 81 21.38
Buffer Gets: 93080 29176 3.19
Disk Reads: 12064 21 574.48
Direct Writes: 0 0
Fetches: 12064 21 574.48
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
--------------------------------------------------------------------------------
So, Plan A (using bind variables captured with A) is evaluated as showing virtually no improvement over the only accepted baseline plan C (using bind variables captured with A). Plan B (using bind variables captured with B) is evaluated with a threefold improvement over the only accepted baseline plan C (using bind variables captured with B), and now becomes an Accepted plan.
Now, on 12/21, the 'failed' plan (A) above is re-evaluated. The results are so different that I suspect it was compared against the newer plan B, which was evolved above, not the original manually-loaded plan C:
================================================================================
SQL handle is: SYS_SQL_2efe4faa065026d2
Plan name is: SQL_PLAN_2xzkgp83509qk2b261865
Creator is : MTRSPROD Origin is: AUTO-CAPTURE
Accepted is: NO Fixed is: NO
Last verified is: 20-OCT-09 03.20.36.000000 AM
Bind Sensitivity: N Bind Aware: N
*** Evolve Result: TESTED; PROMOTED ***
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_2efe4faa065026d2
PLAN_NAME = SQL_PLAN_2xzkgp83509qk2b261865
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_2xzkgp83509qk2b261865
-----------------------------------
Plan was verified: Time used 8.04 seconds.
Passed performance criterion: Compound improvement ratio >= 35.2
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 5230 2733 1.91
CPU Time(ms): 249 52 4.79
Buffer Gets: 19024 536 35.49
Disk Reads: 1588 319 4.98
Direct Writes: 0 0
Fetches: 1588 319 4.98
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
--------------------------------------------------------------------------------
The first reaction is that this must be a contradiction: if A < C and C < B then how can B < A be true? But, given that the evaluations are done with different bind variables, we actually have
A(BindA) < C(BindA)
C(BindB) < B(BindB)
B(BindA) < A(BindA)
(Of course, other factors can change in the course of 60 days, but I don't think that is the explanation for this case.)
Is there any way to determine 'which plan serves as the baseline for the performance comparison?'
Based on this, I think the 60-day rule is too simple; we may be better off re-evaluating after 60 days or after another plan for the same SQL becomes Accepted.
December 31, 2009 - 7:35 am UTC
... The results are so different that I suspect it was compared against the newer plan B, which was evolved above, not the original manually-loaded plan C: ...
the results are computed using your workload history, your recent history of bind variable values. It would be that "A" was evaluated with differing sets of binds over time, not that "A" was evaluated with differing plans.
The plan is fixed with each baseline.
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SYS_SQL_xxxxxxx',
format=>'basic'));
will show you the plans themselves.
Enable SQL Plan Baselines?
A reader, January 28, 2010 - 11:14 am UTC
Hi Tom,
I'm not sure if this fits here.
We are about to upgrade from 9iR2 to 11gR1 and are defining our standard database setup baseline at the moment.
We have a lot of legacy applications and also a lot of java developers with little knowledge of Oracle (they shield themselves by using generic SQL layers like hibernate; sql tuning is the part of the database they say; yes I know your opinion on this). So, we are living with bad execution plans, execution plan regressions,...
My question is if we should enable following features per default in our standard database setup:
1) SQL Plan Baselines (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE)
2) SQL Profile acceptance (DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
)
Do you think it would be a good idea to enable this per default as these features are disabled "out-of-the-oracle-box"?
Thanks,
Michael
January 29, 2010 - 4:01 pm UTC
... also a lot of java developers with
little knowledge of Oracle ...
wow, that make you almost unique in the world - probably only about 99.9999999% of the other sites out there are like you
if you were going to 11gR2, you could use
http://docs.oracle.com/docs/cd/E11882_01/server.112/e10821/optplanmgmt.htm#PFGRF3616 that would let you freeze the plans you have now easily in 9i and reuse them in 11g. As it is, you would probably want to use baselines going forward (most everyone likely will), but it won't give you the same plans as you had in 9i right now.
but yes, you would likely want baselines in place, knowing that in 11gr2 there are even more options with them, but they will give you "consistent plans" over time.
Enabling SQL Profiles
A reader, February 01, 2010 - 5:34 am UTC
Hi Tom,
Thanks for your knowledge. If I got you right, you said we should enable SQL Plan Baselines per default in any case?
Do you think we should also enable the acception of SQL Profiles per default?
Are there any possible side effects if we enable this features per default?
Thanks,
Michael
February 01, 2010 - 10:36 am UTC
you should
a) understand what these things are/do
b) use them as you see fit after (a) happens
sql plan baselines - freeze the query plan, until we prove that a new plan is measurably better.
sql profiles - more statistics, about the query itself, using your workload (your binds). If using sql plan baselines in 11gr2 - we'll have tested the profile and verified it is in fact "a better plan" before using it.
An Oracle 11.2 baseline question
Suzanne, March 18, 2010 - 5:10 pm UTC
I have a problem with Siebel 7.8 and Oracle. Siebel’s system set up specifies the OPTIMIZER_MODE be set to ALL_ROWS (which is a good thing). Unfortunately the client connection startup runs these commands (they are hardcoded in the software) –
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10; - this is my real issue
ALTER SESSION SET HASH_JOIN_ENABLED = FALSE ;
ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED"= FALSE ;
ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE ;
I’ve tried to have these removed from the client startup but I’ve been told by Siebel that this fix is in Siebel 8.0. In Oracle 10g these same two queries performed badly. I put in an SR, we figured out that setting the OPTIMIZER_MODE back to ALL_ROWS fixed the slowness. Part of the problem was that Siebel generates its queries on the fly instead of keeping them in a library so a hint won’t work. Thank goodness Siebel uses bind variables and generates the same query every time this particular feature is called. Customer Support told me how to “trick” Oracle into using a plan outline generated by my query that contained the hint every time one of these two problem queries was submitted to the database.
Now for my question. Oracle 11.2 doesn’t use outlines anymore it uses baselines. Baselines are considerably more intelligent and they are supposed to evolve over time. What I need to do is create a baseline (actually two baselines, one for each of my problem queries) that includes the hint for ALL_ROWS, then I need to trick Oracle into using this baseline every time it gets one or the other of my problem queries. I also need to prevent Oracle from running the automatic tuning sessions on these two queries so that it doesn’t try and evolve these baselines over time. How do I do this?
March 18, 2010 - 5:37 pm UTC
you can migrate your stored outlines to a SPM (sql plan management) managed baseline in 11.2
DBMS_SPM.MIGRATE_STORED_OUTLINE
Read about sql plan management in 11g in general, it is what you are looking for.
!
Sokrates, March 19, 2010 - 2:22 am UTC
side note: I always laugh when people flush the buffer cache or shared pool during a 'benchmark' to make it 'fair', what a wrong thing to do
thank you for that !
you are absolutely right
Raminder, June 09, 2010 - 12:48 pm UTC
In my case, the evolve report says the test is better, but does not accept it, the improvement being deemed marginal.
So, how much improvement over the baseline would be deemed acceptable? Also, I could not find a procedure to manually "accept" a plan. I can "fix" it using the ALTER_SQL_PLAN_BASELINE function, but I could not change accepted to YES. I see that a plan with ACCEPTED=NO is not used even though the FIXED attribute is set to YES.
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
PLAN_LIST = SQL_PLAN_6zsnd8f6zsd9g54bc8843
TIME_LIMIT = DBMS_SPM.NO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_6zsnd8f6zsd9g54bc8843
------------------------------------
Plan was verified: Time used 1.32 seconds.
Plan failed performance criterion: 1.19 times better than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 483.696 128.307 3.77
CPU Time(ms): 274.458 46.564 5.89
Buffer Gets: 2030 1718 1.18
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0
June 10, 2010 - 12:12 pm UTC
it is a weighted function of more than one variable - so it isn't really possible to say "it is X" for "how much improvement". The code currently says "it must be a substantial improvement before we take the risk of promoting the plan into production"
you can evolve without verifying - that'll accept it
http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_spm.htm#sthref8810
What is the threshold?
Michael Tefft, June 10, 2010 - 12:31 pm UTC
I thought I saw, in a presentation somewhere, that a new plan must be at least 1.5 times better than the current baseline to be evolved 'with verification'. And after seeing hundreds of these plans compared, I am 98% sure that the ones with < 1.5 improvement were universally rejected, while the ones with > 1.5 improvement were universally accepted.
But I have not seen that threshold stated in any document, so I am sure it is subject to change, etc. And, since the overall metric is not documented either, it is more a point of curiosity than anything else.
When I first started working with the baselines, I worried about 'should we be scrutinizing the results of the evolve process? What if it didn't evolve something that We Know Should Be Better?' But I don't worry about that anymore.
It's a good idea to watch for evolve errors, for a sudden unexpected flood of new baseliens, etc. But trying to second-guess every Evolve result is not a good use of time.
It does what it is supposed to do: keep your system from going off the rails from a foolish new plan that is theoretically better, but realistically not.
Error Encountered During Plan Verification...
Michael H, June 16, 2010 - 4:45 pm UTC
You asked if there was any advice regarding the 'An error was encountered during plan verification set up' message.
I can't give any general rules around this, but one thing I learned today is that if the ID which is being used to execute DBMS_SPM does not have appropriate privileges/synonyms on the objects that you will get this message.
Since I thought someone else might google for this same error string, I wanted to share my experience. Once I figured out what was going on here, I was able to use EVOLVE_SQL_PLAN_BASELINE as I was intending to.
Fixed Plans
jatin, August 11, 2010 - 2:43 pm UTC
Hi
1. Can you please explain the concept of "fixed" plans? In what cases do we use it - & how is it different (in terms of precedence) from a normal accepted plan?
2. Will adaptive cursor sharing not come into play in case we use SQL baselines (plan is accepted)? If so, how can we handle the bind variable peeking issue in this case?
August 19, 2010 - 12:14 am UTC
1) without any 'context', I'll assume you mean my usage of 'fixed'. 'Fixed' as in 'stuck' - as in 'not changeable'
We make the plan be one of the plans from the baseline - we 'fix' the set of plans, we make them be from a set of known good plans.
2) it'll come into effect but 'differently'. when you use sql plan management - the only plans we'll use come from the accepted baseline. So, if adaptive cursor sharing came up with a non-validated plan - it would register it (so we can test it out later) but not use it.
Fixed SQL Plan Baselines
Michael Tefft, August 19, 2010 - 5:59 am UTC
How to freeze query plan for Queries not using bind Variables in 11gR2
Dan, August 19, 2010 - 7:39 pm UTC
Tom:
This is regarding freezing query plan in 11g.
I ran a query to capture the SQLPLAN baseline as schema owner and accepted it. Can this SQLplan be reused by application user (another oracle id) if they run the same query ?
a) should the creator/parsing_schema_name be same for the user-id who executes the query in order to reuse the frozen plan ?
b) I want to freeze the query plan but application is not using bind variables (will be implemented soon). Queries are using different inputs every time they are submitted. How do i handle this ?
Can show me example.
August 25, 2010 - 12:12 pm UTC
a) as long as the query is really the "same", the plan can be reused - yes.
what I mean by that is:
if I issue: select * from t;
and you issue: select * from t;
as long as they are going after the "same table t" - yes, they can use the same plans.
b) how can you freeze something that hasn't ever been seen before? Think about this - you cannot freeze something you haven't ever executed. The queries - if they use different inputs - are BY DEFINITION different, they will not share the same plan baseline - they cannot - they are different queries.
freeze query plan
Dan, August 25, 2010 - 12:39 pm UTC
thanks Tom.
>> b) how can you freeze something that hasn't ever been seen before? Think about this - you cannot freeze something you haven't ever executed. The queries - if they use different inputs - are BY DEFINITION different, they will not share the same plan baseline - they cannot - they are different queries.
So changing the queries to use bind variables is the only way to reuse the frozen query plan.
August 26, 2010 - 12:51 pm UTC
Let me put it even more simply:
changing the queries to use binds variables is the only way to reuse plans
period. different sql text -> DIFFERENT SQL -> different execution plans, even if they happen to "look the same". the sql is different. We can only match on the SQL.
cursor sharing might apply, but I'm not a fan of cursor sharing force/similar so I always hesitate to suggest it. If you want to use binds, you should use binds.
mfz, August 25, 2010 - 12:52 pm UTC
"....So changing the queries to use bind variables is the only way to reuse the frozen query plan....."
If I have a skewed data and have histograms collected on that skewed data and with the introduction of Adaptive Curor Sharing , we can't freeze the plans , even when using bind variable.
Data Changes / Parameter Changes ... Plan Changes .
SQL Plan Baseliens vs. Adaptive Cursor Sharing
Michael Tefft, August 26, 2010 - 1:12 pm UTC
>If I have a skewed data and have histograms collected on that skewed data and with the introduction
of Adaptive Curor Sharing , we can't freeze the plans , even when using bind variable.
That is not my understanding of adaptive cursor sharing. If you have set parameters to use SQL Plan Baselines, then you are bound by those rules. Adaptive cursor sharing kicks in earlier - in fact, it can determine that a new plan should be proposed even if there is matching SQL already in the shared pool - but any plan proposed would still be checked against the accepted plans in the baseline.
SQL Plan management
Ajeet, July 04, 2011 - 9:59 am UTC
Hi Tom,
We are in planing phase of upgrade from 10.2.0.8 to 11 G R2. I was reading thru SQL Plan management and Real application testing. I have following 2 questions:
1. If i am not using stored outlines in 10 G database, how can I have similar plans in 11 G database as I have in 10 G database. do you suggest that I should first run my application with optimizer_feature_enable = 10.2.0.8 with 11 G database ( in test environment ). and then capture the baseline plans. and finally change the optimizar_feature_enable to 11 G. or should I do this at the session level for the problematic queries (where I see a peformance regression ). what should be the approach.
2. can sql plan management integrated with real application testing in any possible way - would it even make sense.
3. will real application testing help in finding any sql syntax/sementics changes such as a sql which used to work in 10 G but do not works in 11 G due to stricter syntax checks /semantics checks .
Regards
Ajeet
July 05, 2011 - 11:49 am UTC
1) just generate a query plan baseline in 10g, running in production.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#CHDHBGJD 2) sure it can - you'd see what would happen if you had plan management enabled. You could even test what would happen if you evolved plans before running the test if you wanted. but at the very least, it would show you what would happen in production if you were to upgrade with plan management in place.
3) yes, it would - you get an error divergence report.
very helpful
Ajeet, July 06, 2011 - 10:13 am UTC
Hi Tom,
Thanks for you answers to questions above.I am reading more on #1.
- just generate a query plan baseline in 10g, running in production.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#CHDHBGJD My question is - if I create the baseline 10g, can I take this to 11 G database and use these plans as baseline plan to ensure that I will have similar performance with 11 G database. where can I read more about this process .
Regards
Ajeet
July 08, 2011 - 11:47 am UTC
just upgrade the 10g database, the plan baseline will be there. You need to have the same data to test with - so just restore your 10g database, upgrade it (that'll let you see the steps and practice the upgrade) and the test away.
SPM for DW
Prashant, January 02, 2012 - 10:56 pm UTC
Hi Tom
How can SPM be best leveraged for a datawarehouse where the users can submit ad-hoc queries and each query could be so unique? Its almost impossible to create the different scenarios in stage or dev testing to enable bulk load of execution plans.
So does that leave us with Automatic plan capture and are there any known issues or best practices while using automatic plan capture for such kind of applications (BI)? How can we best leverage SPM for such applications?
Thanks
Prashant
January 03, 2012 - 11:28 am UTC
How can SPM be best leveraged for a datawarehouse where the users can submit
ad-hoc queries and each query could be so unique?
it, by definition, cannot be. sql plan management is - by definition - based on a history, without any history, there is nothing to "manage".
sql plan management and an ad-hoc environment are rather orthogonal to each other, doesn't make sense to even consider it in that environment. You'd need an environment with lots of shareable sql that is run over and over (OLTP)
Thank you
Prashant, January 03, 2012 - 10:02 pm UTC
stored outlines and spm
asktom reader, January 04, 2012 - 2:12 pm UTC
Hi Tom,
As of 11gR2, if both a stored outline and sql plan baseline exists for a sql statement, the stored outline still takes precedence, as shown in its execution plan. Is stored outline really deprecated in 11gR2?
Thanks.
January 04, 2012 - 3:45 pm UTC
stored outlines are deprecated, yes. deprecated doesn't mean "gone missing", but rather "stop using, they are going away in some future release - are not enhanced - and could start causing strange issues"
read:
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html in particular "Differences vs. Stored Outlines"
the outline will take precedence because the outline will put the plan into the baseline.
Using STS to basline for 10g to 11g migration
A reader, April 12, 2012 - 3:33 pm UTC
Hi Tom,
We are migrating from 10g on Solaris to 11g on Linux using two main paths: schema expdp/impdp for smaller databases and transportable tablespaces for larger ones.
Q1: For those cases when bind variables are used by apps, will the below approach ensure that I will have exactly the same plans in 11g after migration:
Step 1. on 10g Solaris:
create sql tuning set for all AWR snapshots
export the created sql tuning set
Step 2. on 11g Linux:
import the sql tuning set exported in step 1
load sql baselines from the imported STS, and set them all as FIXED, ACCEPTED, ENABLED
Q2: Because AWR may be missing some sql that falls below the set thresholds, I may not have absolutely all sql in the captured STS. Is there any other way to still capture them into STS?
Q4: Is it possible that because of the endian code difference between Solaris and Linux, the loaded base lines will not match the sql executing in 11g even though it is the same sql? The reason I'm asking, because we noticed that the plan hash values are different for the same plan in Solaris vs. Linux, and we were told by Oracle Support that this can happen in cross platform migrations.
Thanks
Questions about Evolving sql-plan baselines
Rajeshwaran, Jeyabal, August 15, 2012 - 4:49 am UTC
Tom:
I was reading about sql-plan baselines from product docs
http://docs.oracle.com/cd/E11882_01/server.112/e10821/optplanmgmt.htm#autoId10 <quote>
The PL/SQL function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE tries to evolve new plans that have been added by the optimizer to the plan history of existing plan baselines
</quote>
Lets say we have
1) 4 plan got accepted (so these 4 plans will be available in sql plan baseline & lets assume no of them are marked as FIXED )
2) We have one new plan available in sql-plan-history.
3) What happens when we invoke, dbms_spm.evolve_sql_plan_baseline?
does oracle compare this new plan against all the 4 plans available in baseline?
August 17, 2012 - 2:45 pm UTC
it doesn't have to, it wouldn't be in the history if it were in the baseline - so it must be new.
if it were in the baseline, we would have just used it - it wouldn't have been put in the history. plans get in there because they are not in the baseline and haven't been evolved yet
Question
A reader, September 12, 2012 - 8:33 am UTC
As the Topic is "Evolving SQL Plan Baselines", I want to know the below :
* if we have set both capture and use parameter true. Oracle will automatically capture Plans, but will it automatically evolve new plans to accepted or it will wait for evolve it manually before using new plan.
* If it Automatically evolve, when and how will it?
* If it Automatically, will change old plan's flag accepted to No? or add one more accepted plan
Or
we have to manually evolve plans all the time?
regards
September 14, 2012 - 6:29 pm UTC
if you are capturing, you are capturing, they go into the baseline.
the entire premise is that you would 'evolve' the plans later, in a maintenance window. You wouldn't want to capture after the initial capture.
Who is in charge or sql plan baseline evolution?
Galen Boyer, December 19, 2012 - 10:00 am UTC
Hi Tom,
In Oracle docs (the hash tag for anchoring bolded everything after it so I went to tinyurl)
http://tinyurl.com/crn32ww we see that Oracle will parse and get a new plan and then see if that plan exists in the plan outline as accepted. If not, it will choose an accepted plan but also put the plan it came up with in the plan outline as "unaccepted" and then later decides if the plan is acceptable. This would suggest that Oracle executes with the new plan at some future point to then compare and make this judgement, or do we do this. When does this happen? Are we completely in charge of the SQL plan baseline evolution phase? Should we keep tabs on the new plans entering the baseline as unaccepted and do background research/testing on these new ones for acceptance into the plan baseline?
Is there anyway that you have devised or is Oracle working on anyway where we could tie procedure bodies to sql_ids so that during recompilation we could be alerted that the sql being recompiled is tied to sql in a sql baseline? This is our biggest issue with baselines. We go to the trouble of defining a baseline and then a new release of a package loses the tie to the baseline and we are back to square one.
December 19, 2012 - 12:16 pm UTC
This would suggest that Oracle executes with the new plan at some future point to then compare and make this judgementit can be automagic or done on demand.
http://www.antognini.ch/2008/12/automatic-evolution-of-sql-plan-baselines/ ... We go to the trouble of defining a baseline and then a new release of a package loses the tie to the baseline and we are back to square one. ...
why would that happen? baselines are based on the text of the sql, not the version of a plsql package. I'm not sure what you mean?
Why do we lose the tie?
Galen Boyer, December 20, 2012 - 9:48 am UTC
Hi Tom,
Thanks for responding. We lose the tie because some change to the sql happens in a future dot release of some project's codebase. It would be nice to have someway to be warned that the recompilation is tied to baselines so we could then look at the new sql and compare its new plans with the baselines that were previously in play and maybe create a new baseline or tie it back to the old baseline or at least be aware that the baseline is now not going to be used.
Please understand that I work on a team that does not write most of this code, but instead, we get called when application code is performing poorly. Our team is one and the number of application teams deploying code is many. We will put a baseline in place for some poorly written code, or just some code that the optimizer needs some help on and then a new dot release happens, the code is upgraded and recompiled and the baselines are no longer used and we get called again. We do our best to educate and do code reviews, but through the normal pressures of release for a large organization with many projects in the release pipeline, we just can't stop bad code from getting into prod. The same bad code being revisited for the same reasons but yet again in the pressure of a production process not behaving is what we are trying to avoid. We would like to use baselines but liberal hinting is more attractive in that hinting at least can provide a bit more stability in the plan vs a baseline that is no longer used tomorrow. We also have a selfish team reason to want to avoid it because we start to look bad in that the code we fixed last month is now behaving poorly again. Yes, that is because the developer caused it, but from the ivory towers, the Oracle server and our team start having to be defended, no matter how much we explain why things happened.
So, a tie between a procedure/function to its sql_id would be a really nice feature. Some warning coming out of the compilation telling us, "Hey, your package has sql that has been tied to a baseline, please make sure you know what that means..." would be a nice message.
December 20, 2012 - 5:28 pm UTC
basically - there is no "tie", plsql is not tied to the sql therein, it is just "data" - a string...
Ok, now I see what you mean - you want to know when the SQL in a package is different from the SQL that used to be there (and further, if that old sql had a baseline associated with it)
we don't track down the the "sql level" - we track dependencies, but not the individual SQL's.
You can sort of track if a package was having SQL that had a baseline - v$sql has program_id (the object id of the plsql unit) and sql-id. If you were to save that from time to time, you'd know if a package had some sql that was baselined - but it wouldn't be able to tell you if the sql in the new version of the code had changed.
I don't know that we'd ever get to that point - where you want - we just don't really track to that level...
Oh well, would be nice
Galen Boyer, December 20, 2012 - 10:04 pm UTC
I was thinking along the lines of
dbms_spm.tie_a_package(sql_id, package_name)
If the tie exists, let us know about it during compilation. We can use the helper procedure to tie it ourselves if its important enough for us.
Questions on using baselines
ravi vaddepati, December 20, 2012 - 10:48 pm UTC
Tom,
A big query (800 lines 30 tables) suddenly started performing poorly (in latest version 11.2.0.3). The query exists for nearly two years; the statistics are up to date; and no sudden increase in data volumes. To my surprise, I see eleven different plans (different plan-hash-values) in the history with very slight changes between each of these. The plan currently using (in cache) is worst among all plans (highest LIO/row processed and highest elapsed time/exec). What is the best solution? Use baseline (accept/fix/enable) to the best plan OR add hints to force best plan OR any other solution? I recently noted that, in latest versions, more and more hints are being needed to force specific plan. In previous versions such as 10.2, we used to achieve required plans with a simple ORDERED hint and proper order of tables in FROM clause. But in recent versions, we are ending up adding additional join hints, index hints etc. to force good plans.
Our database level “capture baselines” is set to false. Will it be better to turning on, capture baselines at database level constantly and disable the bad plans in the baselines we hit the above situation?
Is there any side effect if we turn on the capture at the database level?
Thanks
Ravi
January 02, 2013 - 8:22 am UTC
no hints, please no hints.
I can give you examples in 10 where a simple ORDERED hint would not be sufficient and you'd have to add more and more to get it be to "good". It has always been that way. That is the problem with hints - you have to be very very very very specific (and many programmers do not realize that). A simple upgrade/patch could lead to radically different plans
use a baseline for this particular query and consider using sql plan management (it will only use better plans for queries after testing them - the optimizer will not use a new plan if one exists in the baseline until it proves to itself that the new plan is better)
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
error in querying sql plan baseline
A reader, February 01, 2013 - 9:57 am UTC
Hi Tom,
Today I got this error when running the following statement. I got the same error even when running this as user SYS:
I have omitted the sql handle in the following query:
SELECT plan_table_output FROM TABLE(dbms_xplan.display_sql_plan_baseline('SQL_handled_omitted'));
ERROR: User has no SELECT privileges on objects of the SQL plan baseline
So what causes this error?
Thanks!
February 01, 2013 - 2:35 pm UTC
this is likely caused by the baseline having been created by a user different than the parsing user of the sql. please contact support and reference bug15976240 to verify.
Acceptance Threshold
Vishal, January 28, 2014 - 7:38 am UTC
Acceptance threshold for evolving baselines is 1.5. It is controlled by a hidden parameter "_plan_verify_improvement_margin". 150 is the default value.
Error encountered during plan verification
Praveen, February 21, 2014 - 8:27 pm UTC
Hi Tom,
I do get the below error when I'm trying to evolve the sqlplan even though I have been granted ADMINISTER SQL MANAGEMENT OBJECT through a role
Any thoughts why do I get this error?
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_b9e6a3344a0daef3');
DBMS_OUTPUT.PUT_LINE(report);
END;
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_b9e6a3344a0daef3
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_bmtp36j50vbrm1155002a
------------------------------------
Plan was not verified.
Error encountered during plan verification (ORA-1031).
ORA-01031: insufficient privileges
Plan: SQL_PLAN_bmtp36j50vbrm4d53ca8a
------------------------------------
Plan was not verified.
Error encountered during plan verification (ORA-1031).
ORA-01031: insufficient privileges
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.19
Wanted to associate an execution plan to another statement
Carmen, September 14, 2017 - 10:40 am UTC
I've several statements of the BPEL application performing bad, and the only way in my tests I could get a good execution plan is modifying the statement with a hint forcing the use of one INDEX, but I cannot do this in the application. I tried gathering statistics of the tables, indexes, ... unsuccessfully. The plan that performs in 22 seconds is the most expensive one and the choosed one takes 10 minutes (BITMAP CONVERSIONS FROM ROWID)....
I know how to load in SPM the plan for the test statement (baseline), but I'm stucked because I cannot associate it to the original statement, as it's different (hint) and I'm executing it with other user, and cannot force the application to generate this plan in the cursor cache to load from it in SPM.
Is there a way to achieve this?
Thank you for your help.
September 19, 2017 - 2:39 am UTC