I ran into a case today where I had tuned a query by putting a cardinality hint into a view, but the optimizer changed the plan anyway by overriding the cardinality hint with cardinality feedback. So, I found out how to turn cardinality feedback off in case you want the cardinality hint to stick. I built a simple testcase for this post.
Here is the test query:
select /*+ cardinality(test,1) */ count(*) from test;
The first time it runs the plan shows that the optimizer thinks there is one row in the test table:
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 292 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST | 1 | 292 (1)| 00:00:04 | -------------------------------------------------------------------
But the second time cardinality feedback tells the optimizer the truth:
------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 292 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST | 31467 | 292 (1)| 00:00:04 | ------------------------------------------------------------------- Note ----- - cardinality feedback used for this statement
How annoying! It ignored my cardinality hint. But you can add this hint to turn off cardinality feedback:
opt_param('_optimizer_use_feedback' 'false')
and then you are back to the original plan with rows = 1. This doesn’t prove that this will help improve performance just that the plan will show the cardinality I’m trying to make it use.
– Bobby
