Skip to Main Content
  • Questions
  • Performance issue - Cardinality Feedbacks

Breadcrumb

May 4th

Question and Answer

Maria Colgan

Thanks for the question, Swayamjit.

Asked: April 04, 2017 - 12:57 pm UTC

Last updated: April 06, 2017 - 7:01 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

DB version - 11.2.0.4
optimizer compatible - 11.2.0.4

-Query incorporates much subqueries and joins,in some tables data changes daily and in some monthly but stats are recent. 10-11 tables are incorporated where only one table contains around 400K rows, rest are miniature.
-Query contains all literals no bind variables.

Issue - Query performs good on the 1st run and returns result set in about 15secs but from subsequent runs the query keeps executing for more time( time is not constant though , varying between 30-50mins).

Preliminary Diagnosis -

On 1st run the Oracle runs it through the rough cardinality estimates (some 110K rows as estimate) but from subsequent run it improves the cardinality (about 1000rows) but the overall cost increases way to much ( evident from execution plans for both the queries).
Suspecting this as the issue.

Tried to create a baseline for the first run , so that the same could be used in subsequent executions but it generates a different baseline even after "optimizer_capture_sql_plan_baselines=FALSE" and on subsequent runs it ignores both the baselines and go after the cardinality feedback and gets stuck for more time.

SQL> select sql_handle,plan_name,accepted, enabled,optimizer_cost from dba_sql_plan_baselines where SQL_HANDLE='SQL_540fb108407c78ed';

SQL_HANDLE PLAN_NAME ACC ENA OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
SQL_540fb108407c78ed SQL_PLAN_583xj1107sy7db3ec76e2 YES YES 33095
SQL_540fb108407c78ed SQL_PLAN_583xj1107sy7dc81ab709 NO YES 195016

============================================

1. what more could you think of a possible issue here?
2. why Oracle is not going after the total cost of executing a query rather than cardinality feedback which is costing more for the particular execution?
3. does using "alter session set "_optimizer_use_feedback" = false;" poses any issues and is it advisable in this kind of scenario?
4. if we assign/force SQLprofile of the better execution plan of the query(ran from a different source) to all the subsequent executions of the particular query? will it pose some issue? whether it is good or bad practice.
5. If cardinality feedbacks are not always good then how this kind of situations can be avoided.should we force disable that every time?

and we said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.