histogram与10053(zt)

histogramoraclecbo提供更精确的成本估计而设计的一种直方图数据。histogram能提供列的数据分布，每次分析表后列的分布信息将会被保存在统计表里面，分析时默认的histogram size75，意思就是采用75buckets来表示数据分布。

histogram分为2种类型，基于高度的histogram和基于值的histogram

histogram buckets的数量少于列的distinct value时，oracle会采用基于高度的直方图反映数据分布，每个bucket容纳相同数量的值。

SQL> column column_name format a20;

SQL> column ENDPOINT_ACTUAL_VALUE format a20;

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE

2      FROM DBA_HISTOGRAMS

3      WHERE TABLE_NAME ='TEST' AND COLUMN_NAME='OBJECT_ID'

4      ORDER BY ENDPOINT_NUMBER;

COLUMN_NAME         ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

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

OBJECT_ID                        29             1

OBJECT_ID                        44             2

OBJECT_ID                        59             3

OBJECT_ID                        74             4

OBJECT_ID                        75            76

histogram buckets>=列的distinct values时，那么Oracle会使用基于值的histogram，每个值将会占据一个bucket，来看一下

SQL> column column_name format a20;

SQL> column ENDPOINT_ACTUAL_VALUE format a20;

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE

2      FROM DBA_HISTOGRAMS

3      WHERE TABLE_NAME ='TEST' AND COLUMN_NAME='OBJECT_ID'

4      ORDER BY ENDPOINT_NUMBER;

COLUMN_NAME         ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

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

OBJECT_ID                     19928             1

OBJECT_ID                     29927             2

OBJECT_ID                     39926             3

...............

OBJECT_ID                     49908            74

OBJECT_ID                     49909            75

alter session set events'10053 trace name context forever,level 1';

select object_name from test where object_id=1;

alter session set events'10053 trace name context off';

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: TEST Alias: TEST

#Rows: 49909 #Blks: 707 AvgRowLen: 95.00

Index Stats::

Index: IND_TEST_OBJECT_ID Col#: 4

LVLS: 1 #LB: 179 #DK:5 LB/K: 35.00 DB/K: 175.00 CLUF: 879.00

***************************************

SINGLE TABLE ACCESS PATH

Column (#4): OBJECT_ID(NUMBER)

AvgLen: 2.00 NDV: 75 Nulls: 0 Density: 1.0018e-05 Min: 1 Max: 75

Histogram: Freq #Bkts: 75 UncompBkts: 49909 EndPtVals: 75

Table: TEST Alias: TEST

Card: Original: 49909 Rounded: 19928 Computed: 19928.00 Non Adjusted: 19928.00

Access Path: TableScan

Cost: 158.56 Resp: 158.56 Degree: 0

Cost_io: 156.00 Cost_cpu: 18011198

Resp_io: 156.00 Resp_cpu: 18011198

Access Path: index (AllEqRange)

Index: IND_TEST_OBJECT_ID

resc_io: 423.00 resc_cpu: 11183699

ix_sel: 0.39929 ix_sel_with_filters: 0.39929

Cost: 424.59 Resp: 424.59 Degree: 1

Best:: AccessPath: TableScan

Cost: 158.56 Degree: 1 Resp: 158.56 Card: 19928.00 Bytes: 0

io_cost=resc_io= blevel+FF*leaf_blocks+FF*clustering_factor

10.39929*179+0.39929*879

=1+71.47291+350.97591

=423

alter session set events'10053 trace name context forever,level 1';

select object_name from test where object_id=75;

alter session set events'10053 trace name context off';

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: TEST Alias: TEST

#Rows: 49909 #Blks: 707 AvgRowLen: 95.00

Index Stats::

Index: IND_TEST_OBJECT_ID Col#: 4

LVLS: 1 #LB: 179 #DK:75 LB/K: 2.00 DB/K: 11.00 CLUF: 879.00

***************************************

SINGLE TABLE ACCESS PATH

Column (#4): OBJECT_ID(NUMBER)

AvgLen: 2.00 NDV: 75 Nulls: 0 Density: 1.0018e-05 Min: 1 Max: 75

Histogram: Freq #Bkts: 75 UncompBkts: 49909 EndPtVals: 75

Table: TEST Alias: TEST

Card: Original: 49909 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50

Access Path: TableScan

Cost: 158.56 Resp: 158.56 Degree: 0

Cost_io: 156.00 Cost_cpu: 18011198

Resp_io: 156.00 Resp_cpu: 18011198

Access Path: index (AllEqRange)

Index: IND_TEST_OBJECT_ID

resc_io: 2.00 resc_cpu: 15503

ix_sel: 1.0018e-05 ix_sel_with_filters: 1.0018e-05

Cost: 2.00 Resp: 2.00 Degree: 1

Best:: AccessPath: IndexRange Index: IND_TEST_OBJECT_ID

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.50 Bytes: 0

SQL> select OBJ#,COL#,BUCKET_CNT,ROW_CNT,SAMPLE_SIZE,MINIMUM,MAXIMUM,DISTCNT,DENSITY from sys.HIST_HEAD\$ where obj#=51933 ANDCOL#=4;

OBJ#      COL# BUCKET_CNT   ROW_CNT SAMPLE_SIZE   MINIMUM   MAXIMUM   DISTCNT   DENSITY

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

51933         4     49909        75      49909         1        75        75 .000010018

io_cost=resc_io= blevel+FF*leaf_blocks+FF*clustering_factor

11.0018e-05*179+1.0018e-05*879

=1+0.001793222+0.008805822

=2（因为最少会读2个块）

• 博文量
19
• 访问量
20569