oracle中Cardinality Feedback与_optimizer_use_feedback的使用建议

该参数与Cardinality Feedback特性有关,优化器可以估算基数不正确的原因有很多，如缺少的统计信息，不准确的统计数据，或复杂的谓词,基数统计反馈有助于优化器生成更合理的执行计划.对于此特性我不作科普了,比较详细的资料可以参考以下文档:

1.Tuning-by-Cardinality-Feedback.pdf
2.Statistics (Cardinality) Feedback – Frequently Asked Questions (文档 ID 1344937.1)

该特性其实从10g版本就已经开始引入,只是它的应用受限于optimizer_dynamic_sampling参数,在11g以前的10g版本中,只有在optimizer_dynamic_sampling=4以上的情况下才会被用到该特性.这个在10g版本中可以通过HINT语法
/*+ dynamic_sampling (customer 4) */或者在session/system中将optimizer_dynamic_sampling设置为4进行测试.

判断SQL是否使用了CF可以通过sql的执行计划中的note提示或者视图V$SQL_SHARED_CURSOR的USE_FEEDBACK_STATS字段进行.

执行计划中的note提示

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - filter(ROWNUM>0)
 8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
- Cardinality Feedback used for this statement
V$SQL_SHARED_CURSOR

column use_feedback_stats format a18
column sql_text format a80

select c.child_number, c.use_feedback_stats , s.sql_text from v$sql_shared_cursor c,v$sql s
where s.sql_id=c.sql_id and c.sql_id = 'an4zdfz0h7513'
and s.child_number= c.child_number;

CHILD_NUMBER USE_FEEDBACK_STATS SQL_TEXT
------------ ------------------ ------------------------------------------------------------
           0 Y                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t
           1 N                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t

对使用了基数统计回馈特性的sql,可以在10053的trace文件中发现sql被转化成带了hint opt_estimate的语法.
CF特性的功能是非常不错的,oracle在使用了cf反馈评估后更准确的统计数据后会根据此数据生成对于的plan,在再次的执行中还会持续评估统计信息是否准确,以此重复解析,参考如下:

When a cursor is found to be a candidate for Statistics Feedback it will be hard parsed again using the new estimates. The child cursor will be marked as not being shareable and USE_FEEDBACK_STATS set to ‘Y’ in V$SQL_SHARED_CURSOR.
Note: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

At the next execution, as a result of the cursor being marked as not shareable, a hard parse will again be performed and a new child created with the optimizer having used the new estimates for creating an optimizer plan.

但是由于cf的评估结果数据只存在内存中(重启之后就需要重新来过),在session之间是不可共用的,并且由于在11g中存在了过多的bug,常见的问题就是在第二次执行sql时候性能下降较多.因此我在11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭.

部分bug list