It seems like there are a number of things going on here. So, I'd like to tackle them one by one.
First you have a complex SQL statement where the initial cardinality estimates are not accurate, so cardinality feedback is kicking in and changing the execution plan for subsequent executions. However, the execution plan determined via cardinality feedback is sub-optimal.
In Oracle Database 11g Cardinality Feedback is limited to just to individual index/table cardinalities and group by estimates. It's possible that correcting only these estimates may not result in a better plan if the issue is in fact the join order or the join methods chosen.
Since the statement is only monitored on the initial execution it's not possible to continue to improve the plan over subsequent execution as it is in 12c. Therefore I would recommend that you don't use cardinality feedback for this statement.
The question then becomes, how should I prevent cardinality feedback happening for this statement?
As you rightly pointed out you have a number of options.
1. You can disable cardinality feedback altogether by setting the underscore parameter "_optimizer_use_feedback" to false at a session or statement level. Is it safe to do this? Yes. However, I wouldn't disable it at the system level on less you have proof that it is doing more harm then good.
Personally, I prefer to change a parameter at lowest level possible. So I would use the opt_param hint to disable cardinality feedback just for this statement as follows: /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */. Given it’s not always possible to add a hint that leads me to option 2.
2. Use SQL Plan Management to ensure the Optimizer doesn’t use the cardinality feedback plan. You have two different ways to do this:
a. Capture the initial plan you get on hard parse
b. Capture the plan you get when you manually add the hint opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE')
You mentioned that your initial attempt to use SQL Plan Management was unsuccessful, which can be true for a number of reasons. Based on your description you chose to go with option A above, capture the initial plan. From the output you provided from dba_sql_plan_baselines, we can see that the plan you captured is both enabled and accepted.
SQL_HANDLE PLAN_NAME ACC ENA COST
------------------------ ------------------------------ --- --- ---------
SQL_540fb108407c78ed SQL_PLAN_583xj1107sy7db3ec76e2 YES YES 33095
SQL_540fb108407c78ed SQL_PLAN_583xj1107sy7dc81ab709 NO YES 195016
So, why wasn’t it used? Unfortunately this is harder to tell from the information you provided. More details on how the optimizer matches SQL statements to SQL Plan Baselines is available in this blog post
https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management Possible causes include the initialization parameter OPTIMIZER_USE_SQL_PLAN_BASELINES was set to FALSE (non-default), the parsing schema is different to the one that created the baseline or more probably the plan captured could not be reproduced. The only way to confirm if a plan is reproducible or not is to look at the optimizer trace for the statement. You will need search within the trace file for acronym SPM. This section of the trace will tell you if the optimizer did indeed associate the plan you captured with your SQL statement and why it couldn’t reproduce it.
You can also capture the plan generated by the hinted SQL statement and store it in the baseline for the non-hinted SQL statement by following the instructions outlined in this blog post
https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex You also suggested using a SQL Profile as an alternative approach. A SQL Profile contains auxiliary information that when used together with object and system statistics, enables the optimizer to minimize cardinality estimate mistakes. Since a SQL profile does not contain a complete set of hints to reproduce an exact execution plan, there is no guarantee it will continue to generate the plan you want as the underlying object statistics change. Therefore I would recommend a SQL Plan Baseline over a SQL Profile.