Thank you so much Andy.
If I get it correct, it's the sql profile(which is generally a combination
of opt_estimate hints) which oracle will use to evaluate the plans but it's
ultimately the sqlplan baselines (which must be in accepted+enabled) state
which is going to be used finally.
And in scenarios where we use technique to manually create sql profile by
passing exact outline hints (but not with opt_etsimate hints) i.e. using
procedure dbms_sqltune.import_sql_profile where we have ability to pass the
exact full query outline hints which restricts the execution path to one.
In those cases it will mostly fix the exact one execution path for the
query no matter how many times it parses. And in the presence of such a sql
profile , if the sql plan baseline has exactly the same path
enabled+accepted that is going to be used or else it's going to be
completely discarded and the priority will be given to the baseline which
is in accepted+enabled state. (And the discarded plan i.e the parsed plan
using sql profile may be added a new baseline to the list with accepted
flag as NO). Which means sql plan baseline having enabled+accepted flag
as "YES" will take the precedence always irrespective of sql profile/sq
patch etc. Is this understanding correct?
Or
In other words the sql profile or sql patch helps to create new plans based
on the embedded hints however presence of baseline with accepted +enabled
flag as 'YES' is always going to rule at the end. Correct if my
understanding is wrong here?
On Fri, Dec 17, 2021 at 1:53 AM Andy Klock <andy@xxxxxxxxxxxxx> wrote:
Hi Laurentiu,
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, December 16th, 2021 at 3:08 PM, Pap <
oracle.developer35@xxxxxxxxx> wrote:
So in cases where the SQL profile or SQL patch has some hints (say FTS on
tab1) which is exactly opposite than that of the one in SQL plan
baseline(say index access for tab1). In that scenario will oracle follow
profile/patch or the baseline path?
If a cursor has "accepted" baselines, then that is typically what the CBO
is going to prefer. When there are multiple accepted plans, then the CBO
will pick the plan with the lowest cost. For cursors that also have a SQL
Profile, then those hints and opt_estimates are applied which may affect
the cost of the baseline plans that the CBO will pick.
Maria wrote a nice article about it [1] in 2012 which I think is still
relevant, but often when you have cursors with lots of baselines, SQL
Profiles, and patches it's time to fix the stats or change the code :)
Andy K
[1]
https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
