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@xxxxxxxxx)> 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@xxxxxxxxx (mailto: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
