Hello Experts,
I am using 11.2.0.3 on AIX and below are my 2 questions
1. For a bad running sql the current plan was having a merge join cartesian and it was running forver. Checked the AWR Plans and one of the plan was good, so baselined the plan from AWR. But the SQL when rerun after creating the plan baseline still picks the bad plan and not the baselined plan. I see all the objects/indexes which existed in the AWR are there in the current plan and they are all valid but for some reason its not picking the baselined plan, any clue why this might be happening?
2. I have a procedure which reads a date table and calls another procedure passing the date, which in turns executes a cursor using the dates and fetches data which is inserted into target tables. When this procedure ran for month1-year1 it picked a good plan say plan1 and for next month it picked a bad plan plan2(new child cursor). So i baselined plan1 (good plan with enabled/accepted and fixed too set to Yes) hoping all the subsequent runs of the SQL run will use the same plan. But it did not, it used it for few months but then again started creating multiple child cursors with diff plans. Again i had to baseline a good plan among the new child cursors. So it used for few months and goes back to not using the baselined plan. Is it happening due to ACS kicking in, but even with it when I baseline a plan it should always use it, right? Unless I am missing some underlying concepts about baselines.
Thanks