Re: SQL Profile and baseline

The first question that needs to be answered is whether this is a real SQL Profile generated by the Tuning Advisor, or a hack that copies the Outline information or other set of hints into the data dictionary.

Dom and Mohamed have already mentioned the point about parse times - an SQL Profile (especially a hack) could reduce parse time dramatically, and replacing it with an SQL Plab Baseline would not do that since the optimizer goes through the parse phase first when all it's got is an SQL Plan Baseline. The other point about SQL Plan Baselines is that Oracle will keep on generating new ones for a statement once the first one exists EVEN IF you set "create_sql_plan_baselines" to false (and then might start auto evolving them in 19c, wasating resources overnight).

On the plus side for SQL Profiles - they can be set to "force match" - which means statements which differ only in their literal values can use the same profiles.SQL Plan Baselines don't allow for that.

If it's a genuine SQL Profile then it's likely to give as good a plan, or possibly better plan on the upgrade since it will be correcting errors in the optimizer's arithmetic, particularly relating to cardinality. But that does depend to some extent, on how you've been collecting stats in 11g and how you collect them after the upgrade to 19c.

Fake SQL Profiles, and SQL Plan Baselines, may include an opt_param hint to set the optimizer_features_enable value to 11.2.0.4, which means that (mostly) the plans won't change - but there are so many bugs and anomalies fixed by 19c that a "complete" set of 11g hints may change plans in 19c even with the OFE set backwards. And if the OFE is not set backwards then there are so many enhancements that plans can very easily change while obeying every hint you've specified.

Personally I favour using SQL Patches to attach hints, and SQL Profiles only if they were properly generated - and even then I'd be looking at making sure I had done the right thing with column groups, function based indexes/virtual columns, and histograms before resorting to SQL Profiles. (You also have to remember that 19c will happily generate SQL Pan directives that might force dynamic sampling and might automatically generate column groups for you as another way to mess with your head.)

IIRC SQL Patches are limited to a single line of 200 characters in 11g, but when you upgrade they're allowed to be as long as you need. Theoretically SQL Patches cannot "force match" - technically it is possible and very easy to do (so that might change in a future version of Oracle).

Bottom line - there is not one correct decision for you, particularly since you're at a bad starting point. What I would suggest is that if you SQL Profiles are hacks then keep them that way (don't change what isn't broken) until you've upgraded and then see what the new stats collection mechanism and virtual columns etc. and do for you.

Regards
Jonathan Lewis

P.S. I'll have to tidy this up and publish it as a blog note, I think. (If it doesn't already exist).

On Tue, 1 Feb 2022 at 18:06, Hameed, Amir <amir.hameed_at_sleepnumber.com> wrote:

> Hi,
>
> As a general guideline, once a SQL profile provides an optimal execution
> plan for a SQL, is it okay to create a baseline for that SQL and then drop
> the SQL Profile? The DB version is 11.2.0.4. Any feedback will be
> appreciated.
>
>
>
> Thanks,
>
> Amir
>

--
http://www.freelists.org/webpage/oracle-l