Hello,
In our application, cursor_sharing is set to EXACT. Application generates code dynamically using literals, and it does use bind variables. Due to this, we can get different SQL ID every day as the day changes even though it is essentially the same SQL in every aspect except for the value in the where condition. We observe occasional plan flip on certain SQLs which sometimes creates delays in producing reports to clients as SQL picks up a bad plan and takes longer than usual.
To achieve plan stability, one solution is to put cursor_sharing to FORCE so that cursor would be shared and we would have same SQL ID, on which , we can create baselines to force the good plan and achieve plan stability. However, setting cursor sharing to FORCE would have its own side effects.
Please suggest 1) if it is advisable to set cursor sharing to FORCE and 2) if not, what are the ways to achieve plan stability where in application does not use bind variables and there is no way to change application as it's a third party tool and not in our control.
Thanks and Regards,
Ateet