Cardinality Feedback是11GR2出現的一個新特性，它的出現是為了幫助ORACLE優化器依據更精準的Cardinality 產生出更加優秀的執行計劃。Cardinality基數的評估準確與否，對於優化器異常重要，直接影響到後續的JOIN COST等重要的成本計算評估，如果評估不當會造成CBO選擇不當的執行計劃。此技術對於僅執行一次SQL無效，在SQL第一次執行時，記錄儲存實際的Cardinality 和評估的Cardinality之間的差異，如果差異較大，在第二次執行時，優化器會依據實際的Cardinality 重新決策生成執行計劃，但是需要注意的是，當使用更準確的Cardinality重新生成執行計劃時，不一定生成的執行計劃與第一次時不一樣，完全有可能是相同的。這個技術的出現是由於優化器在一些情況下不能很好的去計算Cardinality的數值，比如：統計資訊缺失或陳舊、多謂詞、直方圖缺失或者缺少直方圖等等，在這些情況下，Cardinality Feedback可能會幫上忙。

我們來看下Cardinality Feedback是如何發揮作用的。注意使用普通使用者來測試Cardinality Feedback，sys使用者被預設禁用該特性

select product_name
from order_items o, product_information p
where o.unit_price = 15 and quantity > 1
and p.product_id = o.product_id;

在order_items表上有2個謂詞，o.unit_price = 15 and quantity > 1，由於優化器對於聯合謂詞評估不準，導致ORACLE優化器認為經過2個聯合謂詞的過濾，order_items表只返回一條記錄，進而優化器選擇了nest loop的執行計劃。

如果Cardinality Feedback被開啟，在SQL第一次執行結束後，ORACLE會把實際的Cardinality與評估的Cardinality做比較，如果差異較大，這些實際的Cardinality會被儲存以期待SQL再次被執行時這些Cardinality被優化器所使用。我們看看第二次執行的情況：

發現ORACLE 優化器依據第一次執行所記錄的Cardinality，重新評估執行計劃，在第二次執行時，已經選擇了HASH JOIN的執行計劃，在執行計劃的NOTE部分也看到了cardinality feedback used for this statement字樣。11GR2針對此特性，也專門在V$SQL_SHARED_CURSOR中增加了 USE_FEEDBACK_STATS列來記錄SQL是否使用了Cardinality Feedback。
如目前我所負責的一個資料庫中，有521個SQL都使用到了Cardinality Feedback。
select count(*) FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS ='Y';

COUNT(*)
----------
521

通過10053 trace兩次執行過程，可以很容易發現Cardinality Feedback是如何發揮作用的。在第一次執行時，儲存實際的Cardinality 資訊，在第二次執行時，通過hint OPT_ESTIMATE (TABLE “ORDER_ITEMS” ROWS=13.000000 )的方式來告訴優化器真實的基數資訊。優化器根據真實的基數資訊重新評估產生執行計劃。
Cardinality Feedback動能的開啟和關閉通過一個隱含引數_optimizer_use_feedback來控制。此引數可以在session 和 system級別進行設定。

SQL> alter session set “_optimizer_use_feedback”=false;

會話已更改。

system級別

SQL> alter system set “_optimizer_use_feedback”=false;

系統已更改。

還可以在SQL語句級進行開啟和關閉。
select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */ count(*) from test;
select /*+ opt_param(‘_optimizer_use_feedback’ ‘true’) */ count(*) from test;

Cardinality feedback的資訊將存放在cursor中，當Cursor一旦被aged out則會丟失。如果已經採用了Cardinality Feedback的SQL被刷出共享池，那麼在SQL下一次執行的時候，還是會依據表的原始的統計資訊來生成執行計劃，在第二次執行的時候參考第一次執行時的Cardinality 重新生成執行計劃，如此反覆。因此，如果發現一個SQL的執行效能經常反覆，這個SQL可能是使用了Cardinality Feedback，有必要搞清楚是哪裡出現了問題，比如是不是由於統計資訊陳舊導致優化器評估了錯誤的基數進而導致選擇了錯誤的驅動表。
如下一個例子，a表上status='ccc'的值有15355個，由於統計資訊陳舊導致了優化器評估只有1個符合條件。
select count(*) from a a1,a a2 where a1.object_id=a2.object_id and
a1.object_name='c' and a1.object_type>'O' and a2.status='ccc'

Plan hash value: 370690737

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21965 (100)| |
| 1 | SORT AGGREGATE | | 1 | 43 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 43 | 21965 (1)| 00:04:24 |
|* 4 | TABLE ACCESS FULL | A | 1 | 12 | 21962 (1)| 00:04:24 |
|* 5 | INDEX RANGE SCAN | TT | 65 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| A | 1 | 31 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A2"."STATUS"='ccc')
5 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
6 - filter(("A1"."OBJECT_NAME"='c' AND "A1"."OBJECT_TYPE">'O'))

第二次執行時，優化器已經使用了Cardinality Feedback，已經選用了正確的驅動表：

Plan hash value: 370690737
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22045 (100)| |
| 1 | SORT AGGREGATE | | 1 | 43 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 47 | 2021 | 22045 (1)| 00:04:25 |
|* 4 | TABLE ACCESS FULL | A | 30 | 930 | 21955 (1)| 00:04:24 |
|* 5 | INDEX RANGE SCAN | TT | 65 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| A | 2 | 24 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("A1"."OBJECT_NAME"='c' AND "A1"."OBJECT_TYPE">'O'))
5 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
6 - filter("A2"."STATUS"='ccc')
Note
-----
- cardinality feedback used for this statement

把SQL刷出共享池，看看再一次執行，執行計劃會是什麼：
select address,hash_value,executions,parse_calls from v$sqlarea where sql_id='7s9av17u9k6f5';

ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
00000004A34485C8 4103674309 2 2

1 row selected.

exec dbms_shared_pool.purge('00000004A34485C8,4103674309','C');

select count(*) from a a1,a a2 where a1.object_id=a2.object_id and
a1.object_name='c' and a1.object_type>'O' and a2.status='ccc'

Plan hash value: 370690737
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21965 (100)| |
| 1 | SORT AGGREGATE | | 1 | 43 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 43 | 21965 (1)| 00:04:24 |
|* 4 | TABLE ACCESS FULL | A | 1 | 12 | 21962 (1)| 00:04:24 |
|* 5 | INDEX RANGE SCAN | TT | 65 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| A | 1 | 31 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A2"."STATUS"='ccc')
5 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
6 - filter(("A1"."OBJECT_NAME"='c' AND "A1"."OBJECT_TYPE">'O'))

我們看到執行計劃又返回到了第一次執行時的狀態，選用了錯誤的驅動表，再次執行，發現又使用了cardinality feedback：

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22045 (100)| |
| 1 | SORT AGGREGATE | | 1 | 43 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 47 | 2021 | 22045 (1)| 00:04:25 |
|* 4 | TABLE ACCESS FULL | A | 30 | 930 | 21955 (1)| 00:04:24 |
|* 5 | INDEX RANGE SCAN | TT | 65 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| A | 2 | 24 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("A1"."OBJECT_NAME"='c' AND "A1"."OBJECT_TYPE">'O'))
5 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
6 - filter("A2"."STATUS"='ccc')
Note
-----
- cardinality feedback used for this statement

cardinality feedback技術給優化器提供了更加精準的cardinality來幫助優化器更容易產生優秀的執行計劃，ORACLE裡也有其他的一些技術來提升評估cardinality的精準性，如：dynamic sampling，多列聯合的統計資訊收集等，如果採用了這些技術的執行計劃，cardinality feedback技術將不再被採用。

已知的cardinatilty feedback問題
Fixed in 11.2.0.2
Note 8608703.8 Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback
Note 9465425.8 Bug 9465425 - New cursors generated after cardinality feedback
Note 9342979.8 Bug 9342979 Suboptimal plan change with cardinatilty feedback

Fixed in 12g
Note 8521689.8 Bug 8521689 - SubOptimal execution plan on second execution of GROUP BY query
Note 8729064.8 Bug 8729064 Adaptive cursor sharing fails to share / USE_FEEDBACK_STATS not set

【11g新特性】Cardinality Feedback基數反饋

http://www.askmaclean.com/archives/11g-new-feature-cardinality-feedback.html

https://blogs.oracle.com/optimizer/entry/cardinality_feedback

http://www.dba-oracle.com/t_cardinality_feedback.htm

http://kerryosborne.oracle-guy.com/2011/07/cardinality-feedback/

http://oradbastuff.blogspot.com/2012/07/cardinality-feedback-nasty-feature-in.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1216002/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 11gr2 新技術 Cardinality Feedback

【11g新特性】Cardinality Feedback基數反饋

相關文章