直方图相关概念
百度百科定义:直方图(Histogram),又称质量分布图,是一种统计报告图,由一系列高度不等的纵向条纹或线段表示数据分布的情况。 一般用横轴表示数据类型,纵轴表示分布情况。
oracle直方图作用:在Oracle中直方图存储不同分类数据的出现频率。前文中提到分析表的作用是给CBO提供基本的信息并认为每个值出现频率相同,直方图会准确告诉CBO每个唯一值出现的频率,这个很重要。
--1
CREATE TABLE test_fan AS
SELECT d.OWNER,d.OBJECT_ID,DECODE(MOD(ROWNUM,1000),0,'A','B') status
FROM Dba_Objects d
WHERE ROWNUM<=10000;
--2
SELECT t.status
,COUNT(1)
FROM test_fan t
GROUP BY t.status;
STATUS COUNT(1)
B 9990
A 10
--3
CREATE INDEX IDX_TEST_STATUS ON TEST_FAN(STATUS);
--4
BEGIN
dbms_stats.gather_table_stats('PROD_DEV', 'TEST_FAN');
END;
--5
EXPLAIN PLAN FOR
SELECT * FROM TEST_FAN T WHERE T.STATUS='A';
SELECT * FROM TABLE(DBMS_XPLAN.display);
Plan hash value: 3747397153
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 55000 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_FAN | 5000 | 55000 | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."STATUS"='A')
上面做了如下事情:
1 创建测试数据
2查看status数据分布
3创建索引
4分析表但不收集直方图
5查看执行计划
从执行计划可以看到,执行计划并不准确
接下来看直方图作用
--1
BEGIN
dbms_stats.gather_table_stats('PROD_DEV',
'TEST_FAN',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNs SIZE auto',
cascade => TRUE);
END;
--2
SELECT t.table_name
,t.column_name
,t.HISTOGRAM
,t.num_distinct
,t.density
,h.ENDPOINT_NUMBER
,h.ENDPOINT_VALUE
FROM dba_tab_col_statistics t
,dba_tab_histograms H
WHERE T.owner = H.OWNER
AND T.table_name = H.TABLE_NAME
AND T.column_name = H.COLUMN_NAME
AND t.table_name = 'TEST_FAN'
AND T.owner='PROD_DEV';
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT DENSITY ENDPOINT_NUMBER ENDPOINT_VALUE
TEST_FAN STATUS FREQUENCY 2 0.00005 10 3.37499E+35
TEST_FAN STATUS FREQUENCY 2 0.00005 10000 3.42692E+35
TEST_FAN OWNER NONE 5 0.2 0 4.1186E+35
TEST_FAN OBJECT_ID NONE 10000 0.0001 0 2
TEST_FAN OWNER NONE 5 0.2 1 4.32772E+35
TEST_FAN OBJECT_ID NONE 10000 0.0001 1 10189
--3
EXPLAIN PLAN FOR
SELECT * FROM TEST_FAN T WHERE T.STATUS='A';
SELECT * FROM TABLE(dbms_xplan.display());
Plan hash value: 2855297280
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 110 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_FAN | 10 | 110 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_STATUS | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."STATUS"='A')
1.是收集直方图,这些参数设置可以参考官方文档解释,常用到的是
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all columns:统计所有列的histograms
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data
-AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
AUTO和SKEWONLY的区别就在于收集histograms时ORACLE的选择条件不同。
AUTO会根据column数据分布情况以及column的负载情况进行判断是不是收集这个column的histogram.
而SKEWONLY只会根据column的数据分布情况决定是否收集histogram。
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.
2.查看直方图信息,可以看到 列STATUS 的HISTOGRAM 值为FREQUENCY,说明是等频直方图。默认情况下列唯一值得个数少于254是建立等频直方图,否则是等直直方图。我们只需要记住等频直方图比等直直方图更准确,有兴趣可以可以做实验证实method_opt => 'FOR ALL COLUMNs SIZE auto' SIZE就是桶数,设置大于254即可。
上诉有两个唯一值,分配两个桶,桶的值是累加。
3.基于直方图信息的执行计划,可以看到估算信息是准确的。其中rows的值是根据2步骤查询结果计算得来,举个例子 STATUS='B'的 rows=10000-10=9990.
删除直方图
--1--推荐
BEGIN
dbms_stats.gather_table_stats('PROD_DEV',
'TEST_FAN',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNs SIZE 1',
cascade => TRUE);
END;
--2
BEGIN
dbms_stats.delete_table_stats('PROD_DEV','TEST_FAN');
END;
在一切变得简单之前,唯一答案就是努力去做!