Hi All ,
Adding to my earlier mail ,
Below is details :
INST_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS AVG_TIME
----------- --------------- ------------ ---------- ----------
* 1 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 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@xxxxxxxxx>
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