Thanks to remember us that SPM can’t help solving a long time parsing (plan optimization) problem
I am thinking of the possibility to hint a SQL PLAN NAME taken from an existing SPM baseline. The CBO, before starting its plan optimization, knows already the presence (or not) of a SPM plan baseline as far as we find the following information in the beginning of the 10053 trace file:

SPM: statement found in SMB

This is why I don’t think it is too complicated for the Oracle Optimizer group to add a hint that allows using a given PLAN name from an existing SPM baseline. Something like this:

In that case the CBO has only to check the presence of this hinted SQL_PLAN_NAME and reproduce it. Otherwise the hint is ignored and the CBO will optimize its normal plan.

Of course several questions can be asked if this feature is implemented. I am thinking of what the CBO will do if the hinted SQL_PLAN_NAME is not valid (or not reproducible) but there is another SQL_PLAN_NAME for the same force matching signature which is valid and reproducible. If by hinting the plan we wanted to avoid the CBO optimization then we are not done in this case. And so on for each new implementation in Oracle.

The licensing bit is difficult – even with a note about baselines. Part of the confusion is that a call to create a patch calls the dbms_sqltune package and the licensing documents makes various noises about requiring the licence to query views and execute packages.

I would like to think that your comment about the 500 byte limit being an indication of expected use – but I suspect it’s more a question of someone creating a quick and dirty layer on top of dbms_sqltune to get a job done without having to grant execute rights on the whole tuning package. Ultimately the patch procedure and the profile procedure are the same procedure with a different input value for a couple of the parameters.

]]>By: Dom Brookshttps://jonathanlewis.wordpress.com/2014/07/06/sql-plan-baselines-2/#comment-69039
Tue, 08 Jul 2014 09:19:37 +0000http://jonathanlewis.wordpress.com/?p=13362#comment-69039Another good point. Yes, some licensing differences between baselines and sql profiles but are they that significant?https://blogs.oracle.com/optimizer/entry/does_the_use_of_sql

While baseline usage via DBMS_SPM is part of enterprise edition, that’s only good if your plan is in memory (various methods).
But if you want to transfer from AWR into SPM, you still need to go via a SQL Tuning Set and that requires DBMS_SQLTUNE which requires Tuning Pack.
And SQL Profile creation is also done via DBMS_SQLTUNE.

For the SQL patch, I was not aware of that limitation but does that then support the idea that a SQL Patch is best suited for injecting a single or small number of hints to avoid specific problems/bugs? e.g. inject a bind_aware hint (as per optimizer blog example) or inject a hint to prevent a particular transformation?

]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2014/07/06/sql-plan-baselines-2/#comment-69029
Tue, 08 Jul 2014 04:07:43 +0000http://jonathanlewis.wordpress.com/?p=13362#comment-69029On the plus side for profiles, of course, is that you can “force match” when you create them – which means apply the same profile to many statememts that differ only in their literal value usage. On the possible minus side is that you have to call dbms_sqltune to load a profile – and is there an official statement that you can do this without the diagnostic and performance pack licences ?

The minus side for patches is that you’re limited to 500 bytes at the total length of the text string you can use. (The procedure accepts a varchar2() parameter, but that’s cast into the first element of a variable of type sys.sqlprof_attr, which is an array(2000) of varchar2(500))

]]>By: Dom Brookshttps://jonathanlewis.wordpress.com/2014/07/06/sql-plan-baselines-2/#comment-68966
Mon, 07 Jul 2014 13:56:50 +0000http://jonathanlewis.wordpress.com/?p=13362#comment-68966> since I still feel uncomfortable putting “the wrong kind of hints” into an SQL profile
From a poll I did before, and from past and recent experience, a lot of DBAs are more comfortable with this style of SQL Profile (i.e. with plan outline hints) than with baselines.

I’m completely comfortable with it – it’s a supported methodology, published on Oracle Support, part of SQLT, etc, etc.

Good idea. I take it you mean using the SQL Profile or SQL Patch mechanism to apply the hints that would have been in the SQL Baseline so that you don’t have to change the code. Of the two I think I’d prefer to go with the SQL Patch, since I still feel uncomfortable putting “the wrong kind of hints” into an SQL profile.

]]>By: Dom Brookshttps://jonathanlewis.wordpress.com/2014/07/06/sql-plan-baselines-2/#comment-68954
Mon, 07 Jul 2014 09:19:33 +0000http://jonathanlewis.wordpress.com/?p=13362#comment-68954A good point – baselines are not a solution to parsing problems.

So, for parse problems (either slow or errors from bugs), there’s a good case for either a SQL Profile (for a specific plan) or a SQL Patch.(to avoid the bug)

SQL Profiles should be better, created either manually or better via the COE_XFR_SQL_PROFILE.SQL script of SQLT (1614107.1)
These should not have the parsing overhead because there’s no feedback mechanism for plans that the optimizer would have generated otherwise.