Fwd: Plan changed After creating profile

This is a reply I wrote yesterday but I hit "reply" instead of "reply all", so I thought I'd echo it to the list to make it publicly available.

Regards
Jonathan Lewis

• Forwarded message --------- From: Jonathan Lewis <jlewisoracle_at_gmail.com> Date: Wed, 29 Sept 2021 at 20:49 Subject: Re: Plan changed After creating profile To: Krishnaprasad Yadav <chrishna0007_at_gmail.com>

Did you create the SQL Profile properly (i.e. using the tuning tool) or did you hack a set of "ordinary" hints into place by calling the dbms_sqltune package.

SQL Plan Baselines and SQL Profiles are very different - when created "legally".

An SQL Plan Baselines fixes an execution plan and ensures that the same plan is always used.
An SQL Profile tells the optimizer to adjust its arithmetic (and assumptions about cardinality) so that it can create the best execution plan based on its corrected understanding of the statistics.

This means that BOTH mechanisms can fail over time (even if you don't change the structure of the database).

The plan fixed by the baseline may become unsuitable because the volume or pattern in the data has changed - e.g.. the customer base has grown by 10% but the total number of orders has grown by a factor of 8 - a plan forcing a particular join order and mechanism for a DSS query may now be a very bad idea.

The "corrections" supplied by the profile may cease to be valid and make a changed plan look like a good idea when it is actually a bad idea. e.g. with the same example, a correction factor that said "there are 10,000 orders for every customer" is now wrong because applying the same factor to the real data will now lead Oracle to believe that there are "80,000 ordered for every customer".

Generally people seem to find SQL Plan Baslines more stable than SQL Profiles (but I can't quantify that, I only have anecdotal evidence) - so if you've generated a SQL Profile it might be a good idea to generate an SQL Plan Basline after running the query, accept it and fix it, then delete the profile.

Regards
Jonathan Lewis

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