[20150706]11G cardinality feedback問題
[20150706]11G cardinality feedback問題.txt
--今天做一個測試例子，第1次遇到cardinality feedback的問題
1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t1 as select rownum id1, trunc(rownum/10)+1 id2,cast('t1test' as varchar2(20)) pad1 from xmltable('1 to 100000');
create table t2 as select lpad(rownum,10,'0') id1, cast('t2test' as varchar2(20)) pad2 from xmltable('1 to 100000');
create table t3 as select rownum id1, trunc(dbms_random.value(0,100)) x1,trunc(dbms_random.value(0,200)) x2,cast('t3test' as varchar2(20)) pad3 from xmltable('1 to 100000');
create table t4 as select * from t3 where 1=2;
--t4是空表,主要目的這樣與生產系統資訊符合.沒有什麼其它意思.
--另外T2表欄位id1是字元型的，問題主要在這裡。
create unique index pk_t1 on t1 (ID1);
alter table t1 add constraint pk_t1 primary key (id1);
create index i_t1_id2 on t1 (ID2);
create index pk_t2 on t2 (to_number(ID1));
create index i_t3_id1 on t3 (ID1);
create index i_t4_id1 on t4 (ID1);
--分析表忽略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
create view v_t3t4 as select * from t3 union all select * from t4;
create or replace view v_tall as
select t1.id1,t1.id2,t1.pad1,t2.pad2,sum_x1,sum_x2
from t1,t2,
(select id1,sum(x1) sum_x1,sum(x2) sum_x2 from v_t3t4 group by id1) vx
where t1.id1=to_number(t2.id1) and t1.id1=vx.id1;
2.開始測試:
select * from v_tall where id2=42;
--執行計劃如下:
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 09qkq6x7ju4x2, child number 0
-------------------------------------
select * from v_tall where id2=42
Plan hash value: 431511531
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 53 (100)| | | | |
| 1 | HASH GROUP BY | | 10 | 700 | 53 (2)| 00:00:01 | 766K| 766K| 1220K (0)|
| 2 | NESTED LOOPS | | 10 | 700 | 52 (0)| 00:00:01 | | | |
| 3 | NESTED LOOPS | | 10 | 390 | 22 (0)| 00:00:01 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 160 | 2 (0)| 00:00:01 | | | |
|* 5 | INDEX RANGE SCAN | I_T1_ID2 | 10 | | 1 (0)| 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 23 | 2 (0)| 00:00:01 | | | |
|* 7 | INDEX RANGE SCAN | PK_T2 | 1 | | 1 (0)| 00:00:01 | | | |
| 8 | VIEW | V_T3T4 | 1 | 31 | 3 (0)| 00:00:01 | | | |
| 9 | UNION ALL PUSHED PREDICATE | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 12 | 2 (0)| 00:00:01 | | | |
|* 11 | INDEX RANGE SCAN | I_T3_ID1 | 1 | | 1 (0)| 00:00:01 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 39 | 1 (0)| 00:00:01 | | | |
|* 13 | INDEX RANGE SCAN | I_T4_ID1 | 1 | | 1 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$25E2CB83
4 - SEL$25E2CB83 / T1@SEL$2
5 - SEL$25E2CB83 / T1@SEL$2
6 - SEL$25E2CB83 / T2@SEL$2
7 - SEL$25E2CB83 / T2@SEL$2
8 - SET$5715CE2E / V_T3T4@SEL$3
9 - SET$5715CE2E
10 - SEL$8E13D68A / T3@SEL$4
11 - SEL$8E13D68A / T3@SEL$4
12 - SEL$9384AC1D / T4@SEL$5
13 - SEL$9384AC1D / T4@SEL$5
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$8E13D68A")
OUTLINE_LEAF(@"SEL$9384AC1D")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 3)
OUTLINE_LEAF(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$25E2CB83")
MERGE(@"SEL$3")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$25E2CB83" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$25E2CB83" "T2"@"SEL$2" "PK_T2")
NO_ACCESS(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
LEADING(@"SEL$25E2CB83" "T1"@"SEL$2" "T2"@"SEL$2" "V_T3T4"@"SEL$3")
USE_NL(@"SEL$25E2CB83" "T2"@"SEL$2")
USE_NL(@"SEL$25E2CB83" "V_T3T4"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$25E2CB83")
INDEX_RS_ASC(@"SEL$9384AC1D" "T4"@"SEL$5" ("T4"."ID1"))
INDEX_RS_ASC(@"SEL$8E13D68A" "T3"@"SEL$4" ("T3"."ID1"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID2"=42)
7 - access("T1"."ID1"="T2"."SYS_NC00003$")
11 - access("ID1"="T1"."ID1")
13 - access("ID1"="T1"."ID1")
--存在一個謂詞推入，很好的選擇執行計劃：
--PUSH_PRED(@"SEL$25E2CB83" "V_T3T4"@"SEL$3" 3)
3.而第2次執行：
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 09qkq6x7ju4x2, child number 1
-------------------------------------
select * from v_tall where id2=42
Plan hash value: 3589297038
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 119 (100)| | 10 |00:00:01.62 | 339 | 300 | 300 | | | | |
|* 1 | HASH JOIN | | 1 | 11 | 737 | 119 (2)| 00:00:01 | 10 |00:00:01.62 | 339 | 300 | 300 | 981K| 981K| 890K (0)| |
| 2 | NESTED LOOPS | | 1 | 10 | 280 | 22 (0)| 00:00:01 | 10 |00:00:00.01 | 12 | 0 | 0 | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 160 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 4 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 1 | 12 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 9 | 0 | 0 | | | | |
|* 6 | INDEX RANGE SCAN | PK_T2 | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 8 | 0 | 0 | | | | |
| 7 | VIEW | | 1 | 11 | 429 | 97 (3)| 00:00:01 | 100K|00:00:01.33 | 327 | 300 | 300 | | | | |
| 8 | HASH GROUP BY | | 1 | 11 | 341 | 97 (3)| 00:00:01 | 100K|00:00:01.13 | 327 | 300 | 300 | 6485K| 2233K| 3353K (1)| 3072 |
| 9 | VIEW | V_T3T4 | 1 | 11 | 341 | 96 (2)| 00:00:01 | 100K|00:00:00.70 | 327 | 0 | 0 | | | | |
| 10 | UNION-ALL | | 1 | | | | | 100K|00:00:00.50 | 327 | 0 | 0 | | | | |
| 11 | TABLE ACCESS FULL | T3 | 1 | 10 | 120 | 94 (2)| 00:00:01 | 100K|00:00:00.10 | 327 | 0 | 0 | | | | |
| 12 | TABLE ACCESS FULL | T4 | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / T1@SEL$2
4 - SEL$F5BB74E1 / T1@SEL$2
5 - SEL$F5BB74E1 / T2@SEL$2
6 - SEL$F5BB74E1 / T2@SEL$2
7 - SEL$3 / VX@SEL$2
8 - SEL$3
9 - SET$1 / V_T3T4@SEL$3
10 - SET$1
11 - SEL$4 / T3@SEL$4
12 - SEL$5 / T4@SEL$5
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$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "T1"@"SEL$2" ("T1"."ID2"))
INDEX_RS_ASC(@"SEL$F5BB74E1" "T2"@"SEL$2" "PK_T2")
NO_ACCESS(@"SEL$F5BB74E1" "VX"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "T1"@"SEL$2" "T2"@"SEL$2" "VX"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "T2"@"SEL$2")
USE_HASH(@"SEL$F5BB74E1" "VX"@"SEL$2")
NO_ACCESS(@"SEL$3" "V_T3T4"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$3")
FULL(@"SEL$5" "T4"@"SEL$5")
FULL(@"SEL$4" "T3"@"SEL$4")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID1"="VX"."ID1")
4 - access("T1"."ID2"=42)
6 - access("T1"."ID1"="T2"."SYS_NC00003$")
Note
-----
- cardinality feedback used for this statement
--不過這個問題我在一臺11.2.0.4的機器測試，無法重現。估計是bug。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1724262/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Database Cardinality FeedbackOracleDatabase
- Oracle 11g SQL效能的新特性（二）- Cardinality FeedbackOracleSQL
- 基數反饋（Cardinality Feedback）
- 那些語句使用cardinality feedback
- Cardinality Feedback基數反饋
- 【DBA】Oracle 11g 針對SQL效能的新特性（二）- Cardinality FeedbackOracleSQL
- [20150430]11G SPM與cardinality feedback2
- Oracle 11gr2 新技術 Cardinality FeedbackOracle
- 聊聊Cardinality Feedback——一種CBO優化器嘗試優化
- 關於oracle11g的關於cardinality feedback新特性Oracle
- [20130305]Cardinality Feedback on 11gR2.txt
- 【問題排查篇】一次業務問題對 ES 的 cardinality 原理探究
- oracle 11g新特性Cardinality Feedback基數反饋造成同一sql幾乎同時執行產生不同的執行計劃OracleSQL
- oracle 11g ASM問題OracleASM
- connect by 樹形查詢在評估cardinality時存在著問題
- 【問題排查篇】一次業務問題對 ES 的 cardinality 原理探究 | 京東雲技術團隊
- Cardinality
- dedecms /plus/feedback_ajax.php、/templets/feedback_main.htm、/templets/feedback_edit.htm XSS && SQL Injection VulPHPAISQL
- 7.40 CARDINALITY
- No_expand hint 解決CBO針對where...or..語句中的cardinality計算問題
- ORACLE 11g RAC時鐘同步問題Oracle
- impdp 10g/11g問題解決
- Oracle 11g存在密碼過期問題Oracle密碼
- oracle 11g 操作ASM許可權問題OracleASM
- 11g升級到Oracle 12c碰到的問題(11g中不存在問題) - ORA-01792Oracle
- ORACLE 11G EM問題萬能解決辦法Oracle
- oracle linux 下安裝oracle 11g問題OracleLinux
- ORACLE 11G EM 配置命令及問題處理Oracle
- oracle 11g 密碼延遲驗證問題Oracle密碼
- Cardinality指什麼？
- Cardinality (222)
- Cardinality的計算
- Oracle 11g RAC之HAIP相關問題總結OracleAI
- 10g升級到11g密碼問題密碼
- 11g DBNEWID工具修改DBID碰到的問題
- Toad 版本低不能連ORACLE 11G的問題Oracle
- 16.基數(Cardinality)
- CARDINALITY HINT用法小試