oracle 基数反馈

1.建立测试表
create table test.m1 as  select * from dba_objects;
create table test.m2 as  select * from dba_objects;
create table test.m3 as  select * from dba_objects;



2.执行查询


SQL>  select   count(m1.object_id) from m1 ,m2,m3  where m2.object_name like '%t%' and m1.object_id=m2.object_id and m1.object_name=m3.object_name;


COUNT(M1.OBJECT_ID)
-------------------
           71055360


查看执行计划


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  gvqmfucsz65pj, child number 0
-------------------------------------
 select   count(m1.object_id) from m1 ,m2,m3  where m2.object_name like
'%t%' and m1.object_id=m2.object_id and m1.object_name=m3.object_name


Plan hash value: 1367023217


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       | 18914 (100)|          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |      |     1 |   224 |            |          |
|*  2 |   HASH JOIN          |      |   219K|    46M| 18914   (1)| 00:03:47 |
|*  3 |    HASH JOIN         |      |  6984 |  1077K|  9601   (1)| 00:01:56 |
|*  4 |     TABLE ACCESS FULL| M2   |  6984 |   538K|  9303   (1)| 00:01:52 |
|   5 |     TABLE ACCESS FULL| M1   | 88868 |  6856K|   297   (1)| 00:00:04 |
|   6 |    TABLE ACCESS FULL | M3   |  2793K|   175M|  9299   (1)| 00:01:52 |
-----------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / M2@SEL$1
   5 - SEL$1 / M1@SEL$1
   6 - SEL$1 / M3@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "M2"@"SEL$1")
      FULL(@"SEL$1" "M1"@"SEL$1")
      FULL(@"SEL$1" "M3"@"SEL$1")
      LEADING(@"SEL$1" "M2"@"SEL$1" "M1"@"SEL$1" "M3"@"SEL$1")
      USE_HASH(@"SEL$1" "M1"@"SEL$1")
      USE_HASH(@"SEL$1" "M3"@"SEL$1")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("M1"."OBJECT_NAME"="M3"."OBJECT_NAME")
   3 - access("M1"."OBJECT_ID"="M2"."OBJECT_ID")
   4 - filter(("M2"."OBJECT_NAME" IS NOT NULL AND "M2"."OBJECT_NAME"
              LIKE '%t%'))




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - (#keys=0) COUNT("M1"."OBJECT_ID")[22]
   2 - (#keys=1) "M1"."OBJECT_ID"[NUMBER,22]
   3 - (#keys=1) "M1"."OBJECT_ID"[NUMBER,22],
       "M1"."OBJECT_NAME"[VARCHAR2,128]
   4 - "M2"."OBJECT_ID"[NUMBER,22]
   5 - "M1"."OBJECT_NAME"[VARCHAR2,128], "M1"."OBJECT_ID"[NUMBER,22]
   6 - "M3"."OBJECT_NAME"[VARCHAR2,128]




66 rows selected.


SQL>  select   count(m1.object_id) from m1 ,m2,m3  where m2.object_name like '%t%' and m1.object_id=m2.object_id and m1.object_name=m3.object_name;


COUNT(M1.OBJECT_ID)
-------------------
           71055360


查看执行计划


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  gvqmfucsz65pj, child number 1
-------------------------------------
 select   count(m1.object_id) from m1 ,m2,m3  where m2.object_name like
'%t%' and m1.object_id=m2.object_id and m1.object_name=m3.object_name


Plan hash value: 1895697846


--------------------------------------------------------------------------------
-----


| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
    |


--------------------------------------------------------------------------------
-----


|   0 | SELECT STATEMENT     |      |       |       |       | 43726 (100)|
    |


|   1 |  SORT AGGREGATE      |      |     1 |   224 |       |            |
    |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   HASH JOIN          |      |    35M|  7583M|   183M| 43726   (1)| 00:08
:45 |


|*  3 |    HASH JOIN         |      |  1129K|   170M|  7904K| 14856   (1)| 00:02
:59 |


|   4 |     TABLE ACCESS FULL| M1   | 88868 |  6856K|       |   297   (1)| 00:00
:04 |


|*  5 |     TABLE ACCESS FULL| M2   |  1129K|    85M|       |  9305   (1)| 00:01
:52 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


|   6 |    TABLE ACCESS FULL | M3   |  2793K|   175M|       |  9299   (1)| 00:01
:52 |


--------------------------------------------------------------------------------
-----




Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / M1@SEL$1
   5 - SEL$1 / M2@SEL$1
   6 - SEL$1 / M3@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "M1"@"SEL$1")
      FULL(@"SEL$1" "M2"@"SEL$1")
      FULL(@"SEL$1" "M3"@"SEL$1")
      LEADING(@"SEL$1" "M1"@"SEL$1" "M2"@"SEL$1" "M3"@"SEL$1")
      USE_HASH(@"SEL$1" "M2"@"SEL$1")
      USE_HASH(@"SEL$1" "M3"@"SEL$1")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("M1"."OBJECT_NAME"="M3"."OBJECT_NAME")
   3 - access("M1"."OBJECT_ID"="M2"."OBJECT_ID")
   5 - filter(("M2"."OBJECT_NAME" IS NOT NULL AND "M2"."OBJECT_NAME" LIKE
              '%t%'))




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - (#keys=0) COUNT("M1"."OBJECT_ID")[22]
   2 - (#keys=1) "M1"."OBJECT_ID"[NUMBER,22]
   3 - (#keys=1) "M1"."OBJECT_ID"[NUMBER,22],
       "M1"."OBJECT_NAME"[VARCHAR2,128]
   4 - "M1"."OBJECT_NAME"[VARCHAR2,128], "M1"."OBJECT_ID"[NUMBER,22]
   5 - "M2"."OBJECT_ID"[NUMBER,22]
   6 - "M3"."OBJECT_NAME"[VARCHAR2,128]




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - cardinality feedback used for this statement




70 rows selected.




查询官方文档


Oracle只针对下面情况开启CFB：


o 没有收集表的统计信息，并且dynamic sampling 也没有开启。
或者
o 查询条件复杂（比如条件有函数）或者涉及多列，但却没有收集扩展的统计信息(extended statistics)


在这几种情况下，CBO是无法估算出准确的Cardinality的。


所以太low 的语句，oracle 是不屑开cfo的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7569309/viewspace-2133740/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7569309/viewspace-2133740/