[20150706]11G cardinality feedback問題

lfree發表於2015-07-06

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章