關於oracle11g的關於cardinality feedback新特性
現象描述：
前天下午，同事告訴我他有個查詢的sql特別奇怪，第一遍查詢1秒左右能出來，不改任何地方，再次查詢卻需要100秒左右，隨便修改一點條件，哪怕換個註釋，再查又是1秒能查出來，再次執行還是100秒左右才出來。
聽到這個現象後，我驗證了確實存在這個現象，檢視資料庫的一切狀態，指標都正常，此時對查詢的表也沒有什麼操作。還是來看看執行計劃情況吧。
問題分析：
通過set autotrace on來檢視執行計劃情況：
第一遍執行時的執行計劃：
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 3707 (1)| 00:00:45 | | |
| 1 | SORT AGGREGATE | | 1 | 58 | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 58 | 3707 (1)| 00:00:45 | | |
| 3 | PARTITION RANGE ITERATOR | | 8 | 272 | 141 (0)| 00:00:02 | 1 | 30 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_MAIL_CLCT | 8 | 272 | 141 (0)| 00:00:02 | 1 | 30 |
|* 5 | INDEX RANGE SCAN | INDEX_SENDER_POST_CODE | 276 | | 62 (0)| 00:00:01 | 1 | 30 |
| 6 | PARTITION RANGE AND | | 577K| 13M| 3707 (1)| 00:00:45 |KEY(AP)|KEY(AP)|
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_DLV | 577K| 13M| 3707 (1)| 00:00:45 |KEY(AP)|KEY(AP)|
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 9 | BITMAP INDEX RANGE SCAN | P_TB_EVT_DLV | | | | |KEY(AP)|KEY(AP)|
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Statistics
----------------------------------------------------------
208 recursive calls
0 db block gets
455962 consistent gets
2 physical reads
0 redo size
537 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
第二遍執行的執行計劃：
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 3707 (1)| 00:00:45 | | |
| 1 | SORT AGGREGATE | | 1 | 58 | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 58 | 3707 (1)| 00:00:45 | | |
| 3 | PARTITION RANGE ITERATOR | | 8 | 272 | 141 (0)| 00:00:02 | 1 | 30 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_MAIL_CLCT | 8 | 272 | 141 (0)| 00:00:02 | 1 | 30 |
|* 5 | INDEX RANGE SCAN | INDEX_SENDER_POST_CODE | 276 | | 62 (0)| 00:00:01 | 1 | 30 |
| 6 | PARTITION RANGE AND | | 577K| 13M| 3707 (1)| 00:00:45 |KEY(AP)|KEY(AP)|
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_DLV | 577K| 13M| 3707 (1)| 00:00:45 |KEY(AP)|KEY(AP)|
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 9 | BITMAP INDEX RANGE SCAN | P_TB_EVT_DLV | | | | |KEY(AP)|KEY(AP)|
-------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
9 recursive calls
1 db block gets
3307979 consistent gets
3266357 physical reads
176 redo size
537 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> SQL>
執行計劃一樣，但是發現第二遍的邏輯讀和物理讀比第一次大了非常多，但是為什麼會導致邏輯讀和物理讀多那麼多呢？是不是這樣查的執行計劃不準。用dbms_xplan.display_cursor看看。
第一次執行的執行計劃，也就是快的那次：SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8940hwpvjskxc',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8940hwpvjskxc, child number 0
-------------------------------------
Plan hash value: 1825533607
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2231 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 58 | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 58 | 2231 (1)| 00:00:27 | | |
| 3 | PARTITION RANGE ITERATOR | | 5 | 170 | 115 (0)| 00:00:02 | 15 | 31 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_MAIL_CLCT | 5 | 170 | 115 (0)| 00:00:02 | 15 | 31 |
|* 5 | INDEX RANGE SCAN | INDEX_SENDER_POST_CODE | 276 | | 36 (0)| 00:00:01 | 15 | 31 |
| 6 | PARTITION RANGE AND | | 440K| 10M| 2231 (1)| 00:00:27 |KEY(AP)|KEY(AP)|
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_DLV | 440K| 10M| 2231 (1)| 00:00:27 |KEY(AP)|KEY(AP)|
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 9 | BITMAP INDEX RANGE SCAN | P_TB_EVT_DLV | | | | |KEY(AP)|KEY(AP)|
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
39 rows selected.
DongCS 23:45:13
第二次執行，慢的那次：
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8940hwpvjskxc',1));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8940hwpvjskxc, child number 1
-------------------------------------
Plan hash value: 2676587892
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 698K(100)| | | |
| 1 | SORT AGGREGATE | | 1 | 58 | | | | |
|* 2 | HASH JOIN SEMI | | 223 | 12934 | 698K (1)| 02:19:46 | | |
| 3 | PARTITION RANGE ITERATOR | | 23962 | 795K| 5183 (1)| 00:01:03 | 15 | 31 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_EVT_MAIL_CLCT | 23962 | 795K| 5183 (1)| 00:01:03 | 15 | 31 |
|* 5 | INDEX RANGE SCAN | INDEX_SENDER_POST_CODE | 17841 | | 127 (0)| 00:00:02 | 15 | 31 |
| 6 | PARTITION RANGE ITERATOR | | 47M| 1085M| 693K (1)| 02:18:41 | 107 | 306 |
|* 7 | TABLE ACCESS FULL | TB_EVT_DLV | 47M| 1085M| 693K (1)| 02:18:41 | 107 | 306 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Note
-----
- cardinality feedback used for this statement
39 rows selected.
這次查處的執行計劃果然不一樣，TB_EVT_DLV是全表掃描，最後還多了一個cardinality feedback的註釋！
在metalink上查詢cardinality feedback相關資訊，發現cardinality feedback是oracle11g的一個新特性：
Cardinality feedback monitoring may be enabled in the following cases:
Tables with no statistics, multiple conjunctive or disjunctive filter predicates on a table, and predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for.
In some cases, there are other techniques available to improve estimation; for instance, dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. In cases where these techniques apply, cardinality feedback is not enabled.
However, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback.
對應的有個隱含引數：_optimizer_use_feedback，按照metalink上相關文件提供的資訊，嘗試將這個引數設定為false。
alter system set "_optimizer_use_feedback"=false scope=both;
再來檢視上面的那個sql，發現現在無論查詢多少次都是在1秒左右能查出。看來這個是oracle11g cardinality feedback的bug。將這個新特性關掉就ok了。
參考文件：
Cardinality Feedback - Frequently Asked Questions [ID 1344937.1]
Bug 8521689 - SubOptimal execution plan on second execution of GROUP BY query [ID 8521689.8]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12129601/viewspace-719736/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11g SQL效能的新特性（二）- Cardinality FeedbackOracleSQL
- 【DBA】Oracle 11g 針對SQL效能的新特性（二）- Cardinality FeedbackOracleSQL
- 2.2.1 關於CDB的特性
- Oracle Database Cardinality FeedbackOracleDatabase
- 關於C++14：你需要知道的新特性C++
- Oracle 11gr2 新技術 Cardinality FeedbackOracle
- 關於oracle11g的審計功能Oracle
- 關於Spring Cloud的核心特性SpringCloud
- 關於Mysql5.7高版本group by新特性報錯MySql
- 基數反饋（Cardinality Feedback）
- 那些語句使用cardinality feedback
- Cardinality Feedback基數反饋
- 關於新框架的學習框架
- MySQL：關於ICP特性的說明（未完）MySql
- 關於php面向的特性之封裝PHP封裝
- Oracle 12C 新特性：關於歸檔日誌的備份Oracle
- 關於 Angular HttpClient 的單例特性的思考AngularHTTPclient單例
- 關於文字特性的一些設定
- 關於IT，關於技術
- 關於 Roguelike 的探討，及基於 Roguelike 的新框架框架
- 關於oracle11g RAC 監聽器問題Oracle
- 關於DVCS、持續整合和特性分支
- 關於C# 中的Attribute 特性 經典C#
- 關於AI、關於chatGPT的幾十種用法AIChatGPT
- 關於 Go1.14，你一定想知道的效能提升與新特性Go
- MySQL8.0 · 引擎特性 · 關於undo表空間的一些新變化MySql
- 關於~
- 關於
- 關於語義類標籤的新理解
- 關於新書出版的一些想法新書
- 關於 Angular HTTP Interceptor 中 Request 和 Response 的 immutable 特性AngularHTTP
- 關於Spring Cloud的特性與層次結構SpringCloud
- 關於CMP的關係，求教...
- 關於Java你不知道的那些事之Java8新特性[HashMap優化]JavaHashMap優化
- 【新炬網路名師大講堂】關於Oracle 12c Flex ASM特性的理解OracleFlexASM
- Laravel 7.2.2 關於新的日期序列化格式Laravel
- 關於一個新的DW專案的認知
- 關於RedisRedis