Re: Plan changed After creating profile

Thanks for posting the profile content.

That's a "proper" SQL Profile produced by the dbms_sqltune so all it's doing is adjusting the optimizer's arithmetic and letting the optimizer pick the best plan based on the estimates it produced.

If I understand correctly, this profile was created while the database was running 11.2.0.4, and since then you've upgraded to 19.9 and more recently to 19.11. After the upgrade the query was performing adequately for a few months but has suddenly started to perform badly.

The most common problem with the optimizer is that a small change in the data and/or the stats about the data can result in a huge change in performance. This can happen at any time on any version of Oracle. Trivial example:

if the optimizer thinks table X will return 99 rows it will use a nested loop and indexed access into table Y; if it thinks that table X will return 100 rows then it will use a hash join with a tablescan of table Y. The change in plan is a good idea in principle - but shouldn't actually happen until table X returns more than 1,000 rows and the optimizer has decided to do the switch too soon because the arithmetic about table Y says the indexed access will fetch too many rows, or visit too many table blocks.

This type of arithmetic error can still happen after you've created the profile - it's just that it probably won't happen because the profile has been carefully constructed to make the final arithmetic as realistic as possible. HOWEVER - when you upgrade, you will find that the optimizer (a) may have gathered stats differently and (b) may have some corrections applied to the way it does the arithmetic and (c) may have some new options for transformations. This gives you three different ways in which the "corrections" supplied by the profile may no longer be good enough to allow the optimizer to produce a sensible plan all the time.

Examples of change on the upgrade
a) If your stats gather is using default methods you may now have top-frequency or hybrid histograms where you used to have height-balanced histograms; this can make a huge difference to cardinality estimates. b) An automatic job may have created some column group stats - which can make a huge difference
c) various subqueries can unnest in 19c that could not unnest in 11g, and the aritmetic of calculating costs of executing filter subqueries has changed so the profile could have a dramatically different effect on the plan than it used to.

Suggestions

1. Change the Category of this profile (it's DEFAULT at present) and see what happens - it's possible that the optimizer will pick the correct path. WHen doing an upgrade (particularly a long jump) it's always worth spending some time testing with all profiles, baselines, and patches disabled and all optimizer parameters set to default to see if the optimizer will do everything right without any fiddling.
2. Create a new tuning task for this statement (against data that is a very recent backup of production - or even on the production system if you're allowed) and check the profile it produces. There's a script to report the profile before it's accepted on my blog at https://jonathanlewis.wordpress.com/2007/02/11/profiles/ but I've just run a quick test and for 19c it produces multiple sets of data and I think it's the rows under REC_ID = 0 that will be the new profile. You can compare this set of hints with the hints in the original profile to see if you can spot where the most significant change in the numbers appears. This may help you pinpoint the problem. In any case, you may want to accept the new profile.

Regards
Jonathan Lewis

On Fri, 1 Oct 2021 at 18:47, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi All,
>
> Below is extract from Hint :
>
> PLAN_NAME SIGNATURE OPT_TYPE CATEGORY
> PLAN_ID HINT
> ---------- ---------- --------------------------------
> -------------------- ----------
> --------------------------------------------------
> SYS_SQLPRO 1.3066E+19 Profile DEFAULT
> 0 OPT_ESTIMATE(_at_"SEL$2", INDEX_SCAN, "AUDITTRA
> F_014d7f18
> IL"_at_"SEL$2", "IX_AUDITNAME", SCALE_ROWS=127210
> a95d0000
> .5501)
>
> ...

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