[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應該不公開,這些都是基於統計學得來的公式.