Re: Plan changed After creating profile

Hi Jonathan ,

Thanks for your detailed explanation , surely we will try up this recommendations

Regards,
krishna

On Sat, 2 Oct 2021 at 13:47, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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
> ==========
> a) 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.
>
> b) 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