Re: Plan changed After creating profile

 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 (mailto: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 (tel:1733635396) 3 208406 5.00887472 >>>>>>>> bad plan
> 1 816394429 (tel:816394429) 7 36 .043790694 (tel:043790694)
> 2 816394429 (tel:816394429) 0 1020 .005087153 (tel:005087153)
> 2 816394429 (tel:816394429) 1 764142 .001283816 (tel:001283816)
>
>
>
> INST_ID PLAN_HASH_VALUE EXECUTIONS AVG_TIME AVG_LIO AVG_ROW SQL_PROFIL
> ---------- --------------- ---------- ---------- -------------- ---------- ----------
> 1 1733635396 (tel:1733635396) 208442 5.01300161 5,565.1 2.44969344 SYS_SQLPRF_014d7f1895d0000
>
> 2 816394429 (tel:816394429) 765162 .001288886 9.3 (tel: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 (x-apple-data-detectors://12), Krishnaprasad Yadav <chrishna0007_at_gmail.com (mailto: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