[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(?)
***************************************
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 直方圖直方圖
- 收集直方圖及檢視直方圖資訊直方圖
- 直方圖(histograms)直方圖Histogram
- 7.3 直方圖直方圖
- oracle 直方圖Oracle直方圖
- 直方圖均衡化直方圖
- 04:垂直直方圖直方圖
- oracle直方圖使用Oracle直方圖
- 直方圖學習直方圖
- Oracle直方圖解析Oracle直方圖圖解
- [zt] Histograms - 直方圖Histogram直方圖
- dba_histograms等高直方圖和等頻直方圖的理解Histogram直方圖
- 關於高度均衡和頻率均衡的直方圖直方圖
- 直方圖中最大矩形直方圖
- Oracle直方圖詳解Oracle直方圖
- 【效能優化】直方圖優化直方圖
- 11G,可以只刪除直方圖資訊直方圖
- [Python影象處理] 十一.灰度直方圖概念及OpenCV繪製直方圖Python直方圖OpenCV
- OpenCV計算機視覺學習(9)——影像直方圖 & 直方圖均衡化OpenCV計算機視覺直方圖
- halcon-直方圖均衡直方圖
- python如何畫直方圖Python直方圖
- elasticsearch 之 histogram 直方圖聚合ElasticsearchHistogram直方圖
- oracle直方圖筆記-轉Oracle直方圖筆記
- 使用直方圖注意事項直方圖
- (轉)Oracle直方圖詳解Oracle直方圖
- Oracle直方圖詳解(ZT)Oracle直方圖
- [20120905]刪除直方圖.txt直方圖
- Oracle直方圖 (柱狀圖 histograms) 詳解Oracle直方圖Histogram
- 【沃趣科技】直方圖系列1直方圖
- Matplotlib直方圖繪製技巧直方圖
- opencv——影像直方圖與反向投影OpenCV直方圖
- 一文搞懂直方圖均衡直方圖
- 你知道直方圖都能幹啥?直方圖
- OpenCV之影象直方圖均衡化OpenCV直方圖
- 聊一聊MySQL的直方圖MySql直方圖
- oracle直方圖histogram小記(一)Oracle直方圖Histogram
- 轉貼oracle直方圖histogram(二)Oracle直方圖Histogram
- 主鍵列直方圖有用嗎?直方圖