Mani,
A couiple of thoughts:
1: What was the patch for? If it was something to do with wrong results,
or CBO picking bad plan, then maybe the patch has blocked some
transformation that the SPB depended on,
2: if you can enable the 10053 (CBO) trace and force re-optimization of the
query (taking care to ensure that it's being called in exactly the way it
gets called in producton) you may find some clue in the trace file
explaining by the SPB has not been used.
Regards
Jonthan Lewis
On Mon, 9 Aug 2021 at 07:22, manikandan <pvmanikandan@xxxxxxxxx> wrote:
Hi,
We have implemented SPB for one query with a good plan. Post
implementation CBO was able to use the baseline without issue. We went for
DB bounce after that due to one-off patch maintenance and after the DB
bounce CBO is unable to use the BASELINE anymore and has created a new
plan. We have tried flushing the cursor multiple time but of no use. How we
can force the SQL to use the baseline? What would be the cause of query not
being used sql plan baseline post DB bounce?. Database version is 12.1.0.2
+ RAC 5 nodes + AIX.
Thanks,
Mani
