[20170615]直方图-高度直方图(11g).txt

[20170615]直方图-高度直方图(11g).txt

--//昨天看了一些直方图的资料,重新看jonathanlewis写<CBO>书籍,在测试时遇到一些与原来书讲的不一样的地方.
--//自己重复测试看看.

1.环境以及测试建立:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> define m_demo_size=80
SCOTT@book> drop table t1 purge ;
Table dropped.

create table t1 (
    skew        not null,   
    padding
)
as
with generator as (
    select    --+ materialize
        rownum     id
    from    all_objects
    where    rownum <= 5000
)
select
    /*+ ordered use_nl(v2) */
    v1.id,
    rpad('x',400)
from
    generator    v1,
    generator    v2
where
    v1.id <= &m_demo_size
and    v2.id <= &m_demo_size
and    v2.id <= v1.id
order by
    v2.id,v1.id
;

create index t1_i1 on t1(skew);

begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 75'
    );
end;
/

select
    num_distinct, density, num_Buckets,histogram,sample_size
from
    user_tab_columns
where
    table_name = 'T1'
and    column_name = 'SKEW'
;

NUM_DISTINCT    DENSITY NUM_BUCKETS HISTOGRAM       SAMPLE_SIZE
------------ ---------- ----------- --------------- -----------
          80 .013973812          75 HEIGHT BALANCED        3240

select
    endpoint_number, endpoint_value
from
    user_tab_histograms
where
    column_name = 'SKEW'
and    table_name = 'T1'
order by
    endpoint_number
;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              1
              1              9
              2             13
              3             16
              4             19
              5             21
              6             23
              7             25
              8             26
              9             28
             10             29
             11             31
             12             32
             13             33
             14             35
             15             36
             16             37
             17             38
             18             39
             19             40
             20             41
             21             42
             22             43
             23             44
             24             45
             25             46
             26             47
             27             48
             28             49
             29             50
             30             51
             32             52
             33             53
             34             54
             35             55
             37             56
             38             57
             39             58
             41             59
             42             60
             43             61
             45             62
             46             63
             48             64
             49             65
             51             66
             52             67
             54             68
             56             69
             57             70
             59             71
             60             72
             62             73
             64             74
             66             75
             67             76
             69             77
             71             78
             73             79
             75             80
60 rows selected.

prompt    equality on a popular value - uses bucket counts

select
    count(*)
from    t1
where    skew = 77
;
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ftrt6fax5mrgr, child number 0
-------------------------------------
select     count(*) from    t1 where    skew = 77
Plan hash value: 2432955788
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |      1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_I1 |     86 |   258 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SKEW"=77)

--//skew=77 是流行值.占了2个桶. 2/75*3240=86.3999999999999999784 ,也就是流行值的计算是占用backup数量/backup总数量*NDV.

prompt    equality on a non-popular value - uses density

select
    count(*)
from    t1
where    skew = 72
;

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2u51xnc3hnfcf, child number 0
-------------------------------------
select     count(*) from    t1 where    skew = 72

Plan hash value: 2432955788

----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |      1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_I1 |     29 |    87 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

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

   2 - access("SKEW"=72)

--//如果不是流行值,就不用上来类似的公式而是,而是3240*DENSITY
--//3240*.013973812=45.27515088 ?? ,存在误差,说明oracle改变了算法.建立10053分析看看.

SCOTT@book> @ &r/10053x 2u51xnc3hnfcf 0
PL/SQL procedure successfully completed.

--//查看跟踪文件:
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Column (#1):
    NewDensity:0.008958, OldDensity:0.013974 BktCnt:75, PopBktCnt:32, PopValCnt:16, NDV:80
  Column (#1): SKEW(
    AvgLen: 3 NDV: 80 Nulls: 0 Density: 0.008958 Min: 1 Max: 80
    Histogram: HtBal  #Bkts: 75  UncompBkts: 75  EndPtVals: 60
  Table: T1  Alias: T1
    Card: Original: 3240.000000  Rounded: 29  Computed: 29.03  Non Adjusted: 29.03
  Access Path: TableScan
    Cost:  57.06  Resp: 57.06  Degree: 0
      Cost_io: 57.00  Cost_cpu: 2093652
      Resp_io: 57.00  Resp_cpu: 2093652
  Access Path: index (index (FFS))
    Index: T1_I1
    resc_io: 4.00  resc_cpu: 600650
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  4.02  Resp: 4.02  Degree: 1
      Cost_io: 4.00  Cost_cpu: 600650
      Resp_io: 4.00  Resp_cpu: 600650
  Access Path: index (AllEqRange)
    Index: T1_I1
    resc_io: 1.00  resc_cpu: 13971
    ix_sel: 0.008958  ix_sel_with_filters: 0.008958
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: T1_I1
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 29.03  Bytes: 0
    check parallelism for statement[<unnamed>]
kkfdtParallel: parallel is possible (no statement type restrictions)
    kkfdPaForcePrm: dop:1 ()
kkfdPaPrm: use dictionary DOP(1) on table
kkfdPaPrm:- The table : 90429
kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop)
kkfdiPaPrm: dop:1 serial(?)
***************************************

--找到如下连接:
http://www.adellera.it/blog/2009/10/16/cbo-newdensity-replaces-density-in-11g-10204-densities-part-iii/

NewDensity is not stored anywhere in the data dictionary, but it is computed at query optimization time by the CBO (note
that density is still computed by dbms_stats using the old formula, but then it is ignored by the CBO). The NewDensity formula
is based mainly on some histogram-derived figures; using the same names found in 10053 traces:

NewDensity = [(BktCnt - PopBktCnt) / BktCnt] / (NDV - PopValCnt)
--//按照这个公式计算:
(75-32)/75/(80-16) = .00895833333333333333
--//与NewDensity:0.008958非常接近.不过我找遍跟踪文件并没有上面的公式,oracle应该不公开,这些都是基于统计学得来的公式.

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

转载于:http://blog.itpub.net/267265/viewspace-2140770/