[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(?)
***************************************
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应该不公开,这些都是基于统计学得来的公式.