[20130305]Cardinality Feedback on 11gR2.txt
[20130305]Cardinality Feedback on 11gR2.txt
http://blogs.oracle.com/optimizer/entry/cardinality_feedback
Cardinality Feedback是11G的新特性,它可以自動調整執行計劃.但是具體如何實現的呢?按照上面的連結的例子做一些測試看看.
1.測試環境以及問題提出:
可以發現生成了新的子游標,執行計劃發生了變化.
--看不到任何原因,從v$sql_shared_cursor檢視.
--從10053跟蹤看看,因為按照上面的執行方式第2次執行相當於有進行了1次硬分析,跟蹤檔案應該有所記錄.否則不能使用10053來跟蹤分
--析執行計劃.
2.10053分析:
SQL> alter system flush shared_pool;
System altered.
--先執行1次上述sql語句.
SQL> alter session set events '10053 trace name context forever, level 12';
--再執行上述sql語句.
SQL> alter session set events '10053 trace name context off';
3.檢查跟蹤檔案:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (TABLE "P" MIN=13.000000 ) OPT_ESTIMATE (INDEX_SCAN "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 )
OPT_ESTIMATE (INDEX_FILTER "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE (TABLE "O" ROWS=13.000000 ) */
"P"."PRODUCT_NAME" "PRODUCT_NAME" FROM "OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE "O"."UNIT_PRICE"=15
AND "O"."QUANTITY">1 AND "P"."PRODUCT_ID"="O"."PRODUCT_ID";
--最終可以發現語句有點像sql profile那樣加入特定返回資訊的的提示,來控制執行計劃.
--如果拿跟蹤的sql語句直接執行,可以發現執行計劃與上述的第2次執行使用cardinality feedback的一致.
3.總結:
cardinality feedback採用sql profile相似的東西,在估計與實際的返回行差距很大的情況下,透過提示來選擇更優的執行計劃.
http://blogs.oracle.com/optimizer/entry/cardinality_feedback
In Oracle Database 11gR2, cardinality feedback monitors and feeds back the following kinds of cardinalities:
Single table cardinality (after filter predicates are applied)
Index cardinality (after index filters are applied)
Cardinality produced by a group by or distinct operator
http://blogs.oracle.com/optimizer/entry/cardinality_feedback
Cardinality Feedback是11G的新特性,它可以自動調整執行計劃.但是具體如何實現的呢?按照上面的連結的例子做一些測試看看.
1.測試環境以及問題提出:
SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SELECT /*+ gather_plan_statistics */
product_name
FROM order_items o, product_information p
WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fas8yuqm8xqk9, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ product_name FROM
order_items o, product_information p WHERE o.unit_price = 15 AND
quantity > 1 AND p.product_id = o.product_id
Plan hash value: 1255158658
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 13 |00:00:00.01 | 25 |
| 1 | NESTED LOOPS | | 1 | | | 13 |00:00:00.01 | 25 |
| 2 | NESTED LOOPS | | 1 | 4 | 7 (0)| 13 |00:00:00.01 | 12 |
|* 3 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 4 | 3 (0)| 13 |00:00:00.01 | 8 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 13 | 1 | 0 (0)| 13 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 13 | 1 | 1 (0)| 13 |00:00:00.01 | 13 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")--看ID=5,可以發現A-ROWS=13,而估計僅僅E-Rows=1,存在很大的差異! 第2次執行,情況如何呢?
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fas8yuqm8xqk9, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ product_name FROM
order_items o, product_information p WHERE o.unit_price = 15 AND
quantity > 1 AND p.product_id = o.product_id
Plan hash value: 1553478007
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 (100)| 13 |00:00:00.01 | 24 | | | |
|* 1 | HASH JOIN | | 1 | 13 | 9 (12)| 13 |00:00:00.01 | 24 | 1452K| 1452K| 430K (0)|
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 1 | 13 | 3 (0)| 13 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 1 | 288 | 5 (0)| 288 |00:00:00.01 | 17 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
Note
-----
- cardinality feedback used for this statement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
可以發現生成了新的子游標,執行計劃發生了變化.
SQL> @ share fas8yuqm8xqk9
SQL_TEXT = SELECT /*+ gather_plan_statistics */ ....
AND p.product_id = o.product_id
SQL_ID = fas8yuqm8xqk9
ADDRESS = 00000000BE387280
CHILD_ADDRESS = 00000000BE9644A8
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = SELECT /*+ gather_plan_statistics */ ....
AND p.product_id = o.product_id
SQL_ID = fas8yuqm8xqk9
ADDRESS = 00000000BE387280
CHILD_ADDRESS = 00000000BE50C868
CHILD_NUMBER = 1
--------------------------------------------------
PL/SQL procedure successfully completed.
--看不到任何原因,從v$sql_shared_cursor檢視.
--從10053跟蹤看看,因為按照上面的執行方式第2次執行相當於有進行了1次硬分析,跟蹤檔案應該有所記錄.否則不能使用10053來跟蹤分
--析執行計劃.
2.10053分析:
SQL> alter system flush shared_pool;
System altered.
--先執行1次上述sql語句.
SQL> alter session set events '10053 trace name context forever, level 12';
--再執行上述sql語句.
SQL> alter session set events '10053 trace name context off';
3.檢查跟蹤檔案:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (TABLE "P" MIN=13.000000 ) OPT_ESTIMATE (INDEX_SCAN "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 )
OPT_ESTIMATE (INDEX_FILTER "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE (TABLE "O" ROWS=13.000000 ) */
"P"."PRODUCT_NAME" "PRODUCT_NAME" FROM "OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE "O"."UNIT_PRICE"=15
AND "O"."QUANTITY">1 AND "P"."PRODUCT_ID"="O"."PRODUCT_ID";
--最終可以發現語句有點像sql profile那樣加入特定返回資訊的的提示,來控制執行計劃.
--如果拿跟蹤的sql語句直接執行,可以發現執行計劃與上述的第2次執行使用cardinality feedback的一致.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7cy21d5hwyzhw, child number 0
-------------------------------------
SELECT /*+ OPT_ESTIMATE (TABLE "P" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_SCAN "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE
(INDEX_FILTER "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE
(TABLE "O" ROWS=13.000000 ) */ "P"."PRODUCT_NAME" "PRODUCT_NAME" FROM
"OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE
"O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1 AND
"P"."PRODUCT_ID"="O"."PRODUCT_ID"
Plan hash value: 1553478007
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 9 (100)| | | |
|* 1 | HASH JOIN | | 13 | 9 (12)| 1452K| 1452K| 399K (0)|
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 13 | 3 (0)| | | |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 288 | 5 (0)| | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
2 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
3.總結:
cardinality feedback採用sql profile相似的東西,在估計與實際的返回行差距很大的情況下,透過提示來選擇更優的執行計劃.
http://blogs.oracle.com/optimizer/entry/cardinality_feedback
In Oracle Database 11gR2, cardinality feedback monitors and feeds back the following kinds of cardinalities:
Single table cardinality (after filter predicates are applied)
Index cardinality (after index filters are applied)
Cardinality produced by a group by or distinct operator
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-755314/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Database Cardinality FeedbackOracleDatabase
- 基數反饋（Cardinality Feedback）
- 那些語句使用cardinality feedback
- Cardinality Feedback基數反饋
- Oracle 11gr2 新技術 Cardinality FeedbackOracle
- 聊聊Cardinality Feedback——一種CBO優化器嘗試優化
- Oracle 11g SQL效能的新特性（二）- Cardinality FeedbackOracleSQL
- 關於oracle11g的關於cardinality feedback新特性Oracle
- [20150706]11G cardinality feedback問題
- 【DBA】Oracle 11g 針對SQL效能的新特性（二）- Cardinality FeedbackOracleSQL
- [20150430]11G SPM與cardinality feedback2
- Cardinality
- dedecms /plus/feedback_ajax.php、/templets/feedback_main.htm、/templets/feedback_edit.htm XSS && SQL Injection VulPHPAISQL
- 7.40 CARDINALITY
- oracle 11g新特性Cardinality Feedback基數反饋造成同一sql幾乎同時執行產生不同的執行計劃OracleSQL
- Cardinality指什麼？
- Cardinality (222)
- Cardinality的計算
- 16.基數(Cardinality)
- CARDINALITY HINT用法小試
- dedecms /plus/feedback.php SQL Injection VulPHPSQL
- win10 怎麼開啟feedback-hub_win10系統開啟feedback-hub的方法Win10
- Are we ready for learned cardinality estimation?
- Win10怎麼解除安裝Feedback Hub應用程式_Win10解除安裝Feedback Hub的方法Win10
- Android OpenGL ES 系列連載：（07）Transform FeedbackAndroidORM
- 【論文筆記】 Denoising Implicit Feedback for Recommendation筆記
- zt:Cardinality (SQL statements) 最好的解釋SQL
- 簡單解析MySQL中的cardinality異常MySql
- 選擇率(selectivity)與基數(cardinality)
- oracle中執行計劃中的cardinalityOracle
- 高效的SQL（bitmap indexes optimize low cardinality columns）SQLIndex
- 【sql調優】cardinality測試與簡析SQL
- 蘋果Feedback是什麼？有什麼用？怎麼刪除？蘋果
- UVA 10892 LCM Cardinality (分解因數＋暴力)
- 【論文筆記】Leveraging Post-click Feedback for Content Recommendations筆記
- 【Coursera GenAI with LLM】 Week 3 Reinforcement Learning from Human Feedback Class NotesAI
- [20180928]exists與cardinality.txt
- oracle cardinality對於執行計劃的影響Oracle