Re: SQL Profile and baseline

Just that, and it doesn’t sound like your situation, a SQL profile or SQL patch can be used to address a specific rare problem where, for whatever reason, excessive CPU is used during parsing. Under these circumstances, because a SQL Plan Baseline always goes through the from-scratch best cost parse process anyway, it is not effective at addressing that problem in the same way a SQL profile or SQL patch can be.

Sent from my iPhone

On 2 Feb 2022, at 17:11, Amir Hameed <axhameed_at_yahoo.com> wrote:


Thank you for all the feedback. This is a packaged application and therefore, we cannot directly modify the code. There are some other constraints that are prohibiting us from modifying the code. There are a total of seven statements out of which three were optimized by creating SQL Profiles from the Tuning Advisor, whereas SQL Plan Baselines were created for the remaining four statements. The reason for creating the SQL Plan Baseline was to ensure that once we know that those statements were executing the correct execution plans that we wanted them to execute, we would lock those plans for those statements.

All baseline plans were created with the FIX attribute set to YES. The OPTIMIZER_CAPTURE_SQL_PLAN parameter is set to FALSE.

I didn't quite understand the parsing impact when using SQL Profile versus Baseline. Can someone please elaborate on it?

Thank you,
Amir

On Wednesday, February 2, 2022, 04:37:01 AM EST, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

Force matching – excellent point... of course.

Sent from Mail<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C9823140e57ee4159adce08d9e66efda7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637794186639408150%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=G%2BfZehYyYqurkXfKi2ih817x%2BHO85AahLbUB1BVDLCo%3D&reserved=0> for Windows

From: Jonathan Lewis<mailto:jlewisoracle_at_gmail.com> Sent: 02 February 2022 08:37
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: 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<mailto: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