Re: Plan changed After creating profile

If you want to always use a specific plan, you should be using sql baselines and fix the good plan.
Profiles will only last for a specific period of time, as when the DB behaviour/data change, the optimizer will just ignore them.

On Wed, Sep 29, 2021 at 9:06 PM Rajeev Prabhakar <rprabha01_at_gmail.com> wrote:

> Hi Krishna,
>
> Besides other things, please also check if this
> is the random case of data skew / due to usage
> of bind variables. If yes, look into the option of
> turning off peeking. At select/limited times,
> depending upon the use case, using literals
> instead might also be useful.
>
> Rajeev
>
>
> On Sep 29, 2021 at 3:27 PM, <Krishnaprasad Yadav <chrishna0007_at_gmail.com>>
> wrote:
>
> Hi All ,
>
> Adding to my earlier mail ,
>
>
> Below is details :
>
> INST_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS AVG_TIME
> ----------- --------------- ------------ ---------- ----------
> * 1 1733635396 <1733635396> 3 208406
> 5.00887472 >>>>>>>> bad plan*
> 1 816394429 7 36 .043790694
> 2 816394429 0 1020 .005087153
> 2 816394429 1 764142 .001283816
>
>
>
> INST_ID PLAN_HASH_VALUE EXECUTIONS AVG_TIME AVG_LIO AVG_ROW
> SQL_PROFIL
> ---------- --------------- ---------- ---------- --------------
> ---------- ----------
> 1 1733635396 208442 5.01300161 5,565.1
> 2.44969344 SYS_SQLPRF_014d7f1895d0000
>
> 2 816394429 765162 .001288886 9.3
> <001288886%209.3> 2.32738949 SYS_SQLPRO F_014d7f18a95d0000
>
>
> SQL Profile was created in 2015 and it is enabled in state , however today
> , sql was picking bad plan from good plan suddenly and creating huge mess
> up
>
> If any light on this can be shared would be great .
>
>
> Regards,
> Krishna
>
>
> On Thu, 30 Sept 2021 at 00:44, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
>> Hi All ,
>>
>> We have created sql profile on select statement ,but we see plan is
>> changed and worst plan is used in execution rather than sticking to plan
>> for which fixed it with profile .
>>
>> Is any way to avoid it or how can we stick permanently so that it can be
>> fixed with permanent plan .
>>
>> Regards,
>> Krishna
>>
>

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