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.

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.

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.)

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.

select * from table( 
    dbms_xplan.display_sql_plan_baseline( 
        sql_handle=>'SYS_SQL_xxxxxxx', 
        format=>'basic'));

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

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

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?

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

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

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.

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.

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?

See http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BABICJBG

We have not had cause to use 'fixed' baselines. My interpretation is: If you are allowing your baselines to evolve, but you have a plan that you are 100% sure is the only one you want, and you do not want Oracle to try to improve on it - mark it 'fixed'.

You can set this with DBMS_SPM.ALTER_SQL_PLAN_BASELINE or with Enterprise Manager.

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.

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.

"....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 .

>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.

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

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

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

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.

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

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?

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

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.

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.

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.

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

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!

Acceptance threshold for evolving baselines is 1.5. It is controlled by a hidden parameter "_plan_verify_improvement_margin". 150 is the default value.

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

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.