Histogram总结[@more@]

Histogram总结

Histogram主要是提供数据的分布情况，以便Oracle的CBO能够做出最优的执行计划。

直方图的类型：

基于宽度平衡Histogram

将字段的属性分成相等的宽度，称作bucket。并计算行数同样的值被放置在同一个bucket中，如果几个行是同样的值，会放置为同一个bucket中，增机bucket的高度。

基于高度平衡Histogram

每个bucket有几乎相同数量的同样数量的行。如果几行是相同的值，将被放置到同一个或者分散到几个bucket上，也就是说同样值得行被放置在几个bucket中，而每一个bucket的高度，就是存放的行数几乎相同。高度平衡Histogram更适合于计算选择度的估计。

Popular值和nonpopular值

在添加Histogram时候，Oracle 9i区分Popular值和nonpopular值。

Popular值意思是这个值是多次作为bucket的结束点的值；而只是显示为一次或者根本就没有作为bucket的结束点的值的成为nonpopular值。

DENSITY统计

在添加一个Histogram地时候，DENSITY统计被计算。这个值为NUM_DISTINCE加1。

在优化器使用Histogram来估计选择度的时候，范围扫描的选择度是由这个值得bucket分布的数目来决定。当为等值得选择度则是由考虑两种情况：

一、如果这个值是Popular值的话，将根据bucket的数目来确定。

二、如果是nonpopular值的话，这是根据DENSITY统计来确定。

Histogram的收集

可以有两种方式收集Histogram统计数据：analyze和dbms_stats包。

在这两种收集的时候，可以有指定bucket数目或者使用缺省的bucket数目。

什么时间使用Histogram

主要是在字段上的数据有高度的数据分布的不均匀，即有部分数据占整个数据量的比例很大。这样特征的表应该建立Histogram。

避免使用Histogram的情况：

1) 这部分字段不会在where字句中使用；

2) 字段是唯一的，只是在等值使用；

3) 所有的在此字段中的使用条件是用于绑定变量；

4) 字段中的数据是被均匀分配的

这里的第三点，说明在使用绑定变量的时候，Oracle的优化器并不知道这个字段上将绑定的变量的确切的值。所以并不能用到这个Histogram统计数据。而字段的值是唯一的话，说明在这个字段上所有的值将是均匀分布的。而在条件是等值使用的时候，Histogram统计数据是没有任何意义的。

需要bucket的数目

缺省的bucket的数量是75。可以根据自己的需要指定bucket的数量。最大的bucket数量是255。但是决定bucket数量的多少以达到最优的结果，是需要依靠经验来确定。同时在包dbms_stats中可以指定自动决定bucket的数量。

如果在这个字段上的值相对比较少的话，建议设置bucket的数量大于频繁使用的distinct值。

另外在一些资料上说，如果指定的bucket的数量小于字段的distinct值的话，Oracle将采用基于高度的直方图来反映数据的分布。

查看histogram的统计数据

User/all_histogram

Histogram的使用提示

n 可以使用for all indexed columns选项

n 如果数据分布可能动态改变得话，可以定期收集histogram统计数据；

n 如果是使用绑定变量，histogram数据将不会被使用。

n Oracle推荐如果不需要充分提高性能的话，不需要使用histogram统计

关于histograms的使用的一个问题：

在正常的数据分布而是没有数据的某种分布偏差的情况下，如果收集histograms统计信息，在cursor_sharing这个参数设置为similar的时候容易出现cursor的子版本过多的问题。所以在使用histograms的时候需要注意这个问题。

详细请参考：

http://blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx

之所以产生这样的原因是这样的：在使用收集histogram统计数据之后，如果使用使用绑定变量，histogram数据将不会被使用。在我们设置参数cursor_sharing有三种选择：如果是force的时候，这时候，Oracle会将SQL文的变量无论三七二十一就变成需要的绑定变量的形式，这样我们采集的histogram数据将不会被使用。也就达不到我们所需要的优化效果了。如果设置为exact的时候，就会被不使用绑定变量，如果自己本身没有绑定变量的时候，如果设置为similar的时候，每一次优化器会根据histogram统计数据解析SQL，以达到最优的结果。但是如果这个数据的分布并不是有分布不均得现象，就不要使用histogram统计数据，这或许是为什么在Oracle的文档中说：

Do not use histograms unless they substantially improve performance.

的其中一个原因之一了。

作个试验：

SQL> create table t as select rownum id from all_objects;

Table created

SQL> create index t_idx on t (id);

Index created

SQL>

SQL> begin

2 dbms_stats.gather_table_stats

3 ( ownname => USER,

4 tabname => 'T',

5 method_opt => 'for all indexed columns size 254',

6 cascade => TRUE

7 );

8 end;

9 /

PL/SQL procedure successfully completed

SQL>

SQL> alter session set cursor_sharing=similar;

Session altered

SQL> select * from t CS_SIMILAR where id = 1;

ID

----------

1

SQL> select * from t CS_SIMILAR where id = 50;

ID

----------

50

SQL> select * from t CS_SIMILAR where id = 99;

ID

----------

99

SQL> select * from t CS_SIMILAR where id = 1;

ID

----------

1

SQL> select * from t CS_SIMILAR where id = 50;

ID

----------

50

SQL> select * from t CS_SIMILAR where id = 99;

ID

----------

99

SQL> select sql_text from v$sql where sql_text like 'select * from t CS% where id = %' order by sql_text;

SQL_TEXT

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

select * from t CS_SIMILAR where id = :"SYS_B_0"

这时候就可以看到在设置cursor_sharing=similar时候，会有不同的sql出现

SQL> select * from v$sql_shared_cursor

2 a where a.kglhdpar='78E80FF0';

ADDRESS KGLHDPAR UNBOUND_CURSOR SQL_TYPE_MISMATCH OPTIMIZER_MISMATCH OUTLINE_MISMATCH STATS_ROW_MISMATCH LITERAL_MISMATCH SEC_DEPTH_MISMATCH EXPLAIN_PLAN_CURSOR BUFFERED_DML_MISMATCH PDML_ENV_MISMATCH INST_DRTLD_MISMATCH SLAVE_QC_MISMATCH TYPECHECK_MISMATCH AUTH_CHECK_MISMATCH BIND_MISMATCH DESCRIBE_MISMATCH LANGUAGE_MISMATCH TRANSLATION_MISMATCH ROW_LEVEL_SEC_MISMATCH INSUFF_PRIVS INSUFF_PRIVS_REM REMOTE_TRANS_MISMATCH LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH OVERLAP_TIME_MISMATCH SQL_REDIRECT_MISMATCH MV_QUERY_GEN_MISMATCH USER_BIND_PEEK_MISMATCH TYPCHK_DEP_MISMATCH NO_TRIGGER_MISMATCH FLASHBACK_CURSOR

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

78E7B484 78E80FF0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

78E81888 78E80FF0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

78E6D4D8 78E80FF0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

SQL>

再次查看v$sql_shared_cursor会发现这三个sql是独立的，并没有什么不同的地方。

TOM 的这篇文章也是讲的这个话题：

http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html

ITPub博客

Histogram总结

Histogram总结

最新文章