【DB笔试面试642】在Oracle中，什么是基数反馈（Cardinality Feedback）？
- 2019 年 10 月 10 日
- 筆記
题目部分
在Oracle中，什么是基数反馈（Cardinality Feedback）？
♣
答案部分
基数反馈（Cardinality Feedback，CFB）是Oracle 11gR2出现的一个新特性，它的出现是为了帮助Oracle优化器依据更精准的基数生成更加优秀的执行计划。基数的评估准确与否，对于优化器异常重要，直接影响到后续的JOIN COST等重要的成本计算评估。若评估不当则会造成CBO选择不当的执行计划。此技术对于仅执行一次的SQL无效，在SQL第一次执行时，记录存储实际的基数和评估的基数之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的基数重新决策生成执行计划，但是需要注意的是，当使用更准确的基数重新生成执行计划时，生成的执行计划与第一次时使用的执行计划完全有可能是相同的。这个技术的出现是由于优化器在一些情况下不能很好的去计算基数的数值，比如：统计信息缺失或陈旧、多谓词、直方图缺失等等。
Oracle只针对下面情况开启CFB：
① 没有收集表的统计信息，并且动态采样（Dynamic Sampling）也没有开启。
② 查询条件复杂（比如条件有函数）或者涉及多列，但却没有收集扩展的统计信息（Extended Statistics）。
在这几种情况下，CBO是无法估算出准确的Cardinality的。针对上述情况，Oracle会监控操作的实际行数（A-Row），然后对比CBO估算的行数（E-Row）。如果两个值相差很大，那么就记录实际行数（A-Row），做上标记。下次执行时再次进行硬解析，根据实际行数来重新生成执行计划。如果两个值相差不大，那么CBO就不再监控这条SQL语句。
Oracle 11gR2针对此特性，也专门在V$SQL_SHARED_CURSOR中增加了USE_FEEDBACK_STATS列来记录SQL是否使用了基数反馈。基数反馈的开启和关闭通过一个隐含参数“_OPTIMIZER_USE_FEEDBACK”来控制，该参数默认为TRUE，表示开启技术反馈特性。此参数除了可以在SESSION和SYSTEM级别进行设置之外，还可以在SQL语句级使用Hint进行开启和关闭，如下所示：
SELECT /*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'FALSE') */ COUNT(*) FROM TEST; SELECT /*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'TRUE') */ COUNT(*) FROM TEST;
需要注意的是，如果动态采样被启用，那么是不会使用基数反馈特性的。若使用了该特性则在执行计划的Note部分可以看到“cardinality feedback used for this statement”字样。基数反馈在Oracle 12c上得到更进一步的扩展称为统计反馈（Statistics Feedback），并且成为Oracle 12c自动重新优化（Automatic Reoptimization）的一部分。但是由于CFB的评估结果数据只存在内存中（重启之后就会丢失），在会话之间是不可共用的，并且由于在Oracle 11g中存在过多的Bug，常见的问题就是在第二次执行SQL时候性能下降很多。因此在Oracle 11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭。
下面给出基数反馈的一个示例：
SYS@orclasm > set pagesize 9999 SYS@orclasm > set line 9999 SYS@orclasm > col NAME format a40 SYS@orclasm > col KSPPDESC format a50 SYS@orclasm > col KSPPSTVL format a20 SYS@orclasm > SELECT a.INDX, 2 a.KSPPINM NAME, 3 a.KSPPDESC, 4 b.KSPPSTVL 5 FROM x$ksppi a, 6 x$ksppcv b 7 WHERE a.INDX = b.INDX 8 and lower(a.KSPPINM) like lower('%¶meter%'); Enter value for parameter: _optimizer_use_feedback old 8: and lower(a.KSPPINM) like lower('%¶meter%') new 8: and lower(a.KSPPINM) like lower('%_optimizer_use_feedback%') INDX NAME KSPPDESC KSPPSTVL ---------- ---------------------------------------- -------------------------------------------------- -------------------- 2379 _optimizer_use_feedback optimizer use feedback TRUE SYS@orclasm > CREATE TABLE T_CFB_20170602_LHR AS SELECT * FROM DBA_OBJECTS; Table created. SYS@orclasm > SET AUTOT TRACEONLY SYS@orclasm> SELECT /*+ DYNAMIC_SAMPLING(T 0)*/ * FROM T_CFB_20170602_LHR T WHERE OWNER='SCOTT'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2594166763 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 912 | 184K| 305 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T_CFB_20170602_LHR | 912 | 184K| 305 (1)| 00:00:04 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SCOTT') Statistics ---------------------------------------------------------- 19 recursive calls 0 db block gets 1150 consistent gets 1116 physical reads 0 redo size 1521 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed SYS@orclasm> SELECT /*+ DYNAMIC_SAMPLING(T 0)*/ * FROM T_CFB_20170602_LHR T WHERE OWNER='SCOTT'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2594166763 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 912 | 184K| 305 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T_CFB_20170602_LHR | 912 | 184K| 305 (1)| 00:00:04 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SCOTT') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1120 consistent gets 1116 physical reads 0 redo size 1521 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed SYS@orclasm> SYS@orclasm> SET AUTOT OFF SYS@orclasm> SELECT A.SQL_ID, A.VERSION_COUNT FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT /*+ DYNAMIC_SAMPLING(T 0)*/ * FROM T_CFB_20170602_LHR T WHERE OWNER=%'; SQL_ID VERSION_COUNT ------------- ------------- 1a8r06gu4utsm 2 SYS@orclasm> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1a8r06gu4utsm',0,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- SQL_ID 1a8r06gu4utsm, child number 0 ------------------------------------- SELECT /*+ DYNAMIC_SAMPLING(T 0)*/ * FROM T_CFB_20170602_LHR T WHERE OWNER='SCOTT' Plan hash value: 2594166763 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 305 (100)| | |* 1 | TABLE ACCESS FULL| T_CFB_20170602_LHR | 912 | 184K| 305 (1)| 00:00:04 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SCOTT') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128], "T"."SUBOBJECT_NAME"[VARCHAR2,30], "T"."OBJECT_ID"[NUMBER,22], "T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19], "T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7], "T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7], "T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1], "T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22], "T"."EDITION_NAME"[VARCHAR2,30] 50 rows selected. SYS@orclasm> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1a8r06gu4utsm',1,'advanced')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- SQL_ID 1a8r06gu4utsm, child number 1 ------------------------------------- SELECT /*+ DYNAMIC_SAMPLING(T 0)*/ * FROM T_CFB_20170602_LHR T WHERE OWNER='SCOTT' Plan hash value: 2594166763 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 305 (100)| | |* 1 | TABLE ACCESS FULL| T_CFB_20170602_LHR | 6 | 1242 | 305 (1)| 00:00:04 | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SCOTT') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128], "T"."SUBOBJECT_NAME"[VARCHAR2,30], "T"."OBJECT_ID"[NUMBER,22], "T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19], "T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7], "T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7], "T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1], "T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22], "T"."EDITION_NAME"[VARCHAR2,30] Note ----- - cardinality feedback used for this statement 54 rows selected. SYS@orclasm> SYS@orclasm> SELECT A.CHILD_NUMBER,A.USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR A WHERE A.SQL_ID='1a8r06gu4utsm'; CHILD_NUMBER USE_FEEDBACK_STATS ------------ ------------------- 0 Y 1 N
& 说明：
有关基数反馈的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140200/
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
