Hi Experts,
I am on Oracle 11.2.0.4 on Linux. For a very problematic sql we are using SPM (we will find root cause of issues also , but for now we are using SPM, so please take that as a given fact.) This sql has this problem: One plan takes about 11 seconds and another plan takes about one second. So using SPM in our test environment I fixed the good plan and disabled the bad plan. I used the SPM procedures like DBMS_SPM.load_plans_from_cursor_cache to load plans in the baseline. It loaded 2 plans. I then identified a good and bad plan and then fixed a good plan by this procedure: DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_sdfljsdjflsdfklsdjf',
plan_name => 'SQL_PLAN_sdsdsdsdss',
attribute_name => 'fixed',
attribute_value => 'YES');
However in another smaller test environment , I found that there was just one plan loaded by DBMS_SPM.load_plans_from_cursor_cache and not two plans. In this environment, there was much less data and slowness was not seen. I was assuming here too this procedure will load 2 plans but it loaded only one plan.
So I have this question: When we apply this to other higher environment, is it sure that DBMS_SPM.alter_sql_plan_baseline procedure will load both good and bad plan? My intention here is this: I know exactly what is the good plan and want to fix it. However is there a chance that DBMS_SPM.load_plans_from_cursor_cache may not even load that plan when we try to deploy this into the higher environment?
Thank you.
Note: I wanted to post this in the 'General Database Questions" space, but it looks like something changed? I am not seeing the usual UI, so forum admins, can you please help and put this question into the 'General Database Questions" space?